Embedded SQL Programming Guide

Returning Result Sets From Stored Procedures

Stored procedures invoked through DB2 CLI provide a capability not available with those invoked through embedded SQL; that is, the ability to return one or more result sets to the client application. Aspects of this support include:

• The only client API provided is via CLI.

• The client application program can describe the result sets returned.

• Result sets must be processed in serial fashion by the application. A cursor is automatically opened on the first result set and a special call (SQLMoreResults) is provided to both close the cursor on one result set and to open it on the next.

• The stored procedure indicates that a result set is to be returned by declaring a cursor on that result set, opening a cursor on that result set, and leaving the cursor open when exiting the procedure. If more than one cursor is left open, the result sets are returned in the order in which their cursors were opened (passing back any unread rows).

• The RESULT_SETS column in the DB2CLI.PROCEDURES table indicates whether or not a stored procedure can return result sets. Only procedures with a value greater than zero in this column will return result sets for open cursors.

For additional details, see the section on Using Stored Procedures in the CLI Guide and Reference.

The diagram below shows typical use of this facility:

(1)

A client application program AP calls a procedure SP providing two input arguments.

(2)

SP determines, based on those arguments, that 3 result set are to be returned.

(3)

SP provides open cursors on the statements that define the result sets.

(4)

AP processes the result sets in a general loop that is able to deal with different numbers and different types of results.

 
AP                                                                   SP
ç-------------------------------------------------------------¿      ç-----------------------------------------------¿
| /* call the procedure */                                    |      | (2)                                           |
| SQLExecDirect (HSTMT1, "{call SP(arg1, arg2) }", SQL_NTS ) (1)------- /* Process arguments to determine the       |
| (4)                                                        |-----¿ |      number and types of result sets.      */ |
| /* Find out about a result set produced by the procedure */ |    | |                                               |
| SQLNumResultCols (HSTMT, count)                             |    | | (3)                                           |
| SQLDescribeCol (HSTMT1, 1, ... )                            |    | | /* Prepare statement, declare cursor on it,   |
| SQLDescribeCol (HSTMT1, 2, ... )                            |    | |    then open a cursor on it. */               |
| ...                                                         |    | | exec sql PREPARE S1 FROM :stmt1;              |
|                                                             |    | | exec sql DECLARE C1 CURSOR FOR S1;            |
| /* Link output storage to columns of result set */          |    | | exec sql OPEN C1;                             |
| SQLBindCol (HSTMT1, 1, ... )                                |    | |                                               |
| SQLBindCol (HSTMT1, 2, ... )                                |    | | /* Do it again. */                            |
| ...                                                         |    | | exec sql PREPARE S2 FROM :stmt2;              |
|                                                             |    | | exec sql DECLARE C2 CURSOR FOR S2;            |
| /* Fetch until return code is SQL_NO_DATA */                |    | | exec sql OPEN C2;                             |
| SQLFetch (HSTMT1)                                           |    | |                                               |
| ...                                                         |    | | /* and again. */                              |
|                                                             |    | | exec sql PREPARE S3 FROM :stmt3;              |
| /* See if there are any more result sets -also closes cursor|    | | exec sql DECLARE C3 CURSOR FOR S3;            |
|    for previous result set and opens it for any new cursor. |    | | exec sql OPEN 32;                             |
| SQLMoreResults (HSTMT1)                                     |    | |                                               |
| /* If return code is SQL_SUCCESS, there is another result   |    | | ...                                           |
|    set so loop back to 4. */                                |    | |                                               |
| /* If return code is SQL_NO_DATA_FOUND, we are finished. */ |    þ---/* Return */                                  |
|                                                             |      |                                               |
þ-------------------------------------------------------------û      þ-----------------------------------------------û
 

Note that this ability to return one or more result sets to a client application is available to DB2 Universal Database clients using embedded SQL if the stored procedure resides on a server that is accessible from a DataJoiner Version 2 server. This capability may also be available on DRDA host platforms. Consult the product documentation for DataJoiner or for the DB2 DRDA host platform for more information.

[ DB2 List of Books | Search the DB2 Books ]