Adapter for JDBC 10.3 | webMethods Adapter for JDBC Documentation | webMethods Adapter for JDBC Installation and User’s Documentation | Adapter Services | Configuring StoredProcedureWithSignature Services | Creating StoredProcedureWithSignature Adapter Services
 
Creating StoredProcedureWithSignature 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 StoredProcedureWithSignature services.
*To configure a StoredProcedureWithSignature 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 StoredProcedureWithSignature 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
Name of the catalog. The default for the catalog name is current catalog.
Schema Name
Name of the schema. The default for the schema name is current schema.
Note:
You can disable the schema lookup performed on the database by configuring the Schema Name parameter in the Configuration tab.
Procedure Name Pattern
To save time, you can type all or part of the procedure name in this field to narrow your search. This is helpful when dealing with a large database that has a long list of procedures. Use % as a multi-character wildcard and _ (underscore) as a single character wildcard.
Note:
With DB2 databases, functions do not appear in the list of procedure names. Only procedures appear in the list.
Note:
The Procedure Name Pattern field value is not considered if you disable the store procedure lookup performed on the database.
Procedure Name
Select the stored procedure name, depending on the how you set the Procedure Name Pattern field. If you select <All Procedures>, this field lists all of the procedures in the selected catalog and schema.
Note:
You can disable the store procedure lookup performed on the database by configuring the Procedure Name parameter in the Configuration tab.
Specific Name (Only for DB2 and DB2AS400)
Select the specific name for a stored procedure in a DB2 or DB2 AS/400 database after specifying the Procedure Name.
a. The top table on the Call tab lists the following fields and values based on the signature for the stored procedure:
Field
Description/Action
Parameter Name
Stored procedure parameter name.
SQL Type
SQL data type of the database column.
JDBC Type
JDBC data type of the stored procedure parameter.
Parameter Type
Defines the parameter type as IN, INOUT, or OUT. If you select IN or INOUT, you may also set the input expression in the Expression field.
Expression*
Sets the input for the IN or INOUT parameter types only. The RETURN or OUT parameters will appear automatically on the Call tab's bottom table. For a list of the allowable expression settings by parameter type, and how each parameter will map to the input or output fields, see the following table.
The following table shows valid expressions by parameter type:
Parameter Type
Expression
Input or Output Mapping?
RETURN
Empty (default)
Output field
OUT
Empty (default)
Output field
IN
? (default)
Input field
Fixed value
No mapping
INOUT
? (default)
Input and output field
Empty
Output field
ORACLE CURSOR (INOUT)
Empty (default)
Output field
b. The middle table on the Call tab lists the following input parameters and values for the stored procedure that will map to the input fields of the service:
Field
Description/Action
Input Parameter Name
Stored procedure input parameter name.
SQL Type
The SQL data type of the database column.
JDBC Type
JDBC data type of the input parameter.
Input Field
Name of any input parameters.
Input Field Type
Input parameter Java data type. For a list of JDBC type to Java data type mappings, see JDBC Data Type to Java Data Type Mappings.
c. The bottom table on the Call tab lists the following output parameters and values for the stored procedure that will map to the output of the service, including the OUT or INOUT parameters:
Field
Description/Action
Output Parameter Name
Stored procedure output parameter name.
SQL Type
SQL data type of the database column.
JDBC Type
JDBC data type of the output parameter.
Output Field
Name of the output parameter.
Output Field Type
Output parameter Java data type. For a list of JDBC to Java data type mappings, see JDBC Data Type to Java Data Type Mappings.
d. Specify the query time out value of the StoredProcedureWithSignature service you are configuring in the following field:
Field
Description/Action
Query Time Out
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. Select the Configuration tab to configure the lookup parameters for stored procedure. Use the following fields to set the parameters and values:
Field Name
Use Lookup
Schema Name
Performs a lookup on the database for schema name. Possible values are:
*true. Default. Performs a lookup on the database for schema name.
*false. Skips the lookup on the database for schema name. The Schema Name field is now editable and you can enter the value.
Procedure Name
Performs a lookup on the database for procedure name. Possible values are:
*true. Default. Performs a lookup on the database for procedure name.
*false. Skips the lookup on the database for procedure name. The Procedure Name field is now editable and you can enter the value.
Note:
The value in the Procedure Name Pattern field is not considered.
8. If the procedure returns a result set, select the ResultSet tab to specify result set parameters using the fields in the following table.
StoredProcedureWithSignature services can support multiple results sets. The result set can contain nested cursors.
Note:
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.
Note:
While all the tables in the Call tab will be updated automatically if the selected stored procedure changes, the ResultSet tab information is not updated automatically. To update this information, you must manually update the fields in the ResultSet tab.
Provide values for the following parameters:
Field
Description/Action
Result Set Index
An index is automatically assigned to each result set. The first row default value is 1.
Note:
When using for Oracle database, this field is not required.
Result Set Name
Name of the result set you want to create.
Note:
When using for Oracle database, this field is not required.
Result Set Name (from second row)
Select result set name.
Column Name
Name of the column of the result set.
JDBC Type
JDBC data type of the result column.
Output Type
Java data type of the result column. For a list of JDBC to Java data type mappings, see JDBC Data Type to Java Data Type Mappings.
9. From the File menu, select Save.