DB2 10.5 for Linux, UNIX, and Windows

Retrieving data from cursor output parameters in SQLJ applications

DB2® for Linux, UNIX, and Windows stored procedures can have OUT parameters of the cursor type. To retrieve data from those parameters in SQLJ applications, you use iterators or ResultSet objects.

Procedure

To retrieve data from cursor variables, follow these steps:

Define an iterator or ResultSet object for each OUT parameter that has the CURSOR data type in the stored procedure definition.
Iterators for retrieving cursor OUT parameters can be named or positioned.
Assign values to input parameters.
Call the stored procedure.
Retrieve rows from the cursor parameters.
- If you declare a positioned iterator for the cursor parameter, use FETCH statements to retrieve the data.
- If you declare a named iterator for the cursor parameter, use NamedIterator methods to retrieve the data.
- If you define a ResultSet object for the cursor parameter, use ResultSet methods to position the cursor and retrieve values from result set rows.
If the stored procedure returns multiple result sets by opening cursors that are defined as WITH RETURN, retrieve those result sets.
A single stored procedure can return data through multiple result sets as well as CURSOR parameters.

Example

A cursor data type and a stored procedure have the following definitions:

CREATE TYPE myRowType AS ROW (name VARCHAR(128))
CREATE TYPE myCursorType AS myRowType CURSOR
CREATE PROCEDURE MYPROC(IN pempNo VARCHAR(6), OUT pcv1 myCursorType) 
  RESULT SETS 0
  LANGUAGE SQL 
  BEGIN 
     SET pcv1 = CURSOR FOR SELECT name FROM employee WHERE empNo = pempNo; 
     OPEN pcv1; 
  END

The following code calls stored procedure MYPROC and uses a positioned iterator to retrieve data from cursor pcv1. The numbers to the right of selected statements correspond to the previously-described steps.

#sql iterator Iter (String);         // Declare a positioned iterator
…
Iter iter = null;                    // Output parameter                1 
String hvPempNo="000500";            // Input parameter                 2 
#sql [ctx] {CALL MYPROC (:IN hvPempNo, :OUT iter)};                     3 
                                     // Call the stored procedure
String hvEmpName = null;
while (true) {                       // Retrieve rows from the result set
  #sql { FETCH :iter into :hvName };                                    4 
  if (iter.endFetch()) break;
  System.out.println("Employee name for " + hvPempNo 
    + ": " + hvEmpName);
}

The following code calls stored procedure MYPROC and uses a ResultSet object to retrieve data from cursor pcv1. The numbers to the right of selected statements correspond to the previously-described steps.

…
ResultSet rs = null;                 // Output parameter                1 
String hvPempNo="000500";            // Input parameter                 2 
#sql [ctx] {CALL MYPROC (:IN hvPempNo, :OUT rs)};                       3 
                                     // Call the stored procedure
String hvEmpName = null;
while (rs.next()) {                  // Retrieve result set rows        4 
  hvEmpName=rs.getString(1);
  System.out.println("Employee name for " + hvPempNo 
    + ": " + hvEmpName);
}