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. Refer Add Resources to a Microservice section to do this.

Configuration

Managed Connection Factory

Connection details are configured in the Managed Connection Factory (MCF) panel.

Figure below illustrates the panel with Expert Properties enabled.

Figure1: 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 the ellipsis button to launch the dialog box to configure Database settings. Details of the database to which the component should be connected are 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.

A list of Database is available to choose from the drop-down list.

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

Icon

The jar file(s) that are part of JDBC client libraries for selected vendor have to be added as resources to JDBC system library.

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

Icon

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.

User name

User name that should be used to connect to the database.

Password

Password of the specified user.

Connection ping sql

An SQL statement which is guaranteed to execute without exception, except when connection to database is lost. When an 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, for example, fixedString=true uses FIXED CHAR semantics for string values in oracle.

Icon

Please refer to documentation of the 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.

Auto Commit (Y/N)

Commit mode that should be used by the JDBC connection.

If enabled, any transactions (queries executed) will be automatically and implicitly committed to the database. This is done even before the response is generated.

If disabled, 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

Please refer the corresponding section in Common Configurations page.

Advanced Settings

Query timeout (in seconds)

Time till a Database Query needs to process and then quit.

Enable JDBC Driver Logging

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

Examples:

" " for Oracle
[ ] for Microsoft Excel
no wrap characters for MySQL.

Database object names are wrapped as shown below:

Icon

The wrap character options appear on enabling Wrap DB object names property.
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.

Testing Connection Configuration

Connection configuration can be tested by clicking the Test button in the Managed Connection Factory.

Interaction Configurations

Business logic configuration details are configured in the second panel, Interaction Configurations. Below is the snapshot of the panel with Expert Poperties view enabled.

Figure 4: Business logic configuration in Interaction configurations panel

Attributes

Auto Commit For Test

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

If enabled, any transactions (queries executed) will be automatically committed to the database while testing. Performed transactions will have to manually undone

If disabled, any transactions (queries executed) will be rolled back at the completion of test

Icon

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

SP Configuration

Click ellipsis button against property SQL configuration property to launch the SP Configuration wizard which can be used to configure call statement.

Figure 5: SP Configuration Wizard

Configuring Stored Procedure

To configure Stored Procdure, perform the following actions:

Click ellipsis button against Stored Procedure property to launch Procedure Selection dialog box. To select the Procedure/Function which has to be executed, first click the Refresh button to fetch the Schemas.

Figure 6: Procedure Selection Dialog

Filter the Procedure/Function by selecting the required schema and click OK to fill the parameters.

Figure 7: Filtering Stored Procedures based on Schemas

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

For parameters whose data type is a user-defined data type, Data Type column will be populated by the value 'OBJECT'.

Figure 9: populated Parameter of type User defined
Select the 'User Defined Type' from the DataType drop-down list as shown in the figure below 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. Icon 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.

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 containing 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 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: 3^rd 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

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

Figure 19: Result Set and parameters as output elements in output schema

When the procedure has both Result Set and parameters as output both elements will appear in output schema as shown in figure 19.

Functional Demonstration

Scenario 1

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 SP Configuration section 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

Useful Tips

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 Add Resources to a Microservice section.