Retrieving an unknown number of result sets from a stored procedure in a JDBC application

Retrieving an unknown number of result sets from a stored procedure is a more complicated procedure than retrieving a known number of result sets.

About this task

To retrieve result sets when you do not know the number of result sets or their contents, you need to retrieve ResultSets, until no more ResultSets are returned. For each ResultSet, use ResultSetMetaData methods to determine its contents.

After you call a stored procedure, follow these basic steps to retrieve the contents of an unknown number of result sets.

Procedure

  1. Check the value that was returned from the execute statement that called the stored procedure.

    If the returned value is true, there is at least one result set, so you need to go to the next step.

  2. Repeat the following steps in a loop:
    1. Invoke the getResultSet method to obtain a result set, which is in a ResultSet object. Invoking this method closes the previous result set.
    2. Use ResultSetMetaData methods to determine the contents of the ResultSet, and retrieve data from the ResultSet.
    3. Invoke the getMoreResults method to determine whether there is another result set. If getMoreResults returns true, go to step 1 to get the next result set.

Example

The following code illustrates retrieving result sets when you do not know the number of result sets or their contents. The numbers to the right of selected statements correspond to the previously described steps.
CallableStatement cstmt;
ResultSet rs;
…
boolean resultsAvailable = cstmt.execute(); // Call the stored procedure
while (resultsAvailable) {                  // Test for result sets       1 
 ResultSet rs = cstmt.getResultSet();       // Get a result set           2a 
 …                                          // Process the ResultSet
                                            // as you would process
                                            // a ResultSet from a table
 resultsAvailable = cstmt.getMoreResults(); // Check for next result set  2c 
                                            // (Also closes the 
                                            // previous result set)
}