Retrieving data from cursor output parameters in JDBC applications

Db2® on Linux®, UNIX, and Windows systems stored procedures can have OUT parameters of the cursor type. To retrieve data from those parameters in JDBC applications, you use ResultSet objects.

Procedure

To retrieve data from cursor variables, follow these steps:

  1. Define a ResultSet object for each OUT parameter that has the cursor data type.
  2. Invoke the Connection.prepareCall method with the CALL statement as its argument to create a CallableStatement object.
  3. Invoke the CallableStatement.registerOutParameter method to register the data types of parameters that are defined as OUT in the CREATE PROCEDURE statement.

    The data type for cursor type output parameters is com.ibm.db2.jcc.DB2Types.CURSOR.

  4. Call the stored procedure.
  5. Invoke the CallableStatement.getObject method to retrieve the ResultSet for each OUT cursor parameter.

    You can call only CallableStatement.getObject or CallableStatement.getString on a cursor parameter. Calling CallableStatement.getString returns a name that is associated with the result set that is returned for the parameter.

    If more than one OUT cursor parameter references the same cursor at the data source, the same ResultSet instance is returned for all parameters.

  6. Retrieve rows from the ResultSet object for each OUT cursor parameter.
  7. Close the ResultSet.

    If the autocommit value is true, a commit operation occurs only when all of the result sets that are returned by cursor type output parameters or by the stored procedure are closed.

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 
     DECLARE c1 CURSOR WITH RETURN FOR 
       SELECT  empno FROM EMPLOYEE; 
     OPEN c1;
     SET pcv1 = CURSOR FOR SELECT name FROM employee WHERE empNo = pempNo; 
     OPEN pcv1; 
  END
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.
Connection con;
ResultSet rs = null;                 // Output parameter                1 
…
CallableStatement cstmt = conn.prepareCall("CALL MYPROC(?, ?)");        2 
String hvEmpNo="000500";
cstmt.setString (1, hvEmpNo);
cstmt.registerOutParameter (2, DB2Types.CURSOR);                        3 
cstmt.executeUpdate();               // Call the stored procedure       4 
String hvEmpName = null;
rs = (java.sql.ResultSet)cstmt.getObject(2);                            5 
while (rs.next()) {                  // Retrieve result set rows        6 
  hvEmpName=rs.getString(1);
  System.out.println("Employee name for " + hvEmpNo 
    + ": " + hvEmpName);
}
rs.close();                          // Close the ResultSet             7