Execute operation

The Execute operation is used to run stored procedures and stored functions, as well as wrapper stored procedures and stored functions. The external service wizard generates the required stored procedure business object that corresponds to the stored procedure or stored function definition in the database. The adapter uses the Execute operation to process the stored procedure business object.

The following information provides a simple example of a stored procedure, the business object that is constructed from it, and the steps used by the adapter to process the stored procedure business object with an Execute operation.

A simple example of a stored procedure:

PROCEDURE testSP(IN int x,INOUT VARCHAR(10) msgSTR, OUT int status,
                 OUT struct outrec, OUT array retArr)

The procedure returns two result sets.

For this stored procedure, following is an example of the business object that is constructed:

BOLevel ASI
      SPName=testSP
      ResultSet=true
      MaxNumberOfResultSets=2
      ReturnValue = propName 
                    Returned if the stored procedure is a function. function). 
                    Will be property name corresponding to the child business 
                    object if returned value is complex type(array/struct/resultset) 
										Defined only if it is a Function

Properties
      x Type=IP
      msgStr Type=IO
      status Type=OP
      outrec Type OP - Child BO for outrec, ASI ChildBOType = struct
      retarr Type OP - n cardinality child BO for retArr, ASI ChildBOType = array
      childBOName1 - Child BO for 1st result set, ASI ChildBOType = resultset
      childBOName2 - Child BO for 2nd result set, ASI ChildBOType = resultset

To process this stored procedure business object with an Execute operation, the adapter:

Constructs the following stored procedure call: CALL testSP(x, msgStr, status, outrec, retArr).
Sets the input parameters x and msgStr on the callable statement.
Runs the callable statement.
Obtains the return value (if Function) and sets the value in the appropriate attribute if it is a scalar value, or in a child business object if it is a complex value (such as struct, array).
Obtains the first result set and creates the container for ResultSet1.
Obtains the second result set and creates the container for ResultSet2.
Obtains the output parameters msgStr and status, and sets the corresponding attributes on the business object.
Obtains the output parameter outrec and creates the child business object from the data returned in outrec. If outrec is a nested struct type, then the adapter recursively creates and stores data in the hierarchical child business object.
Obtains the output parameter retArr and creates a multiple cardinality child business object from the data returned in retArr. If retArr is a nested array type, then the adapter recursively creates and stores data in the hierarchical child business object.