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 Adding Resources to a Microservice section.

Configuration

Managed Connection Factory Panel

The connection details are configured in the first panel, Managed Connection Factory (MCF). Figure 1 illustrates the panel with expert properties view enabled.

Figure 1: Connection configuration details in MCF panel

Connection Properties

Use Connection Details From Input

Parameters to create the connection can be specified in the input message when this property is set to true. If this property is selected the validation errors in the managed connection factory panel of the CPS are treated as warnings. So user can bypass this step without giving valid configuration and complete the configuration of the component. If valid properties are not provided even in the input message exception will be thrown at runtime.

Database Configuration

Click the Database Configuration ellipsis button to launch the Database Configuration editor as shown in the figure above, where details of the database to which the component should connect are configured.

Figure 2: Database configuration editor with Mckoi database details

Password Encryption Configuration

Enables use of custom keys and algorithms to encrypt passwords.

Component Configuration

Database

This property determines the vendor of the database to which the component has to connect.

1. The vendor name is marked in red color if the default JDBC driver class is not present in component's classpath.
2. Even if a particular database vendor name is not present in the drop-down list, the component can still connect to the database.
3. If a vendor name is specified in the drop-down list, it only means that vendor-specific handling is done.
   Example: vendor-specific handling for data types, naming conventions etc
4. To connect to a database from a vendor whose name is not specified in the drop-down list, select Other and provide the correct values for Driver and JDBC URL.

Driver

The driver class name that should be used to connect to the database. On selecting the required value for Database, driver values are populated with the standard value (This can be changed to required value based on the driver being used).

URL

This property determines the location at which the required database is running. On selecting the required database, URL value is populated with the standard value (This can be changed to required values based on the driver being used).
Note: The populated values have placeholders which have to be replaced to point to correct database location. In the figure above "<hostname>" may be replaced with "localhost" if the database is running on local machine.

Username

This property determines the username that should be used to connect to the database.

Password

This property determines the password for the specified user.

Connection ping sql

A SQL statement which is guaranteed to execute without exception, except when connection to the database is lost. when a SQL exception occurs on executing a configured query, this SQL statement is executed. If execution of this SQL statement fails as well, then it is assumed that connection to the database is lost and appropriate configured action (say, reconnect) is taken.
Example: select * from dual for Oracle, select 1 for MS SQL

Connection Properties

Any driver-specific connection properties which may have to be passed while creating a JDBC connection should be provided against Connection Properties. For example, fixedString=true uses FIXED CHAR semantics for string values in Oracle.

Auto Commit (Y/N)

Commit mode that should be used by the JDBC connection.

• yes - Any transactions (queries executed) are automatically and implicitly committed to the database. This is done even before the response is generated.
• no - Any transactions (queries executed) are committed after the request is processed successfully and the response is generated, but before the message is sent out of the component.

Connection Pool Params

Defines the connection pool settings for the component.

Advanced Settings

Query timeout

Time, in seconds (>= 0), after which an exception is thrown if the query execution is not complete. For example, For the default value "60", if a query to database does not return within 60 seconds, then an exception is thrown and query execution is stopped.

Enable JDBC Driver Logging

Value yes implies that logging at the driver level should be enabled. This is used as a debugging option.

Login Timeout

Time, in seconds (>= 0), after which an exception is thrown if the login process is not complete. 

Interaction Configurations Panel

Business logic configuration details are configured in the second panel, Interaction Configurations panel. The figure below illustrates the panel with the expert properties view enabled.

Figure 3: Business logic configuration in Interaction configurations panel

Attributes

Auto Commit For Test

This property determines whether auto-commit should be enabled when testing from the CPS.

• yes - Any transactions (queries executed) are automatically committed to the database while testing. Performed transactions will have to manually undone.
• no - Any transactions (queries executed) are rolled back at the completion of test.

This property will override the value provided for property Auto Commit (Y/N) in the MCF panel.

Pre Processing XSL Configuration

Pre Processing XSL configuration can be used to transform request message before processing it. Click the small button against the property to configure the properties.

Post Processing XSL Configuration

Post Processing XSL configuration can be used to transform response message before sending it to the output port.

Process Message Based on Property

The property helps components to skip certain messages from processing.

Generate columns as

This property determines how any data returned (result set) by the component is represented in the output message. Result sets are tabular data returned for a database query. Select queries always return result sets and stored procedures may return result sets based on the type of variables returned by stored procedures.

TAGS

Column names of result set will be generated as XML elements in the output message.


Figure 4: Output when columns (InternalPO, ArrivalTime) are generated as TAGS

The schema on the output port is not completely defined in this case as the schema varies depending on the query sent in input.

Figure 5: Output schema when the columns are generates as TAGS

However, since the column names are generated as elements, if the columns in result are known and same for all inputs of business scenario, a schema for the output can be generated by defining rowType element type manually and loaded into Fiorano Mapper.

Figure 6: Modified rowType element for query returning InternalPO and ArrivalTime columns

This option allows direct mappings in XSLT.

Figure 7: Mapping to set value from InternalPO to Title

ATTRIBUTES

Column names of result set will be generated as value of attribute name of element column in the output message.

Figure 8: Output when columns names are generated as ATTRIBUTES

The output structure is completely defined and will not vary based on the input.

Figure 9: Output structure when the column names are generated as ATTRIBUTES

Defining mappings in XSLT using this option will require writing some user-defined XSL to loop through all columns in a row and map only data from column element whose attribute name contains required column name to the required output element.

Output XSD

To configure the output XSD, click the ellipsis button.

Figure 10: Output XSD configuration panel

Enable CSV Output

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

Figure 11: CSV Configuration properties

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. This Field is not mandatory.

When separators used are:

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

then the output will be like:

"13579008642";"john";"hyd","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.

Single Batch Mode

This option determines whether the component should send entire result of a query as a single message or as multiple messages.

• yes - Complete result of the query from input request is sent out as a single message. If the result set returned is huge then the component can run into memory problems and stop. When this value is selected, property Batch Size is hidden.
• no - Result of query from input is split and sent out as multiple messages. Number of rows from result to be included in each output message is determined by property Batch Size. When this value is selected, property Batch Size is shown.

Example: If a query returns 100 rows, and the batch size is set to 10 then 10 outputs will be generated each contains 10 rows.

Batch Size

This property is visible when the property Single Batch Mode is disbled. 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.

• firstmessage: first 10 rows from first query
• secondmessage: remaining 8 rows from first query and 2^nd update query results.
• thirdmessage: 3^rd update query result and first 9 rows of second select query.
• fourthmessage: remaining two rows from second query.

Input and output

Input

Input schema for the component is as shown below.

Figure 12: Input schema for the component

Input message contains only one element query whose value contains the query that has to be executed.

Figure 13: Sample input message

When the property Use Connection details from input is chosen, an additional element ConnectionFactorySettings is added to the input schema, as shown in the figure. Properties that are used to create the connection are present under this element.

Figure 14: Input schema with ConnectionFactorySettings

Output

Output schema for the component depends on the value configured for property Generate columns as in Interaction Configurations panel. Output schemas based on the property value are shown in Figures 5 and 9 and sample inputs for them are shown in Figures 4 and 8.

Functional Demonstration

Scenario 1

Execution of a select query with ATTRIBUTES as the mode of column generation.
Start mckoiDB present in %FIORANO_HOME%\esb\samples\mckoiDB by executing CreateMckoiDB.bat and RunMckoi.bat files. Configure the DBQueryOnInput component as shown in Figures 15 and 16.

Use feeder and display components (shown in Figure 17) to create a flow to send sample input and check the response respectively.

Figure 15: MCF Panel configuration for scenario1

Figure 16: Interactions configuration of scenario1

Use feeder and display components to create a flow to send sample input and check the response respectively.

Figure 17: Flow for scenario1

Send input message from the feeder, shown in the figure below.

Figure 18: Input for scenario1

Notice the output as shown below.

Figure 19: Output for scenario1

Scenario 2

Use same connection configurations as described in scenario 1 and change the Interactions Configuration.

Figure 20: Interaction configuration of scenario2

Repeat the test as described in scenario 1 with the same input and observe the output.

Figure 21: Output of scenario2

Useful Tips

• 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 Adding Resources to a Microservice section.