view enabled.
The DBProc component is used to execute Database Stored Procedures. The CPS of this component allows configuring stored procedures for execution using the Design mode in CPS. There is no coding effort involved in the configuration.
Points to note
Only one stored procedure can be configured in the adapter. Please use the DB component if multiple stored procedures need to be configured for a single instance.
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 library and not as resource to the DBProc component itself. To add JDBC drivers to DBProc component please refer to section 3.3.3.3 of Fiorano SOA User Guide or Fiorano SOA Online Help.
Connection details are configured in the Managed Connection Factory (MCF) panel.
Figure below illustrates the panel with expert properties view enabled.
Figure1: Connection configuration details in MCF panel
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.
Click ellipsis button 
to launch Database
Configuration panel shown in figure 2. Details of the database
to which the component should be connected and configured in this panel.
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 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 and so on.
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
Driver class name that should be used to connect to the database. On selecting required value for Database, Driver value is 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 the correct database location.
Example: In figure 2 <hostname> is replaced with localhost IP, indicating that the database is running on a local machine.
Username
User name that should be used to connect to the database.
Password
Password of 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 as 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.
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 gets 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. For
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 (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.
Start wrap character
Character which should be used before the object name.
End wrap character
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 logic configuration in Interaction configurations panel
Auto Commit For Test
This property determines whether auto-commit should be enabled when testing from the CPS.
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.
1.
Click ellipsis button 
against
property SQL
configuration property will launch
the SP
Configuration wizard which can be used to configure call
statement.
Figure 5: SP Configuration Wizard
2. Click ellipsis button against Stored Procedure to launch Procedure Selection dialog box. Select the Procedure/Function which has to be executed.
Figure 6: Procedure Selection Dialog
3. We can filter the Procedure/Function by selecting the required schema and clicking Refresh button.
Figure 7: Filtering Stored Procedures based on Schemas
4. Parameters and their configurations are automatically populated.
Figure 8: Populated Parameters
|
Column |
Description |
|
Parameter |
Name of the parameter for named parameters, blank otherwise |
|
Parameter Type |
Type of parameter – IN, OUT, INOUT, UNKOWN, RETURN, RESULT Values of type OUT, INOUT, RETURN, RESULT form output structure |
|
Data Type |
SQL data type of the parameter |
|
Sample Data |
NA |
5. For parameters whose data type is a user defined data type Data Type column will be populated by value OBJECT as shown in figure 9.
Figure 9: populated Parameter of type User defined
We need to explicitly select the user defined data type from User Types Selection dialog. Select the User Defined Type from the Data Type drop-down list as shown in figure 10 to launch User Types Selection dialog.
Figure 10: Selecting User Defined Type
Select the appropriate data type from the dialog.
After selecting user defined data type, it will populate in the Data Type column for the respective parameter.
Figure 11: User Types Selection Dialog to select Data type
Figure 12: Populated User Defined Datatype
We need to generate the Input/Output parameters by clicking the Generate Parameters button. Then the input/output parameters will be generated as shown in below figure. After the generation of input/output parameters these parameters will be included in the input and output port xsd's.
Figure 13: 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 above figure 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. |
|
|
BindPosition |
The position in the query where this value is bound to. Note: Do not change this value. |
|
|
JavaTypeName |
JDBC type which maps to Data Type. |
|
|
SQlTypeName |
This defines the data type of this column in the database table. This should be correctly defined. |
Returns Resultset determines whether the configured stored procedure returns resultsets or not.
Click Ok to close the dialog.
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 containing 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 3 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 update query results
third message: 3rd update query result and first 9 rows of second select query
fourth message: remaining 2 rows from second query.
Input
The input schema is auto generated based on the configuration provided. When Generate Parameters button is clicked the input parameters required for the execution of the procedure will be added as child elements to the CALL element in the input schema as shown in figure 14.
Figure 14: Input schema for Procedure with input parameter param1
The input XML to the component will thus be in the format shown in figure 15.
Figure 15: Sample XML corresponding to the Input Schema
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 16: Input schema with ConnectionFactorySettings
Output Schema
This is auto generated based on the configuration provided. When Generate Parameters button is clicked the output parameters, if any, required for the execution of the procedure will be added as child elements to the RESULT element in the input schema as shown in figure 17.
Figure 17: Output schema when there are output parameters
Figure 18: Output Schema when the Procedure returns result set
If the Return Result Set is set to true then an element ResultSet will be added and results appear as row elements in the output XSD as shown in figure 18.
When the procedure has both Result Set and parameters as output both elements will appear in output schema as shown in figure 19.
Figure 19: Result Set and parameters as output elements in output schema
Execution of a Stored Procedure:
Start mckoiDB present at %FIORANO_HOME%\esb\samples\mckoiDB by executing CreateMckoiDB.bat and RunMckoi.bat. Configure the DBProc component as described in section SP Configuration. Use feeder and display components (shown in figure 20) to create a flow to send sample input and check the response respectively.
Figure 20: Configure the DB Proc component
Figure 21: Demonstrating Scenario 1 with Sample Input
Figure 22: Demonstrating Scenario 1 with Sample output
Only one stored procedure can be configured in the adapter. Please use the DB component if multiple stored procedures need to be configured for a single instance.
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 library and not as resource to the DBProc component itself. To add JDBC drivers to DBProc component please refer to section 3.3.3.3 of Fiorano SOA User Guide or Fiorano SOA Online Help.
