view enabled.
Managed Connection Factory Panel
Interaction Configurations Panel
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 section 3.3.3.3 in Fiorano SOA User Guide.
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 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.
1. The vendor name is marked in red color if the default JDBC driver class is not present in component's class path.
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 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.
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
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.
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, 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.
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
panel. Figure 4 illustrates the panel with expert properties
view enabled.
Figure 4: Business logic configuration in Interaction configurations panel
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.
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.
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 10 rows from first query
second message: remaining 8 rows from first query and 2nd update query results.
third message: 3rd update query result and first 9 rows of second select query.
fourth message: remaining two rows from second query.
Input schema for the component is shown in Figure 11
Figure 11: Input schema for the component
Input message (shown in Figure 12) contains only one element query whose value contains the query that has to be executed.
Figure 12: Sample input message
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.
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 13 and 14. Use feeder and display components (shown in Figure 15) to create a flow to send sample input and check the response respectively.
Figure 13: MCF Panel configuration for scenario1
Figure 14: Interactions configuration of scenario1
Use feeder and display components (shown in Figure 15) to create a flow to send sample input and check the response respectively.
Figure 15: Flow for scenario1
Send input message, shown in Figure 16, from feeder and notice the output similar to the one shown in Figure 17 in display.
Figure 16: Input for scenario1
Figure 17: Output for scenario1
Use same connection configurations as described in scenario 1 and change the Interactions Configuration as shown in Figure 18.
Figure 18: 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 19.
Figure 19: Output of scenario2
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 section 3.3.3.3 in Fiorano SOA User Guide.
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.