The DBQuery component is used to configure simple queries to insert, update, delete or select records from database.

The CPS of this component allows configuring queries for execution. There is no coding effort involved in the configuration. However, the SQL mode can also be used to write queries. Syntactical validity can be verified by using the Check Syntax SQL button provided on the SQL configuration panel in the SQL mode.

Points to note

• Only one query can be configured in the adapter. Please use the DB component if multiple query configurations need to be configured for a single instance.
• 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 your 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 DBQuery component itself. To add JDBC drivers to DBQuery component, please refer to Add Resources to a Microservice section.

Configuration and Testing

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

Figure 2: Database Configuration editor with database details filled-in

Component Configuration

Database

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

• 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 value will be populated with standard value (This can be changed to required value based on driver being used).

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).

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 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.

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.

Auto Commit (Y/N)

Commit mode that should be used by the JDBC connection.

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

Connection Pool Params

Here you can specify the details for maintaining the pool of connections in the component. Click the Ellipsis  button for the ConnectionPoolAttributes dialog box to appear.


Figure 3: Connection Pool Attributes

Enable Connection Pool

If selected, the connections created are cached in to a pool and used whenever required and available. This can reduce the time for creating a new connection for every input request. If disabled, a new connection is created for each request and it will be closed after completion of that request.

Max Pool Size

The maximum number of connections that can be allocated for the pool.

Blocking Timeout (in ms)

The time after which the call to fetch a connection from the pool will timeout if there is no unused connection available.

Idle Timeout (in ms)

The time after which the idle connections are returned back to the pool.

Advanced Settings

Query timeout

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

Enable JDBC Driver Logging

Selecting this option enables logging at the driver level. This is used as a debugging option.

Wrap DB object names

When database object names (viz. table names, column names, schema names…) contain spaces, 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

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. Figure 4 illustrates the panel with expert properties Expert Properties view enabled.

Figure 4: Business in Interaction panel logic configuration

Attributes

Auto Commit for Test

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

• Enabled (Checkbox selected) - Any transactions (queries executed) will be automatically committed to the database while testing. Performed transactions will have to manually undone
• Disabled (Checkbox cleared) - Any transactions (queries executed) will be rolled back at the completion of test

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

SQL Query type

This property determines SQL query type to be executed. The user can select one of the SQL query types from SELCET, UPDATE, INSERT and DELETE.
Explanation for types of queries is given in the following table:

SQL Configuration

Click the SQL Configuration ellipsis button  button to launch the SQL Configuration wizard which allows configuring queries that have to be executed.

Table Selection Dialog

Configuring queries requires selecting database object Tables on which actions have to be taken. The Table Selection Dialog does the required thing. This panel is shown in Figure 5.

Figure 5: Table Selection Dialog dialog box

Select the Table on which the query should execute. Filter the tables by selecting the required schema and clicking Refresh button.

Refer Object Selection in DB page to understand how to add a table.

Insert Statement Configuration

Click SQL configuration Ellipsis button next to after selecting SQL Query Type as INSERT will launch the SQL Configuration Wizard which will useful to configure Insert query.

Figure 6: SQL Configuration Dialog for Insert Query

Simple Insert Statement

Inserts a row in configured table with column values taken from input XML or with constant column values.

1. Click Add button to launch Table Selection Dialog dialog box.
2. Select required table as explained in Table Selection Dialog section. Selected table is added to the easel under Table. Primary key column, if exists, is marked with adjacent to column name.
   
   
   Figure 7: Selected table added to easel
3. Table can be changed by clicking replace selected table button and removed by clicking remove database table button.
4. If values are never to be inserted into a particular column, then that column can be unchecked (this requires column has a default value or supports null values) as shown in Figure 8.
   
   
   Figure 8: Ignoring column for insertion

5. To insert a constant value for a particular column, specify the required value in the Column Value column against the required column name.

   Icon

   • If the value is a string value it should be wrapped in single quotes (' ')
   • ? indicates value will be taken from input or from the output of another query where possible

6. Insert statement is automatically generated and shown in the text editor under SQL Statement in SQL tab. The generated SQL can be validated by pressing (check syntax) button.

   Icon

   This feature only checks for invalid tokens, it does not perform a complete syntax check.

   EnableReconfiguration: Select the check box against this option to reconfigure the query, if it is not selected then the Design tab (which is used to configure the query) will not be visible.
   
   Figure 9: Generated insert query

7. Once the query is configured, the user has to generate the input and output parameters by clicking the Generate Parameters button.
8. If the query contains any value which should be taken from input XML then the generated parameters are used to define the input and output port schemas.
9. The Add and Delete buttons are used to add and delete particular parameters from the list. Delete All button used to delete all the parameters from the list.
   
   
   Figure 10: Generated Input/Output Parameters
   

   Each of the columns is explained below.

   Column Name	Description
   BindName	This value is used to generate the schema for the query. In the Figure 10 value for EMPLOYEE_ID (field name) is changed to EMPLOYEE_I. So the schema generated would contain EMPLOYEE_I as the first element instead of default populated value, EMPLOYEE_ID.
   Bind Position	The position in the query where this value is bound to. Icon Do not change this value.
   Java TypeName	JDBC type which maps to Data Type.
   SQL TypeName	This defines the data type of this column in the database table. This should be correctly defined.

10. Click Ok to close the dialog.

Insert Statement with select

Insert rows in configured table by selecting rows from another table.

1. Follow the steps from 1 to 4 as described in section Simple Insert Statement.
2. Select SELECT Query option against Insert values using as shown in Figure 11.
   
   
   Figure 11: Option to insert values into a table using select query
   
   
3. Click Wizard… button to launch Query Builder to specify the Select query.
4. Follow the steps as described in section Select Statement Configuration.
5. The select query is automatically generated and shown in the text editor under Insert values using in Design tab as shown in Figure 12.
   
   
   Figure 12: Generated Select query which is used in Insert Query
6. Insert statement is automatically generated and shown in the text editor under SQL Statement in SQL tab. The generated SQL can be validated by pressing (check syntax) button.
   Enable reconfiguration: Select the check box against this option to reconfigure the query, if the option is not selected then the Design tab (which is used to configure the query) will not be visible. When a query is reconfigured Generate Parameters button should be clicked to generate parameters for the modified query.
   
   
   Figure 13: Generated Insert Query
7. Follow the steps from 7 and 8 as described in section Simple Insert Statement to complete the query configuration.

Delete Statement Configuration

Click ellipsis button next to SQL configuration property after selecting SQL Query Type as DELETE will launch the SQL Configuration Wizard which will useful to configure Insert statement.

Figure 14: Sql Configuration Dialog for Delete Query

Simple Delete Statement

Delete rows satisfying defined condition in configured table, with column values taken from input XML or with constant values.

1. Click add database table button to launch Table Selection Dialog dialog box.
2. Select required table as explained in Table Selection Dialog section. Selected table is added to the easel under Table
   
   
   Figure 15: Selected table added to easel
3. Table can be changed by clicking replace selected table button and removed by clicking remove database table button.
   
   Figure 16: Adding condition on column to WHERE clause
4. Specify condition which should be satisfied for deleting row under WHERE condition. Select a column name in the first column on which WHERE condition has to be applied.
5. When selecting multiple columns for where condition, conditions can be combined using AND or OR under fourth column
   
   
   Figure 17: Combining multiple conditions for WHERE clause
6. Operator of choice can be chosen from the drop-down under second column.
   
   
   Figure 18: Selecting operator for a condition
7. To specify a constant value for WHERE condition on a column, specify the required value in the third column against the required column name in where tab.
   
   
   Figure 19: Specifying constant value for a column in condition for WHERE clause
8. To specify WHERE condition on a column whose value is equal to value defined in another column, select the required column from drop-down in the third column against the required column name in where tab
   
   
   Figure 20: Specifying comparison between columns in condition for WHERE clause
9. Insert statement is automatically generated and shown in the text editor under SQL Statement in SQL tab. The generated SQL can be validated by pressing check syntax button.
   Enable reconfiguration: If this option is checked then the query can be re-configured, if the option is unchecked then the Design tab (which is used to configure the query) will not be visible.
   
   
   Figure 21: Generated Delete Query
10. Follow the steps from 7 and 8 as described in section Simple Insert Statement to complete the query configuration.

Update Statement Configuration

Figure 22: SQL Configuration Dialog for Update Query
Click ellipsis button next to SQL configuration property after selecting SQL Query Type as UPDATE will launch the SQL Configuration Wizard which will useful to configure update statement.

Simple Update Statement

Update rows satisfying defined condition in configured table, with column values taken from input XML or with constant values. Condition values can also be taken from input XML or defined as constant values.

1. Click add database table button to launch Table Selection Dialog dialog box.
2. Select required table as explained in Table Selection Dialog section. Selected table is added to the easel under Table.
   
   
   Figure 23: Selected table added to easel
3. Table can be changed by clicking replace selected table button and removed by clicking remove database table button.
4. Select the columns whose values have to be set. Figure 24 shows that NAME and AGE are selected for update
   
   
   Figure 24: Selecting column for update
5. These selected columns will automatically add under the SET tab as shown in Figure 25.
   
   
   Figure 25: Columns added to SET clause
6. Click WHERE tab and select a column name on which where condition has to be applied.
   
   
   Figure 26: Adding condition on column to WHERE clause
7. When selecting multiple columns forwhere condition, conditions can be combined using AND or OR under And/Or column.
   
   
   Figure 27: Combining multiple conditions under where clause
8. Operator of choice can be selected from the drop-down menu underOperator column.
   
   Figure 28: Selecting operator for a condition

1. Constant values can also be set to columns that have to be updated (under SET tab) or for values in where condition (under WHERE tab).
2. To update a column with a constant value, specify the required value in the Column Value column against the required column name in SET tab.

 

Figure 29: Specifying constant value for a column in SET clause

1. To specify a constant value for where condition on a column, specify the required value in the Column Value column against the required column name in WHERE tab.

Figure 30: Specifying constant value for a column in condition for WHERE clause

• ? indicates value will be taken from input or from the output of another query where possible.

1. To specify where condition on a column whose value is equal to value defined in another column, select the required column from the drop-down menu in the Column Value column against the required column name in WHERE tab.

Figure 31: Specifying comparison between columns in condition for WHERE clause

 

Enable reconfiguration: If the check box is selected the query can be re-configured, if the query is uncheck then the Design tab (which is used to configure the query) will not be visible.

Figure 32: Generated update query
Follow the steps from 7 and 8 as described in section Simple Insert Statement to complete the query configuration.

Select Statement Configuration:

Click ellipsis button next to SQL configuration property after selecting SQL Query Type as SELECT will launch the SQL Configuration Wizard which will useful to configure Insert statement.

Simple Select Statement

Retrieves data from all columns or from selected columns in a configured database table.

1. Click add database table button to launch Table Selection Dialog dialog box.
2. Select required table as explained in Table Selection Dialog section. Selected table is added to the easel under Table
   
   
   Figure 33: Selected table added to easel
    

• Table can be changed by clicking replace selected table button and removed by clicking remove database table button.
• To retrieve specific columns values from the table, check required columns to build a select query with specific columns. If no column is checked, then *SELECT ** is used. Select the columns in order in which they should appear they should appear in select clause.
  
  
  Figure 34: Selecting columns for selection
   
• Selected columns are shown under Columns tab. Check/Uncheck the check box in Output column against required column name to show/not show the corresponding column in the output XML.

For example, configuration in the following image generates IDNO in the output XML, but does not generate NAME in output XML, though values for both IDNO and NAME are retrieved from the table.

Figure 35: Selecting columns for output XML

• To define a column alias, provide the alias name under Alias column against required column name. Aliases are useful when the column name is not intuitive or too long. When an alias is specified output XML will contain an element with defined alias name instead of the column name.

Figure 36: Defining Column Alias

• To return unique rows check DISTINCT.

Figure 37: Distinct option to return unique values

 

Enable reconfiguration:

Select the check box against this option to reconfigure the query, if this option is not checked then the Design tab (which is used to configure the query) will not be visible.

Figure 38: Generated Select Query

1. Once this query is configured, generate the input and output parameters by clicking the Generate Parameters button. These generated parameters are used to define the input and output port schema.
2. If the query contains any value which should be taken from input XML then the generated parameters are used to define the input and output port schemas.
3. Add and Delete buttons are used to add and delete particular parameters from the list. Delete All button used to delete all the parameters from the list. The Output parameter list contains only those columns for which the Output column is checked under Columns tab.

Figure 39: Generated Input/Output Parameters

Each of the columns is explained below.

Click Ok to close the dialog.

Select Statement with Filter:

Retrieves data from all columns or from selected columns in a configured database table after applying specified conditions. Condition values can be provided from input XML or as constant values.

1. Follow the steps from 1 to 7 as described in the section Simple Select Statement.
2. ClickWHERE tab and select a Column name on which WHERE condition has to be applied.
   
   
   Figure 40: Adding condition on column to WHERE clause
    
3. When selecting multiple columns for WHERE condition, conditions can be combined using AND or OR under And/Or column.
   
   
   Figure 41: Adding condition on column to WHERE clause
    
4. Operator of choice can be selected from the drop-down list under Operator column.
   
   
   Figure 42: Selecting operator for a condition

• Constant values can also be set for values in WHERE condition (under WHERE tab).

  • To specify a constant value for WHERE condition on a column, specify the required value in the Column Value column against the required column name in WHERE tab.

    Icon

    • If the value is a string value it should be wrapped in single quotes (' ').
    • ? indicates value will be taken from input or from the output of another query where possible.

    
    Figure 43: Specifying constant value for a column in SET clause

• • To specify WHERE condition on a Column whose value is equal to value defined in another Column, select the required Column from drop-down list in the Column Value against the required column name in WHERE tab.
    
    
    Figure 44: Specifying comparison between columns in condition for WHERE

• Follow the steps from 8 to 10 as described in the section Simple Select Statement.

 

Select Statement with Sorting:

Retrieves sorted data from all columns or from selected columns in a configured database table. Data is sorted in configured order on columns configured for sorting.

• Follow steps 1 to 7 in the section Simple Select Statement.
• To specify columns which have to be sorted, select the appropriate sort order from drop-down list under Order By column in Columns tab. Order By for each columns has one of the following values:

 

Figure 45: Selecting sorting order for column

• When multiple columns have to be sorted, sorting priority for each column can be set under Sort Priority. Columns are sorted in order of increasing Sort Priority that is column with minimum value for Sort Priority is order first.

Figure 46: SQL Statement with different columns sorted in different order
When values of Sort Priority for multiple columns are same, columns are sorted in the order in which they appear in select clause.

• Follow the steps from 8 to 10 as described in the section Simple Select Statement.

 

Select Statement with Grouping:

Retrieves data, after applying grouping conditions, from all columns or from selected columns in a configured database table.

1. Follow steps 1 to 7 in the section Simple Select Statement.
2. Click GROUP BY tab and check under Select against the columns under Group By on which group by condition should be applied.

Figure 47: Selecting columns for grouping condition

• To filter the results click HAVING tab and define required conditions. HAVING tab has functionality similar to WHERE tab (described in Select Statement with filter).

Figure 48: Adding condition to HAVING clause

1. Select required columns under Tables.

Figure 49: Selecting required columns

• Now generate the input and output parameters as described in step 9 in section Simple Select Statement.
• Edit the Select statement is which was shown in the text editor under SQL Statement in SQL tab.

Figure 50: Generated SQL Select Query

Click the Ok button to close the dialog box.

Select Statement with Multiple Tables

Retrieves data from all columns or from selected columns from multiple configured database tables. Currently retrieving data from more than one table is not supported.

Date Time Format

Date time format to be used when any of the input parameters represent Date/Time-Stamp data types.

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 yes. 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.

1. first message: first ten rows from first query
2. second message: remaining 8 rows from first query and two update query results
3. third message: third update query result and first nine rows of second select query
4. fourth message: remaining two rows from second query.

Input

The input of the component varies with the kind of query configuration. If the query has input parameters which have to be provided dynamically, these parameters will be included in the input schema of the component if Generate Parameters button is clicked after configuring SQL.

SELECT

In case of select query, the input parameters that have to be provided will be added as child elements to the SELECT element. When the query is as shown in Sample Query 1, the input schema will contain the parameters which have to be provided for the query as shown in the Figure 51.

Sample Query 1, Select with Where and Having clauses

Figure 51: Input schema – Sample Query 1

INSERT

In case of insert query, the input parameters that have to be provided will be added as child elements to the INSERT element present in the input port schema of the component. The input schema corresponding to Sample Query 2 is as shown in the Figure 53.

Figure 52: Sample Query Simple Insert

Figure 53: Input Schema – Sample Query 2

UPDATE

In case of update query, the input parameters that have to be provided will be added as child elements to the UPDATE element present in the input port schema of the component. The input schema corresponding to Sample Query 3 is as shown in the Figure 55.

Figure 54: Sample Query Simple Update

Figure 55: Input Schema – Sample Query 3

DELETE

In case of update query, the input parameters that have to be provided will be added as child elements to the DELETE element present in the input port schema of the component. The input schema corresponding to Sample Query 4 is as shown in the Figure 57.

Figure 56: Sample Query Delete with Where clause

Figure 57: Input Schema - Sample Query 4

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 58: Input schema with ConnectionFactorySettings

Output

The output schema is auto generated based on the configuration provided. An element Result will be used to represent the result of the query configured. In case of SELECT query, an element Row with zero-many cardinality will be added to this element. Each Row element represents a single entry in the result set obtained. If Generate Parameters button is clicked after configuring SQL different elements corresponding to columns in the result set will be added as child elements to the row elements. The sample output for the Sample Query 1 is as shown in Figure 58.

Figure 58: Output Schema – Sample Query 1
In case of UPDATE, DELETE, INSERT statements an element UpdateCount will be added as child to the Result element which holds the number of rows updated as a result of execution of the query. The output schema for queries 2, 3 and 4 is as shown in Figure 59.

Figure 59: Output Schema – Sample Queries 2,3,4

Functional Demonstration

Scenario 1

Execution of a select query
Start mckoiDB present at %FIORANO_HOME%\esb\samples\mckoiDB by executing CreateMckoiDB.bat and RunMckoi.bat. Configure the DBQuery component as shown in Figure 2 and configure a select statement as described in Simple Select Statement section. Use feeder and display components (shown in Figure 60) to create a flow to send sample input and check the response respectively. DBQuery component configures to a select query. As shown in sample input (Figure 61) DBQuery takes the CUSTOMER column value and returns that particular row. The selected row will be organized as XML and it will be returned to the output port as shown in sample output (Figure 62).

Figure 60: Flow for Scenario 1
Send input message, shown in Figure 60, from feeder and notice the output similar to the one shown in Figure 61 in display.

Figure 61: Sample Input for Scenario 1

Figure 62: Sample Output for Scenario1