Stored procedures

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. A stored procedure encapsulates a set of operations or queries for the connector to execute on an object in a database server.

The connector calls stored procedures in the following circumstances:

When it processes a hierarchical business object, the connector can use a stored procedure to process the top-level business object or any of its child business objects. However, each business object or array of business objects must have its own stored procedure.

Specifying a stored procedure

This section describes the steps you must perform to cause the connector to use a stored procedure for a business object. It contains the following sections:

Adding attributes to the business object

You must add a special kind of attribute to the business object for each type of stored procedure that the connector processes. These attributes represent only the stored procedure's type and the application-specific information that defines it. These attributes do not use the application-specific information parameters available for a standard simple attribute.

Name the attribute according to the type of stored procedure to be used. For example, to cause the connector to use AfterUpdate and BeforeRetrieve stored procedures, add the AfterUpdateSP and BeforeRetrieveSP attributes.

The connector recognizes the following business object attribute names:

BeforeCreateSP
   AfterCreateSP
   CreateSP
   BeforeUpdateSP
   AfterUpdateSP
   UpdateSP
   BeforeDeleteSP
   AfterDeleteSP
   DeleteSP
   BeforeRetrieveSP
   AfterRetrieveSP
   RetrieveSP
   BeforeRetrieveByContentSP
   AfterRetrieveByContentSP
   RetrieveByContentSP
   BeforeRetrieveUpdateSP
   AfterRetrieveUpdateSP
   RetrieveUpdateSP
   

Note:
Create an attribute only for those stored procedures that you want the connector to execute. Use the application-specific information or mapping (only if InterChange Server Express is used as the integration broker) to specify values for these attributes before the business object is sent to the connector. The connector must be restarted to recognize changes to these values for subsequent calls on a business object.

Syntax of a stored procedure

The syntax for specifying a stored procedure is:

SPN=StoredProcedureName;RS=true|false[;IP=Attribute_Name1[:Attribute_Name2[:...]]]
   [;OP=Attribute_Name1| RS[:Attribute_Name2| RS[:...]]]
   [;IO=Attribute_Name1[:Attribute_Name2[:...]]]
   

where:

StoredProcedureName
The name of the stored procedure.

RS
Is true if the stored procedure returns a result set or false if it does not. By default, false. If the value is true, the ColumnName property in an attribute's application-specific information points to the appropriate column in the result set. If RS is part of the output parameter list, then that particular parameter returns a result set. Only one result set OUTparameter is supported. If there are more than one result sets returned as OUTparameters, only the first result set is returned and all others are ignored. Currently, this feature is supported for Oracle 8i and above, stored procedures that use the Oracle JDBC Driver. For the stored procedure in the database, the corresponding parameter should return a REFCURSORtype.

IP
Input Parameters: The list of business object attributes whose values the connector should use as input values when executing the stored procedure.

OP
Output Parameters: The list of business object attributes to which the connector should return values after executing the stored procedure. See RS for a description of the result set.

IO
InputOutput Parameters: The list of business object attributes whose values the connector should use as input values and to which the connector should return values after executing the stored procedure.
Note:
The order of StoredProcedureName, RS, and parameters is important; the order of parameters among themselves is not important. In other words, it makes no difference to the connector if the stored procedure groups all parameters of each type or intersperses the types of parameters.

When multiple parameters of the same type are grouped together, separate the values with a colon delimiter; you need not repeat the parameter's name for each value. Separate parameters of different types with a semicolon delimiter. When specifying parameter values, do not put a blank space on either side of the equal sign (=).

Examples of stored procedures

The following examples use stored procedures named CustomerInsert and VendorInsert that get values from two input attributes, and return values to four output attributes. The examples illustrate different structures for stored procedures.

The connector supports only the simple data types supported by the JDBC driver.

Specifying the stored procedure

There are two ways to specify the stored procedure name and its parameter values:

Note:
If a stored procedure that handles a create, update, or delete operation is executed on a hierarchical business object containing an array of child business objects, the connector processes each child business object individually. For example, if the connector executes a BeforeCreate stored procedure, it does not process the array as a unit but processes each member in the array. When it processes a BeforeRetrieve stored procedure, the connector operates on a single business object. When it processes an AfterRetrieve stored procedure, the connector operates on all business objects returned by the retrieval.

Processing business objects using stored procedures or simple SQL statements

The following sections explain how the connector processes the stored procedures:

Business object create operations

A Create stored procedure usually returns values that the connector uses to populate the simple attributes in the top-level business object. The connector performs the following steps when processing the Create stored procedures (BeforeCreate, Create, AfterCreate):

  1. Checks whether the business object contains a BeforeCreateSP attribute. If it does, calls the BeforeCreate stored procedure.
  2. If the stored procedure returns values through output parameters, uses the values to set the value of simple attributes in the business object.
  3. Creates the single-cardinality child business objects.
  4. Sets each of the top-level business object's foreign key values to the primary-key value of each single-cardinality child business object.
  5. Checks whether the business object contains a CreateSP attribute. If it does, calls the Create stored procedure to create the top-level business object. If it does not, builds and executes an INSERT statement to create the top-level business object.
  6. If the Create stored procedure returns values through output parameters, uses the values to set the value of simple attributes in the business object.
  7. Sets the foreign-key value in each multiple-cardinality child to the value of its parent's primary-key attribute.
  8. Creates the multiple-cardinality child business objects.
  9. Checks whether the business object contains an AfterCreateSP attribute. If it does, calls the AfterCreate stored procedure.
  10. If the stored procedure returns values through output parameters, uses the values to set the values of simple attributes in the business object.

The connector can use values returned in step 10 to change the values of a business object that it created in steps 3 or 5.

Business object update operations

An Update stored procedure usually returns values that the connector uses to populate the simple attributes in the top-level business object. The connector performs the following steps when processing the Update stored procedures (BeforeUpdate, Update, AfterUpdate):

  1. Checks whether the business object contains a BeforeUpdateSP attribute. If it does, calls the BeforeUpdate stored procedure.
  2. If the BeforeUpdate stored procedure returns values through output parameters, uses the values to set the value of simple attributes in the business object.
  3. Updates the single-cardinality child business objects.
  4. Sets each of the top-level business object's foreign-key values to the primary-key value of each child business object contained with single cardinality.
  5. Checks whether the business object contains an UpdateSP attribute. If it does, calls the Update stored procedure to update the top-level business object. If it does not, builds and executes an UPDATE statement to update the top-level business object.
  6. If the Update stored procedure returns values through output parameters, uses the values to set the value of simple attributes in the business object.
  7. Sets foreign-key values in the multiple-cardinality children to reference the value in the corresponding primary-key attributes in the parent.
  8. Updates the multiple-cardinality child business objects.
  9. Checks whether the business object contains an AfterUpdateSP attribute. If it does, calls the AfterUpdate stored procedure.
  10. If the stored procedure returns values through output parameters, uses the values to set the value of simple attributes in the business object.

Business object delete operations

A Delete stored procedure does not return values to the connector. The connector performs the following steps when processing the Delete stored procedures (BeforeDelete, Delete, AfterDelete):

  1. Checks whether the business object contains a BeforeDeleteSP attribute. If it does, calls the BeforeDelete stored procedure.
  2. Deletes the single-cardinality child business objects.
  3. Deletes the multiple-cardinality child business objects.
  4. Checks whether the business object contains a DeleteSP attribute. If it does, calls the Delete stored procedure to delete the top-level business object. If it does not, builds and executes a DELETE statement.
  5. Checks whether the business object contains an AfterDeleteSP attribute. If it does, calls the AfterDelete stored procedure.

Business object retrieve operations

For simple RETRIEVE operations, stored procedures can be used for top-level business object, single cardinality children, as well as multiple cardinality children. The order of the procedures is as follows:

The connector creates a temporary object to retrieve a single cardinality child business object or a multiple cardinality child business object. The connector applies the BeforeRetrieve stored procedure to the temporary business object. The AfterRetrieve stored procedure is applied to each of the child objects retrieved for the container.

The connector executes the AfterRetrieve stored procedure after it executes a Retrieve query generated dynamically from the business object meta-data or stored procedure on the business object.

According to the JDBC specification there are three types of StoredProcedure calls as follows:

The connector supports the first two types. It will process the ResultSet that is returned from StoredProcedure.

In the stored procedure syntax, if RS=true, the result set from the stored procedure is processed. If RS=false, the result set is not processed. By default the value of RS is false. After the result set values are processed, the stored procedure output variables are processed. If RS=true, multiple cardinality children cannot specify the output variables in the related stored procedure.

Note:
Result set processing is supported only for Retrieve verb operations and for RetrieveSP only.

Processing result set returned from retrieve stored procedure (RetrieveSP):

ResultSetMetaData is obtained for the result set returned from the stored procedure. Values of all the columns in the result set are obtained and set on the corresponding attribute of the business object. The ColumnName property of an attribute's application-specific information should contain the ResultSet column name to match the attribute to the column.

For single cardinality objects, the corresponding result set should consist of only one row. If multiple rows are returned in the result set, an error is reported.

For multiple cardinality children, multiple rows can be returned through the result set. For each row returned, a new object is created and added to the container. The container is then added to the parent object at the required attribute index.

Business object RetrieveByContent operations

For simple RetrieveByContent operations, stored procedures can be used only for the top-level business object and its single-cardinality children; that is, they cannot be used to return a result set or multiple rows. The order of the procedures is as follows:

The connector creates a temporary object to retrieve a single cardinality child business object or a multiple cardinality child business object. For multiple cardinality business objects, the connector applies the BeforeRetrieveByContent stored procedure to the temporary business object. The AfterRetrieveByContent stored procedure is applied to each of the child objects retrieved for the container.

The connector executes the AfterRetrieveByContent stored procedure after it executes a RetrieveByContent query generated dynamically from the business object meta-data or stored procedure on the business object. In this case, even though the retrieval of a hierarchical business object also retrieves its child business objects, the connector executes the AfterRetrieveByContent stored procedure on every business object present in the array.

Business object Retrieve-for-Update operations

The following stored procedures are called on the top-level business object and retrieve all child business objects in the same way as the simple Retrieve.

The order of the procedures is as follows:

These stored procedures perform the same operations as BeforeRetrieve and AfterRetrieve. They have distinguishing names so that you can create separate attributes to cause the connector to perform both BeforeRetrieve and BeforeRetrieveUpdate operations, as well as AfterRetrieve and AfterRetrieveUpdate operations.

The connector creates a temporary object to retrieve a single cardinality child business object or a multiple cardinality child business object. For multiple cardinality business objects, the connector applies the BeforeRetrieveUpdate stored procedure to the temporary business object. The AfterRetrieveUpdate stored procedure is applied to each of the child objects retrieved for the container.

The connector executes the AfterRetrieveUpdate stored procedure after it executes a RETRIEVE query generated dynamically from the business object meta-data or stored procedure on the business object. In this case, even though the retrieval of a hierarchical business object also retrieves its child business objects, the connector executes the AfterRetrieveUpdate stored procedure on every business object present in the array.

Copyright IBM Corp. 1997, 2003