Adapter for JDBC 10.3 | webMethods Adapter for JDBC Documentation | webMethods Adapter for JDBC Installation and User’s Documentation | Adapter Services | Configuring StoredProcedure Services | Creating StoredProcedure Adapter Services
 
Creating StoredProcedure Adapter Services
You configure Adapter for JDBC services using Designer.
Be sure to review the sections Before Configuring or Managing Adapter Services and Considerations when Configuring StoredProcedure Adapter Services before you configure StoredProcedure services.
*To configure a StoredProcedure adapter service
1. In Designer, right-click the package in which the service should be contained and select New > Adapter Service.
2. Select the parent namespace, type a name for the adapter service and click Next.
3. Select Adapter for JDBC as the adapter type and click Next.
4. Select the appropriate Adapter Connection Name and click Next.
5. From the list of available templates, select the StoredProcedure template and click Finish.
The adapter service editor for the adapter service appears. You can select the Adapter Settings tab at any time to confirm adapter service properties such as the Adapter Name, Adapter Connection Name, and Adapter Service Template, as necessary.
6. Select the Call tab to specify the stored procedure to call. Use the following fields to set the Call parameters:
Field
Description/Action
Catalog Name
The name of the catalog. The default for the catalog name is current catalog.
Schema Name
The name of the schema. The default for the schema name is current schema.
Enable Procedure Name Lookup (Optional)
To type in the Procedure Name, set this field to False. To select the Procedure Name from a list, set this field to True. The default is False. To save you time, use the default value (typing the name) if you know the name of the procedure and you are working with a large database which may have a long list of procedures.
Procedure Name
Type or select the stored procedure name, depending on how you set the Enable Procedure Name Lookup field.
JDBC Type
Specify the JDBC type of the corresponding return field for the stored procedure.Use the icon to create new rows as needed. You can use the icon to fill in all rows to the table.
Return Field Name
Add return field names for the stored procedure.Use the icon to create new rows as needed. You can use the icon to fill in all rows to the table.
Query Time Out
Specify the query time out value in seconds.
This value is the amount of time Adapter for JDBC waits for the service to execute before stopping the SQL operation.
The time out specified in the Query Time Out field is not guaranteed but depends on the implementation specific to the driver vendor. The JDBC standard Statement.SetQueryTimeout() method relies on the Statement.cancel() method. When execution takes longer than the specified time-out interval, the monitor thread calls Statement.cancel(). In some cases, because of a limitation in the Statement.cancel() method, the time out does not free the thread that invoked the Statement.execute() method and this may lead to higher waiting times.
The default value is -1. Use the default value to have the service use the value indicated on the watt.adapter.JDBC.QueryTimeout property as the time out. If you specify a value equal to 0, or if the watt.adapter.JDBC.QueryTimeout property is not set, the service executes without a time out. If you specify a value greater than 0, the service executes with the specified value as the time out.
Note:
-1 is the only permissible negative value for this field.
For more information about the watt.adapter.JDBC.QueryTimeout property, see Forcing a Timeout During Long-Running SQL Operations in Services and Notifications.
7. Use the Parameter tab to specify the stored procedure's parameters.
8. Use the icon to create new stored procedure parameters as needed. You can use the icon to fill in all rows to the table.
Field
Description/Action
Param JDBC Type
The JDBC type of the stored procedure parameter.
Param Name
The stored procedure parameter name.
Param Type
Define the parameter type as IN, INOUT, or OUT.
Expression
The default value is ?, which acts as a placeholder for the variable so that you can set the input variable for that column at run time, or get input external to this adapter service. It adds one row with the same column name to the table. You can also type a fixed value as input now or at run time. If you choose to type a fixed value, you type a stored procedure call statement with values you set using this field.
Input Name
The name of any input parameters.
Input Type
The input parameter Java type. For a list of JDBC type to Java type mappings, see JDBC Data Type to Java Data Type Mappings.
Output Name
The name of any output parameters.
Output Type
The output parameter Java type. For a list of JDBC type to Java type mappings, see JDBC Data Type to Java Data Type Mappings.
9. If the procedure returns a result set, select the ResultSet tab to specify result set parameters using the fields in the following table.
Note:
StoredProcedure services can support multiple results sets. The result set can contain nested cursors.When using the result set that contains nested cursors, the performance of Adapter for JDBC could degrade. Since the nested cursors are recursively processed, Adapter for JDBC may also return data that may not be required.
Use the icon to create additional result sets as needed. Use the following fields:
Field
Description/Action
Result Set Index
An index is automatically assigned to each result set. The first row default value is 1.
Result Set Name
The name of the result set you want to create.
Result Set Name (from second row)
Select result set name.
Column Name
The name of the column of the result set.
JDBC Type
The JDBC type of the result column.
Output Type
The Java type of the result column. For a list of JDBC type to Java type mappings, see JDBC Data Type to Java Data Type Mappings.
10. From the File menu, select Save.