This section details what the IP developer has to do to implement the stored procedures that define their result set(s) at runtime.
The sequence of operations when a stored procedure with dynamic results is called is:
1. The client issues execution of a stored procedure by calling SQLPrepare/SQLExecute of a SQL statement that references a stored procedure:
Stored Procedure:
SQLPrepare( "call ? = test(1,'a', ?)" );
SQLExecute()
Native command:
SQLPrepare( "This is a native command" );
SQLExecute()
2. The OpenAccess SDK SQL engine receives this call and validates if the stored procedure name is valid by calling SCHEMA(type=DAMOBJ_TYPE_PROC). One of the properties of OA_PROC object is NUM_RESULT_SETS. A stored procedure that defines the result sets at execution time must set this value to > 1, indicating a dynamic result set (1 or more results). The OpenAccess SDK SQL engine calls SCHEMA() for PROCCOLUMNS of the static elements of the stored procedure (return value, input arguments and output arguments). In this case, the OpenAccess SDK SQL engine does not call SCHEMA() for the definition of result columns.
The stored procedure should expose the following column types from the standard SCHEMA() or in the OA_PROCCOLUMNS table:
• SQL_RETURN_VALUE – return value from a stored procedure call – retval = fz(2)
Note: In addition to these procedure columns, SCHEMA() should also return a dummy column of type SQL_RESULT_COL column to indicate that the schema has result sets. You can mark this column to be of type SQL_VARCHAR with any settings for the other parameters.
The order in which these columns are returned is significant. The SQL_RETURN_VALUE column, if any, should be returned first. The SQL_PARAM_INPUT and SQL_PARAM_OUTPUT columns should be returned in the order in which you expect the user to specify the parameter values when calling the stored procedure.
3. An IP that implements stored procedures with dynamic result sets or native commands that return result sets support a new function DYNAMIC RESULTS to be called during execution for building the dynamic result sets. The OpenAccess SDK SQL engine calls DYNAMIC RESULTS multiple times: first, to initialize, and then to allow it to add each result set.
The IP also must implement a new schema function SCHEMAEX to return information about the result sets. The IP can call dam_getIP_hstmt() to check which procedure or native command is being processed and return the result columns information for the current result set.
4. To initialize dynamic results query execution, the OpenAccess SDK SQL engine calls DYNAMIC RESULTS. During the initialization phase, the IP saves information about the first result set that is returned so that SCHEMAEX can provide the appropriate result column definitions. The IP should get the input parameters of the stored procedure and prepare for actual execution. It should save the procedure execution context by calling dam_setIP_hstmt.
When the IP is getting the input parameter values, it should check the type of the column that is being returned by dam_getFirstValueSet() and dam_getNextValueSet(), and skip when the column type is not SQL_PARAM_INPUT.
5. The OpenAccess SDK SQL engine calls SCHEMAEX (DAM_HSTMT...) to request information about the result set. This call is made to request PROC_COLUMNS if the stored procedure is being executed, and COLUMNS if the native command is being executed. The IP can use information stored in the IP_STMT_DA, with the IP DYNAMIC RESULTS call in Step 4, to describe the current result set. Here, you use the API calls for returning dynamic schema information. Refer to the "Interface Provider API" chapter of the OpenAccess SDK Programmer’s Reference for your programming language.
6. The OpenAccess SDK SQL engine calls DYNAMIC RESULTS to allow the IP to return results. It should return DAM_SUCCESS or DAM_SUCCESS_WITH_MORE_RESULT_SETS .
Note: DAM_SUCCESS_WITH_RESULT_PENDING is used to signal results for same result set.
The IP can access the IP_STMT_DA saved as part of the DAM_HSTMT to retrieve the current state of the stored procedure execution. The IP should build and return rows for the current result set. If there are more sets, then the IP should update the IP_STMT_DA with the information required to process the next set and return DAM_SUCCESS_WITH_MORE_RESULT_SETS once the current set is finished. If this is the last set, then it should return DAM_SUCCESS.
IP DYNAMIC RESULTS should ignore the dummy placeholder result column of type SQL_RESULT_COL returned by IP SCHEMA. The OpenAccess SDK SQL engine removes any result column information for the stored procedure and call IP SCHEMAEX for the result columns in the current result set before each call to IP DYNAMIC RESULTS (for example, in C/C++, ip_procedure_dynamic (stmtType=DAM_DSP_EXECUTE)). IP SCHEMAEX is called even for the first result set information.
If the stored procedure definition includes a return value or output values, the DYNAMIC RESULTS operation uses the new function dam_allocOutputRow() for returning the value of the return value and output values of the stored procedure. The IP can get column handles of the return value and output values by using dam_getCol() and add values for these by using dam_addValToRow(). The return row is then handed over to the OpenAccess SDK SQL engine by calling the function dam_addOutputRow(). The IP can choose to build the return row before it returns from this with DAM_SUCCESS.
The OpenAccess SDK SQL engine passes this information to the ODBC driver. The ODBC driver updates the ODBC client application’s output parameters with the return value before returning SQL_NO_DATA from SQLMoreResults() call in Step 4.
Returning return value and output values are not available for Native commands.
7. The OpenAccess SDK SQL engine returns the first result set to the client application. The client application executes SQLFetch () or SQLExtendedFetch until NO DATA is returned. At this point it calls SQLMoreResults to see if there are more results.
If Step 4 returns DAM_SUCCESS_WITH_MORE_RESULT_SET then the OpenAccess SDK SQL engine repeats the sequence from Step 4. Otherwise returns SQL_NO_DATA.
