Stored procedures used in place of or in addition to operations, IBM WebSphere Adapter for JDBC

IBM BPM, V8.0.1, All platforms > Authoring services in Integration Designer > Services and service-related functions > Access external services with adapters > Configure and using adapters > IBM WebSphere Adapters > JDBC > Overview of IBM WebSphere Adapter for JDBC > Technical overview
Stored procedures used in place of or in addition to operations

You can specify that the adapter use a stored procedure in the database in place of, before, or after the SQL statements that the adapter uses to perform an operation. Each business object can have a different set of stored procedures used with each operation.

The adapter can use simple SQL statements for Create, Update, Delete, Retrieve, or RetrieveAll operations. The column names used in the SQL statements are derived from an attributes application-specific information. The WHERE clause is constructed using key values specified in the business object. Each query spans one table only, unless posted to a view. However, you can replace or enhance the SQL statement provided by the adapter using stored procedures and stored functions.
The adapter can call a stored procedure or stored function in the following circumstances:

Before processing a business object, to perform preparatory operational processes
After processing a business object, to perform actions after the operation
To perform a set of operations on a business object, instead of using a simple Create, Update, Delete, Retrieve, or RetrieveAll statement.

In a hierarchical business object, if you want the stored procedure to be performed for each business object in the hierarchy, you must separately associate a stored procedure with the top-level business object and each child business object or array of business objects. If you associate a stored procedure with the top-level business object but do not associate it with each child business object, then the top-level business object is processed with the stored procedure, but the child business objects are processed using the standard SQL query.
Table 1 lists the application-specific information elements for a stored procedure and describes their purpose and use. A complete description of each element is provided in the sections that follow the table. A sample stored procedure definition is shown in Stored procedure sample.

Application-specific information for stored procedures in table and view business objects
Descriptive name Element name Purpose
Stored procedure type StoredProcedureType The stored procedure type defines the type of stored procedure to be used, and it determines when the stored procedure is called, for example, before processing a business object.
Stored procedure name StoredProcedureName The name of the stored procedure that is associated with the appropriate StoredProcedureType.
Result set ResultSet This value specifies whether the stored procedure returns a result set. If the result set is returned, a multiple-cardinality child for the current business object is created using the values returned in the result set rows.
Parameters Parameters Each Parameters element describes one parameter for a stored procedure or stored function.
Return value ReturnValue A value that indicates it is a function call, not a procedure call, because the value is returned by the function.

Stored procedure type

The stored procedure type defines the type of stored procedure to be used, and the stored procedure type determines when the stored procedure is called, for example, before processing a business object.

Stored procedure type element characteristics
Required Yes
Default None
Possible values Can be one of:

Before OperationSP
After OperationSP
OperationSP
Operation specifies one of the operation names: Create, Update, Delete, Retrieve, or RetrieveAll.
Bidirectional transformation supported No
Property type String
Usage notes Stored procedure types associated with RetrieveAll apply to top-level business objects only.
You can remove any selected application-specific information from the StoredProcedureType property. All the corresponding operation application-specific information property groups are also removed.
Examples

CreateSP: Performs the create operation
UpdateSP: Performs the update operation
BeforeCreateSP: Runs before creating a business object
AfterCreateSP: Runs after creating a business object
AfterDeleteSP: Runs after deleting a business object

Stored procedure name

The name of the stored procedure that is associated with the appropriate StoredProcedureType.

Stored procedure name element characteristics
Required Yes
Default None
Bidirectional transformation supported Yes
Property type String

Result set

This value determines whether the stored procedure returns a result set or not. If the result set is returned, a multiple-cardinality child for the current business object is created using the values returned in the result set rows.

Result set element characteristics
Required Yes
Default None
Possible values
True
False
Bidirectional transformation supported No
Property type Boolean
Usage notes Oracle users: If your stored procedure returns a result set, use the business object editor after finishing the external service wizard to verify that this attribute is set to true. The Oracle JDBC driver does not always return this value correctly.

Parameters

There is one Parameters element for each parameter for a stored procedure or stored function. Each Parameters element defines the name and type of one parameter.

Parameters element characteristics
Required Yes
Default None
Contents Each Parameters element specifies the following information:

PropertyName: Specifies the name of the business object attribute to pass as the parameter.
Type: Specifies the type of the parameter, one of the following values:

IP for input only
OP for output only
IO for input and output
RS for result set

Bidirectional transformation supported No
Property type String
Usage notes
For Oracle stored procedures, a result set can be returned only as an output parameter. In that case, one of the parameters must have the type RS, to indicate a result set.

Return value

A value that indicates it is a function call, not a procedure call, because the value is returned by the function.

Return value element characteristics
Required No
Default None
Possible values Can be RS or the name of a business object attribute or child business object.
Bidirectional transformation supported No
Property type String
Usage notes If the returned value is RS, the returned value is a result set and is used to create the multiple-cardinality container corresponding to this business object. If the returned value is the name of an attribute, the value is assigned to that particular attribute in the business object. If the attribute is another child business object, the adapter returns an error.
When you associate a stored procedure with a business object that is generated from a table or view, and if the stored procedure is a function, a value is returned from this stored procedure. One ReturnValue application-specific information value is added to the operation application-specific information. The existence of this application-specific information implies that it is a function call and not a procedure call, because a value is being retuned by the function.
If the value of this application-specific information is a business object attribute name, the returned value is assigned to that particular attribute in the business object.
If the value of this application-specific information is another child business object, the adapter run time returns an error.
In summary, if the returned value is of a simple data type, the wizard enables you to bind one business object attribute to it, and the value of this application-specific information is set to the name of that business object attribute. But if the returned value is a result set, the wizard sets the value of this application-specific information to RS.
For an Oracle database, a result set must be returned as an output parameter, not as a returned value. The type of the output parameter is set as RS, to indicate that this parameter is used to return a result set.
In a hierarchical business object, if you want the stored procedure to be performed for each business object in the hierarchy, you must separately associate a stored procedure with the top-level business object and each child business object or array of business objects. If you associate a stored procedure with the top-level business object but do not associate it with each child business object, then the top-level business object is processed with the stored procedure, but the child business objects are processed using the standard SQL query.

Stored procedure sample

The following sample shows the XML definition of the Customer business object in the RtCustomer.xsd file, showing the definition of the stored procedures for RetrieveSP and AfterRetrieveSP for the Retrieve operation. The adapter runs the RT.RETR_CUST stored procedure in place of the standard SQL to retrieve a table business object. After the business object is retrieved, the adapter runs the RT.CUSTINFO stored procedure.
<jdbcasi:JDBCBusinessObjectTypeMetadata 
   xmlns:jdbcasi="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
   <jdbcasi:TableName>RTASSER.CUSTOMER</jdbcasi:TableName 
     <jdbcasi:Operation> <jdbc asi:Name>Retrieve</jdbcasi:Name>
       <jdbcasi:StoredProcedures>
         <jdbcasi:StoredProcedureType>AfterRetrieveSP</jdbcasi:StoredProcedureType>
         <jdbcasi:StoredProcedureName>RT.CUSTINFO</jdbcasi:StoredProcedureName>
           <jdbcasi:Parameters>
             <jdbcasi:Type>IP</jdbcasi:Type>
             <jdbcasi:PropertyName>pkey</jdbcasi:PropertyName>
           </jdbcasi:Parameters>
           <jdbcasi:Parameters>
             <jdbcasi:Type>OP</jdbcasi:Type>
             <jdbcasi:PropertyName>fname</jdbcasi:PropertyName>
           </jdbcasi:Parameters>
           <jdbcasi:Parameters>
             <jdbcasi:Type>OP</jdbcasi:Type>
             <jdbcasi:PropertyName>lname</jdbcasi:PropertyName>
           </jdbcasi:Parameters>
           <jdbcasi:Parameters>
             <jdbcasi:Type>OP</jdbcasi:Type>
             <jdbcasi:PropertyName>ccode</jdbcasi:PropertyName>
           </jdbcasi:Parameters>
         </jdbcasi:StoredProcedures>
         <jdbcasi:StoredProcedures>
         <jdbcasi:StoredProcedureType>RetrieveSP</jdbcasi:StoredProcedureType>
         <jdbcasi:StoredProcedureName>RT.RETR_CUST</jdbcasi:StoredProcedureName>
           <jdbcasi:Parameters>
             <jdbcasi:Type>IP</jdbcasi:Type>
             <jdbcasi:PropertyName>ccode</jdbcasi:PropertyName>
           </jdbcasi:Parameters>
           <jdbcasi:Parameters>
             <jdbcasi:Type>OP</jdbcasi:Type>
             <jdbcasi:PropertyName>fname</jdbcasi:PropertyName>
           </jdbcasi:Parameters>
           <jdbcasi:Parameters>
             <jdbcasi:Type>OP</jdbcasi:Type>
             <jdbcasi:PropertyName>lname</jdbcasi:PropertyName>
           </jdbcasi:Parameters>
       </jdbcasi:StoredProcedures>
   </jdbcasi:Operation>
</jdbcasi:JDBCBusinessObjectTypeMetadata>
Technical overview

Descriptive name	Element name	Purpose
Stored procedure type	StoredProcedureType	The stored procedure type defines the type of stored procedure to be used, and it determines when the stored procedure is called, for example, before processing a business object.
Stored procedure name	StoredProcedureName	The name of the stored procedure that is associated with the appropriate StoredProcedureType.
Result set	ResultSet	This value specifies whether the stored procedure returns a result set. If the result set is returned, a multiple-cardinality child for the current business object is created using the values returned in the result set rows.
Parameters	Parameters	Each Parameters element describes one parameter for a stored procedure or stored function.
Return value	ReturnValue	A value that indicates it is a function call, not a procedure call, because the value is returned by the function.

Required	Yes
Default	None
Possible values	Can be one of: Before OperationSP After OperationSP OperationSP Operation specifies one of the operation names: Create, Update, Delete, Retrieve, or RetrieveAll.
Bidirectional transformation supported	No
Property type	String
Usage notes	Stored procedure types associated with RetrieveAll apply to top-level business objects only. You can remove any selected application-specific information from the StoredProcedureType property. All the corresponding operation application-specific information property groups are also removed.
Examples	CreateSP: Performs the create operation UpdateSP: Performs the update operation BeforeCreateSP: Runs before creating a business object AfterCreateSP: Runs after creating a business object AfterDeleteSP: Runs after deleting a business object