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.
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:
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
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:
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 (=).
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.
SPN=CustomerInsert;RS=false;IP=LastName:FirstName;OP=CustomerName: CustomerID:ErrorStatus:ErrorMessage;IO=VendorID
SPN=VendorInsert;RS=false;IP=LastName;OP=CustomerName: CustomerID:ErrorStatus;IP=FirstName;IO=VendorID;OP=ErrorMessage
The connector supports only the simple data types supported by the JDBC driver.
There are two ways to specify the stored procedure name and its parameter values:
If the length of the text that specifies the stored procedure is less than or equal to 4 KiloBytes, you can specify the value in the attribute's AppSpecificInfo property. You can use this property to specify the stored procedure regardless of whether the connector has polled for the business object (that is, the business object represents an application event) or has received the business object as an integration broker request.
The following example illustrates specification of the stored procedure in application-specific information. In this case, the value specified for the MaxLength property is not important to the stored procedure.
[Attribute] Name = BeforeCreateSP Type = String MaxLength = 15 IsKey = false IsRequired = false AppSpecificInfo =SPN=ContactInsert;IP=LastName:FirstName;OP=CustomerName: CustomerID:ErrorStatus:ErrorMessage [End]
If the length of the text that specifies the stored procedure is more than 4 KiloBytes, you must use mapping to specify the stored procedure. You can use mapping to specify the stored procedure only if the business object represents an integration broker request. In other words, you cannot use an attribute's value to specify a stored procedure when the connector is polling for events.
If the text of the stored procedure is longer than 4 KiloBytes and you use mapping to specify it, remember to expand the value of the MaxLength property to accommodate the full text.
The following sections explain how the connector processes the stored procedures:
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):
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.
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):
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):
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.
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.
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.
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.