DBQueryOnInput

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.

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 ellipsis button to launch the Database Configuration editor as shown in Figure 2, where details of the database to which the component should connect are configured.

Figure 2: Database configuration editor with mckoi database details

Database

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

The vendor name is marked in red color if the default JDBC driver class is not present in component's class path.
Even if a particular database vendor name is not present in the drop-down list, the component can still connect to the database.
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
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 required value for Database, driver values are populated with standard value (This can be changed to required value based on driver being used).
Note: The jar file(s) that are part of JDBC client libraries for selected vendor have to be added as resources to JDBC system library.

JDBC URL

This property determines the location at which the required database is running. On selecting required database, URL value is populated with standard value (This can be changed to required values based on driver being used).
Note: The populated values have place holders which have to be replaced to point to correct database location, Example: In Figure 2 <hostname> is replaced with localhost IP indicating that the database is running on local machine.

Username

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

Password

This property determines the password for the specified user.

Connection Properties

Any driver specific connection properties which may have to be passed while creating a JDBC connection should be provided against Connection Properties (shown in Figure 3). For example, fixedString=true uses FIXED CHAR semantics for string values in oracle.
Note:

Please refer to documentation of driver that is being used for valid name-values for connection properties.
Connection properties can be loaded from a properties file using File... button. Refer to the following link http://java.sun.com/j2se/1.5.0/docs/api/java/util/Properties.html#load(java.io.InputStream) for details on properties file.

Figure 3: Connection property for 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 response is generated, but before the message is sent out of the component.

Advanced Settings

Connection ping sql

A SQL statement which is guaranteed to execute without exception, except when connection to 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 database is lost and appropriate configured action (say, reconnect) is taken.
Example: select * from dual for oracle, select 1 for MS SQL

Enable JDBC Driver Logging

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

Wrap DB object names

When database object names (viz. table names, column names, schema names…) contain spaces, then they should be wrapped in database dependent special characters. For example, " " for Oracle, [ ] for Microsoft Excel, and no wrap characters for MySQL.
Database object names are wrapped as shown below -
Start wrap character + object name + End wrap character
Note: Providing a wrong wrap character may lead to problems.

Start wrap character

Character which should be used before the object name.

End wrap character

Character which should be used after the object name.

Interaction Configurations Panel

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

Figure 4: 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.

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 (shown in Figure 5). The schema on the output port is not completely defined (shown in Figure 6) in this case as the schema varies depending on the query sent in input.

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

Figure 6: 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 (shown in Figure 7) and loaded into Fiorano Mapper.

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

This option allows direct mappings in XSLT (shown in Figure 8)

Figure 8: 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 (shown in Figure 9). The output structure (shown in Figure 10) is completely defined and will not vary based on the input.

Figure 9: Output when columns names are generated as ATTRIBUTES

Figure 10: 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 required output element.

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 value of property Single Batch Mode is set as '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

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

Figure: CSV Configuration

Icon

This property is supported when CSV output is required only in the instance are either Select queries or Stored Procedure queries which return single ResultSet.

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.

Input and output

Input

Input schema for the component is shown in Figure 14

Figure 11: Input schema for the component

Input message (shown in Figure 15) contains only one element query whose value contains the query that has to be executed.

Figure 12: 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 13: 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 6 and 10 and sample inputs for them are shown in Figures 5 and 9.

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 14 and 15. Use feeder and display components (shown in Figure 16) to create a flow to send sample input and check the response respectively.

Figure 14: MCF Panel configuration for scenario1

Figure 15: Interactions configuration of scenario1

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

Figure 16: Flow for scenario1

Send input message, shown in Figure 17, from feeder and notice the output similar to the one shown in Figure 18 in display.

Figure 17: Input for scenario1

Figure 18: Output for scenario1

Scenario 2

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

Figure 19: Interaction configuration of scenario2

Repeat the test as described in scenario 1 with same input and observe the output similar to the one shown in Figure 20.

Figure 20: 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 Add Resources to a Microservice section.