Application Development Guide

Returning Result Sets from Stored Procedures

You can code stored procedures to return one or more result sets to DB2 CLI, ODBC, JDBC, or SQLJ client applications. Aspects of this support include:

• Only DB2 CLI, ODBC, JDBC, and SQLJ clients can accept multiple result sets.

• DB2 clients that use embedded SQL can accept multiple result sets if the stored procedure resides on a server that is accessible from a DataJoiner Version 2 server. Stored procedures on host and AS/400 platforms can return multiple result sets to DB2 Connect clients. Stored procedures on DB2 Universal Database servers can return multiple result sets to host and AS/400 clients. Consult the product documentation for DataJoiner or the host or AS/400 platform for more information.

• 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 for DB2 CLI, getMoreResults for JDBC, getNextResultSet for SQLJ) 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

• Only unread or unfetched rows are passed back in the result set.

• Stored procedures which return result sets must be run in FENCED mode.

• A COMMIT or ROLLBACK will close all cursors except WITH HOLD cursors.

• 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 on handling multiple result sets:

• in DB2 CLI, refer to the CLI Guide and Reference

• in Java, refer to the DB2 Java Enablement web page for links to the JDBC and SQLJ specifications

The following diagram 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 three result sets 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.

[ DB2 List of Books | Search the DB2 Books ]