view enabled.
Managed Connection Factory Panel
Interaction Configurations Panel
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 lib and not as resource to the DBProc component itself. To add JDBC drivers to DBProc component, please refer to section 3.3.3.3 in Fiorano SOA User Guide.
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
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 Schema
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
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 16.
Figure 16: Output schema when there are output parameters
Figure 17: 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 17.
When the procedure has both Result Set and parameters as output both elements will appear in output schema as shown in figure 18.
Figure 18: 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 19) to create a flow to send sample input and check the response respectively.
Figure 19: Configure the DB Proc component
Figure 20: Demonstrating Scenario 1 with Sample Input
Figure 21: 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 lib and not as resource to the DBProc component itself. To add JDBC drivers to DBProc 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.
