Call Level Interface Guide and Reference

Returning Result Sets from Stored Procedures

DB2 CLI provides the ability to retrieve one or more result sets from a stored procedure call, provided the stored procedure has been coded such that one or more cursors, each associated with a query, has been opened and left opened when the stored procedure exits. If more than one cursor is left open, multiple result sets are returned.

Processing within the CLI Application

DB2 CLI applications can retrieve result sets after the execution of a stored procedure that has left cursor(s) open by doing the following:

• Before the stored procedure is called, ensure that there are no open cursors associated with the statement handle.

• Call the stored procedure.

• The execution of the stored procedure CALL statement effectively causes the opening of the cursor(s) associated with the result set(s).

• Examine any output parameters that have been returned by the stored procedure. For example, the procedure may have been designed so that there is an output parameter that indicates exactly how many result sets have been generated.

• The DB2 CLI application can then use all the normal functions that it has available to process a regular query. If the application does not know the nature of the result set or the number of columns returned, it can call SQLNumResultCols(), SQLDescribeCol() or SQLColAttribute(). Next, it can choose to use any permitted combination of SQLBindCol(), SQLFetch(), and SQLGetData() to obtain the data in the result set.

• When SQLFetch() has returned SQL_NO_DATA_FOUND or if the application is done with the current result set, the application can call SQLMoreResults() to determine if there are more result sets to retrieve. Calling SQLMoreResults() will close the current cursor and advance processing to the next cursor that has been left open by the stored procedure.

• If there is another result set, then SQLMoreResults() will return success; otherwise, an SQL_NO_DATA_FOUND is returned.

• Result sets must be processed in serial fashion by the application.

Programming Stored Procedures to Return Result Sets

DB2 Universal Database stored procedures must satisfy the following requirements to return one or more result sets to a CLI application:

• The stored procedure must be run in fenced mode. If this is not the case then no result sets will be returned, and no error is generated. For more information on fenced and unfenced stored procedures refer to the Embedded SQL Programming Guide

• The stored procedure must be run on a remote server. This means there must be a network or named pipe connection used. A 'loopback' connection to a server on the same machine can be used; see the README file for the CLI sample programs (in the sqllib/samples/cli subdirectory) for instructions on setting up this type of connection.

• The stored procedure indicates that a result set is to be returned by declaring a cursor on the result set, opening a cursor on the result set (i.e. executing the query), and leaving the cursor open when exiting the stored procedure.

• For every cursor that is left open, a result set is returned to the application.

• If more than one cursor is left open, the result sets are returned in the order in which their cursors were opened in the stored procedure.

• If the stored procedure commits the current transaction then all cursors not declared using the WITH HOLD clause will be closed.

• If the stored procedure rolls back the current transaction then all cursors will be closed.

• If the stored procedure calls SQLFreeStmt() with either SQL_DROP or SQL_CLOSE, then the cursor for the current result set is closed and the rows are flushed. Note that this is also the case for all other cursors associated with other result sets generated by this same stored procedure call.

• Only unread rows are passed back. For example, if the result set of a cursor has 500 rows, and 150 of those rows have already been read by the stored procedure at the time the stored procedure terminates, then rows 151 through 500 will be returned to the stored procedure. This can be useful if the stored procedure wishes to filter out some initial rows and not return them to the application.

How Returning a Result Set Differs from Executing a Query Statement

In general, calling a stored procedure that returns a result set is equivalent to executing a query statement. The following restrictions apply:

• Column names are not returned by either SQLDescribeCol() or SQLColAttribute() for static query statements. In this case, the ordinal position of the column is returned instead.

• The length value of LOB data types is always set to the maximum length, or the value of the LOBMAXCOLUMNSIZE keyword if it is specified.

• All result sets are read-only.

• The cursor cannot be used as a scrollable cursor.

• Schema functions (such as SQLTables()) cannot be used to return a result set. If schema functions are used within a stored procedure, all of the cursors for the associated statement handles must be closed before returning, otherwise extraneous result sets may be returned.

• When a query is prepared, result set column information is available before the execute. When a stored procedure is prepared, the result set column information is not available until the CALL statement is executed.

[ DB2 List of Books | Search the DB2 Books ]