Retrieving multiple result sets from a stored procedure in an SQLJ application

Some stored procedures return one or more result sets to the calling program by including the DYNAMIC RESULT SETS n clause in the definition, with n>0, and opening cursors that are defined with the WITH RETURN clause. The calling program needs to retrieve the contents of those result sets.

To retrieve the rows from those result sets, you execute these steps:
  1. Acquire an execution context for retrieving the result set from the stored procedure.
  2. Associate the execution context with the CALL statement for the stored procedure.

    Do not use this execution context for any other purpose until you have retrieved and processed the last result set.

  3. For each result set:
    1. Use the ExecutionContext method getNextResultSet to retrieve the result set.
    2. If you do not know the contents of the result set, use ResultSetMetaData methods to retrieve this information.
    3. Use an SQLJ result set iterator or JDBC ResultSet to retrieve the rows from the result set.

Result sets are returned to the calling program in the same order that their cursors are opened in the stored procedure. When there are no more result sets to retrieve, getNextResultSet returns a null value.

getNextResultSet has two forms: 
getNextResultSet();
getNextResultSet(int current);
When you invoke the first form of getNextResultSet, SQLJ closes the currently-open result set and advances to the next result set. When you invoke the second form of getNextResultSet, the value of current indicates what SQLJ does with the currently-open result set before it advances to the next result set:
java.sql.Statement.CLOSE_CURRENT_RESULT
Specifies that the current ResultSet object is closed when the next ResultSet object is returned.
java.sql.Statement.KEEP_CURRENT_RESULT
Specifies that the current ResultSet object stays open when the next ResultSet object is returned.
java.sql.Statement.CLOSE_ALL_RESULTS
Specifies that all open ResultSet objects are closed when the next ResultSet object is returned.

The following code calls a stored procedure that returns multiple result sets. For this example, it is assumed that the caller does not know the number of result sets to be returned or the contents of those result sets. It is also assumed that autoCommit is false. The numbers to the right of selected statements correspond to the previously-described steps.

Figure 1. Retrieving result sets from a stored procedure
ExecutionContext execCtx=myConnCtx.getExecutionContext();      1 
#sql [myConnCtx, execCtx] {CALL MULTRSSP()};                   2  
            // MULTRSSP returns multiple result sets
ResultSet rs;                                                 
while ((rs = execCtx.getNextResultSet()) != null)              3a 
{
  ResultSetMetaData rsmeta=rs.getMetaData();                   3b 
  int numcols=rsmeta.getColumnCount();
  while (rs.next())                                            3c 
  {
    for (int i=1; i<=numcols; i++)
    {
      String colval=rs.getString(i);
      System.out.println("Column " + i + "value is " + colval); 
    }
  }
}