Page History

The DBQueryOnInput component is used to execute different SQL statements with each request on a configured database. The SQL to be executed is not configured in the CPS and is taken from the input message. In other DB components, the component is configured with predefined SQL statement(s) in the CPS and some or all of these statements are executed for all requests.

Points to note:

• Only one query can be processed per message. If multiple queries have to be processed, use XMLSplitter to split the message into multiple messages each containing a single query.
• It is recommended NOT to use JDBC-ODBC Bridge driver to connect to any RDBMS in a production environment. Please, use a commercial JDBC driver instead.
• The JDBC drivers or the resources must be directly added onto the JDBC system lib and not as resource to the DBQueryOnInput component itself. To add JDBC drivers to DBQueryOnInput component, please refer to Add Resources to a Microservice section.

...

This property is visible when the value of property Single Batch Mode is set as yes'no'. The property determines the number of units of result an output message contains.
Each row in a result set (typically result of a select query) or an update result (result of update, delete, insert operations) is treated as unit of result.
Example: Consider a stored procedure that returns a result of select query followed by three update queries and another select query. Assume first select return 18 rows and second query returns 11 rows. If Single Batch Mode is set as no and Batch Size is set as 10 then there will be four output messages.

• first message: first 10 rows from first query
• second message: remaining 8 rows from first query and 2^nd update query results.
• third message: 3^rd update query result and first 9 rows of second select query.
• fourth message: remaining two rows from second query.

CSV Configuration

Image Removed
Figure : CSV Configuration

When When Enable CSV Output property  property is selected, output of the queries will be in CSV format.

Image Added
Figure: CSV Configuration

After enabling this property, the below parameters becomes editable. These separators, which act as punctuation in the CSV output,can be customized by providing the respective values:

Field Separator: The value of Column Separator to be used in CSV output.
Line Separator: The value of Row Separator to be used in CSV output.
Wrap Character: The specified character is used to wrap each field that is returned when a query is processed. For example , if the wrap character is " and the field separator is ; This Field is not mandatory.

When separators used are:,

• Field separator → ;
• Line separator → ,
• Wrap Character → "

then the output will be like:

"13579008642";"john";"hyd". This Field is not mandatory.,"124";"joseph";"bang",

Escape Character: This field is not mandatory. If the wrap character mentioned in CPS turn up in the values returned from the DB, then those characters will be prefixed by escape character specified. If no character is specified, no action will be taken.

...