Keeping result sets open when retrieving multiple result sets from a stored procedure in a JDBC application

The getMoreResults method has a form that lets you leave the current ResultSet open when you open the next ResultSet.

Procedure

To specify whether result sets stay open, follow this process:

When you call getMoreResults to check for the next ResultSet, use this form:

CallableStatement.getMoreResults(int current);

To keep the current ResultSet open when you check for the next ResultSet, specify a value of Statement.KEEP_CURRENT_RESULT for current.
To close the current ResultSet when you check for the next ResultSet, specify a value of Statement.CLOSE_CURRENT_RESULT for current.
To close all ResultSet objects, specify a value of Statement.CLOSE_ALL_RESULTS for current.

Example

The following code keeps all ResultSets open until the final ResultSet has been retrieved, and then closes all ResultSets.

CallableStatement cstmt;
…
boolean resultsAvailable = cstmt.execute(); // Call the stored procedure
if (resultsAvailable==true) {               // Test for result set
 ResultSet rs1 = cstmt.getResultSet();      // Get a result set
 …
 resultsAvailable = cstmt.getMoreResults(Statement.KEEP_CURRENT_RESULT); 
                                            // Check for next result set
                                            // but do not close
                                            // previous result set
 if (resultsAvailable==true) {              // Test for another result set
  ResultSet rs2 = cstmt.getResultSet();     // Get next result set
  …                                         // Process either ResultSet 
 }
} 
resultsAvailable = cstmt.getMoreResults(Statement.CLOSE_ALL_RESULTS);
                                            // Close the result sets