Returning result sets from C and C++ procedures
You can develop C and C++ procedures that return result sets to a calling routine or application that is implemented using an API that supports the retrieval of procedure result sets.
Most APIs support the retrieval of procedure result sets, however embedded SQL does not.
The C and C++ representation of a result set is an SQL cursor. Any SQL cursor that has been declared, opened, and not explicitly closed within a procedure, prior to the return of the procedure can be returned to the caller. The order in which result sets are returned to the caller is the same as the order in which cursor objects are opened within the routine. No additional parameters are required in the CREATE PROCEDURE statement or in the procedure implementation in order to return a result set.
- Prerequisites
A general understanding of how to create C and C++ routines will help you complete the steps in the following procedure for returning results from a C or C++ procedure.
Cursors declared in C or C++ embedded SQL procedures are not scrollable cursors.
- Procedure
To return a result set from a C or C++ procedure:
In the CREATE PROCEDURE statement for the C or C++ procedure you must specify along with any other appropriate clauses, the DYNAMIC RESULT SETS clause with a value equal to the maximum number of result sets that are to be returned by the procedure.
No parameter marker is required in the procedure declaration for a result set that is to be returned to the caller.
In the C or C++ procedure implementation of your routine, declare a cursor using the DECLARE CURSOR statement within the declaration section in which host variables are declared. The cursor declaration associates an SQL with the cursor.
Within the C or C++ routine code, open the cursor by executing the OPEN statement. This executes the query specified in the DECLARE CURSOR statement and associates the result of the query with the cursor.
Optional: Fetch rows in the result set associated with the cursor using the FETCH statement.
-
Do not execute the CLOSE statement used for closing the cursor at any point prior to the procedure's return to the caller. The open cursor will be returned as a result set to the caller when the procedure returns.
When more than one cursor is left open upon the return of a procedure, the result sets associated with the cursors are returned to the caller in the order in which they were opened. No more than the maximum number of result sets specified by the DYNAMIC RESULT SETS clause value can be returned with the procedure. If the number of cursors left open in the procedure implementation is greater than the value specified by the DYNAMIC RESULT SETS clause, the excess result sets are simply not returned. No error or warning will be raised by the database manager in this situation.
Once the creation of the C or C++ procedure is completed successfully, you can invoke the procedure with the CALL statement from the Db2® Command Line Processor or a Db2 Command Window to verify that the result sets are successfully being returned to the caller.