view enabled.
Managed Connection Factory Panel
Interaction Configurations Panel
The DBQuery component is used to configure simple queries to insert, update, delete or select records from database. The CPS of this component allows designing queries with the application of zero coding effort. 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.
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
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 mckoi database details
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, for example, of types, is done.
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).
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 value will have place holders which have to be replaced to point to correct database location, for example, In Figure 2 <hostname> is replaced with localhost IP, indicating that 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.
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 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
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.
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.
Value yes implies that logging at the driver level should be enabled. This is used as a debugging option.
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 + object name + End wrap character
Note: Providing a wrong wrap character may lead to problems
Character which should be used before the object name.
Character which should be used after the object name.
Business logic configuration details are configured in the second panel,
Interaction Configurations. Figure 4 illustrates the
panel with expert properties 
view enabled.
Figure 4 : Business in Interaction panel logic configuration
This property determines whether auto-commit should be enabled when testing from the CPS. Business logic configuration in Interaction configurations panel.
yes - Any transactions (queries executed) will be automatically committed to the database while testing. Performed transactions will have to manually undone
no - 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.
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:
|
Type of query |
Explanation |
|
INSERT |
Inserts/adds data into database table |
|
UPDATE |
Modifies existing data in database table |
|
DELETE |
Deletes data from database table |
|
SELECT |
Retrieves data from database table |
Click ellipsis button against property SQL configuration 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.
Insert Statement Configuration
Click
ellipsis button next to
SQL configuration property 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
Inserts a row in configured table with column values taken from input XML or with constant column 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. 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.
Note:
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.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check.
Enable Reconfiguration
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. Note: 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.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check
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 s atisfying 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
Note:
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 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.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check.
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 added 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 for where 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 under Operator column.
Figure 28: Selecting operator for a condition
9. 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).
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.
Note:
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 29: Specifying constant value for a column in SET clause
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.
Note:
If the value is a string value it should be wrapped in single quotes ( ).
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.
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
10.
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.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check.
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.
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.
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
3.
Table can be changed by clicking replace
selected table button and removed by clicking remove
database table button.
4. 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
5. 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
6. 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
7. To return unique rows check DISTINCT.
Figure 37: Distinct option to return unique values
8.
Select 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.
Note: This feature only checks for invalid tokens, it does not perform a complete syntax check.
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
9. 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.
10. 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.
11. 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.
|
Column Name |
Description |
|
BindName |
This value is used to generate the schema for the query. In the Figure 40 value for IDNO (field name) is changed to ID. So the schema generated would contain ID as the first element instead of default populated value, IDNO. |
|
Bind Position |
The position in the query where this value is bound to. Note: 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. |
12. 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. Click WHERE 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
5. Constant values can also be set for values in WHERE condition (under WHERE tab).
a. 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.
Notes:
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
b. 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
6. 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.
1. Follow steps 1 to 7 in the section Simple Select Statement.
2. 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:
|
Order By Value |
Explanation |
|
Unsorted |
Data is not sorted on values in the column, that is, no order by clause will added in the SQL statement. |
|
Ascending |
Data is sorted in ascending order on values in the column, that is, order by clause will be added in the SQL statement as ORDER BY <column name> ASC. |
|
Descending |
Data is sorted in descending order on values in the column, that is, order by clause will be added in the SQL statement as ORDER BY <column name> DESC. |
|
Default |
Data is sorted in default order for order by clause on values in the column, that is, order by clause will be added in the SQL statement as ORDER BY <column name>. |
Figure 45: Selecting sorting order for column
3. 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.
4. 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.
Note: Grouping functions are not provided in query builder. Grouping conditions have to be explicitly added by editing the SQL statement either before closing the SQL Configuration Wizard.
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
3. 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
4. Select required columns under Tables.
Figure 49: Selecting required columns
5. Now generate the input and output parameters as described in step 9 in section Simple Select Statement.
6. Edit the Select statement is which was shown in the text editor under SQL Statement in SQL tab.
Note: Editing Select and HAVING clauses should be last action before closing the dialog.
Figure 50: Generated SQL Select Query
7. 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.
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.
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.
first message: first ten rows from first query
second message: remaining 8 rows from first query and two update query results
third message: third update query result and first nine rows of second select query
fourth message: remaining two rows from second query.
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 52.
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.
Sample Query 52: 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 54.
Sample Query 54: 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 55.
Sample Query 56: Delete with Where clause
Figure 57: Input Schema - Sample Query 4
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 57.
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 58.
Figure 59: Output Schema Sample Queries 2,3,4
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 59) 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 60) 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 61).
Figure 60: Flow for Scenario 1
Send input message, shown in Figure 55, from feeder and notice the output similar to the one shown in Figure 56 in display.
Figure 61: Sample Input for Scenario 1
Figure 62: Sample Output for Scenario1
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.
Copyright © 1999-2008, Fiorano Software Technologies Pvt. Ltd. All rights reserved.
Copyright © 2008-2009, Fiorano Software Pty. Ltd. All rights reserved.
This software is the confidential and proprietary information of Fiorano Software ("Confidential Information"). You shall not disclose such Confidential Information and shall use it only in accordance with the terms of the license agreement enclosed with this product or entered into with Fiorano.
