The OpenAccess SDK SQL engine supports execution of stored procedures that are invoked by a client application. A stored procedure execution is handled by the PROCEDURE function in the IP. An IP supports stored procedures by:
1. Marking them as supported in the support array.
2. Providing a function in the IP to handle the PROCEDURE call.
3. Making entries in the OA_PROC and OA_PROCCOLUMNS tables to register the stored procedure name and the associated input parameters, output columns, output parameters and return value.
Execution of the stored procedure is handled by the following sequence of steps:
1. The PROCEDURE function in the IP is called.
2. Call dam_describeProcedure to obtain the name of the procedure and the associated user data.
3. Call dam_getInputRow to obtain the input arguments.
4. Call dam_getFirstValueSet and dam_getNextValueSet to navigate through the row containing the input arguments.
5. Call the function(s) that are associated with the named procedure with the arguments that were obtained from the input row.
6. If the stored procedure has marked some of its columns as SQL_RESULT_COL, it can return the result rows using the following steps:
a. Get the column handles of each of the result columns using dam_getCol() function.
b. For each result row to be returned, call:
• dam_allocRow() to allocate an empty row.
• dam_addValToRow() to add column values for each of the result columns.
• dam_addRowToTable() to add the row to the result set.
7. If the stored procedure has marked some of its columns as SQL_RETURN_VAL or SQL_PARAM_OUTPUT, it can return the output rows using the following steps:
a. Get the column handles of each of the result columns, using dam_getCol().
b. Allocate an empty row for return value and output parameters, using dam_allocOutputRow().
c. Add column values for the return value column and/or each of the output parameter columns, using dam_addValToRow().
d. Add the row to the result set, using dam_addOutputRow().
8. Set the number of result rows return value.
9. Return DAM_SUCCESS or DAM_FAILURE.
The OpenAccess SDK SQL engine allows the IP to build result rows without requiring it to specify values for all the result columns as defined in the schema entry for this stored procedure. This functionality is useful to implement stored procedures that can return different result columns based on its input arguments. The IP can define all possible result columns in the schema definition, but while building the result rows, skip adding values for some of the columns.
