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 procedure business object overview

You can create a stored procedure business object that corresponds to a stored procedure or stored function in the database.

You can then use the Execute operation to run the stored procedure against the data in the database.

The external service wizard helps you build stored procedure business objects that run a stored procedure or stored function. The wizard examines the stored procedure or stored function in the database to create the business object. A stored procedure business object has an attribute for each parameter.

If the database schema has multiple stored procedures with the same name, and the stored procedures have different parameters, the external service wizard cannot identify which stored procedure is selected. Hence, the stored procedures must have unique names.

If a parameter attribute has a simple data type, there is an attribute for a sample value for the parameter. The wizard uses the sample values when you validate the stored procedure before saving it. The adapter uses the result from the stored procedure to validate the parameters, to obtain the maximum number of result sets returned, and to be able to use the metadata of these result sets to generate child business objects. The wizard generates the hierarchy for stored procedure business objects automatically, if you validate the stored procedure business object.

If the stored procedure has input or output parameters, or return value parameters that are complex data types such as Struct, Array, or result set, you need to select the corresponding data type for each such parameter in the wizard and provide the name of the corresponding user-defined type. For Struct or Array type parameters, you must also provide the name of the corresponding user-defined type name, which is saved in the property SPComplexParameterTypeName.

For example, if you create a Struct object named Struct_TEMP in the database, and you set the type as one input parameter, then you need to set the value of this property to Struct_TEMP. The wizard uses this type name to determine the metadata to generate for the corresponding child business object. If the stored procedure returns result set, you need to set the number of result sets returned from this stored procedure in the property MaxNumberOfResultSets. This value represents the maximum number of returned result sets that are handled by the adapter run time.

During discovery and at run time, IBM WebSphere Adapter for JDBC expects the returned result set from the stored procedure execution to contain columns with names. Some stored procedures return result set with unnamed columns.

For example, a stored procedure with the SQL statements like the examples that follow return result set with unnamed columns:

SELECT COUNT(*) FROM EMPLOYEE;
SELECT 111,222,333 FROM CUSTOMER;

Oracle processes such SQL SELECT statements by assigning "dummy" names to the table columns in the returned result set- like count(*) or d1, d2, d3 for the respective SELECT statement in the examples shown.

If the returned result set contains table columns with no names (because the database did not assign dummy names), the adapter creates dummy names for such columns.

Dummy column names, generated by either the database or by the adapter, are assigned to the attributes of the stored procedure business object.

The behavior (by the adapter or by the database) of assigning dummy names to unnamed table columns ensures that the stored procedure runs successfully during discovery and at run time.

For stored procedure business objects, the wizard supports nested Struct and Array objects, and can support any number of layers of nested hierarchy. The wizard can generate corresponding child business objects for all these nested Struct and Array objects.

Complex data type properties for stored procedure business objects

Property name	Type	Description
SPComplexParameterType	String	Value can be one of: Array ResultSet Struct
SPComplexParameterTypeName	String	The name of the user-defined type. This property is required when the value of SPComplexParameterType is Struct or Array.
MaxNumberOfResultSets	Integer	The maximum number of returned result sets to be handled by the adapter run time. The wizard creates this number of business objects.

Technical overview