Receiving procedure result sets in JDBC applications and routines

You can receive result sets from procedures you invoke from a JDBC routine or application.

To accept procedure result sets from within a JDBC routine or application:

1. Open a database connection (using a Connection object):

     Connection con =
         DriverManager.getConnection("jdbc:db2:sample", userid, passwd);

2. Prepare the CALL statement that will invoke a procedure that returns result sets (using a CallableStatement object). In the following example, a procedure named GET_HIGH_SALARIES is invoked. The prepare is followed by the assignment of an input variable (called inSalaryThreshold -- a numeric value to be passed to the procedure) to the value of the parameter marker in the previous statement. A parameter marker is indicated with a "?" or by a colon followed by a name (:name).

     String query = "CALL GET_HIGH_SALARIES(?)";
   
     CallableStatement stmt = con.prepareCall(query);
     stmt.setDouble(1, inSalaryThreshold);

3. Call the procedure:

     stmt.execute();

4. Use the CallableStatement object's getResultSet() method to accept the first result set from the procedure and fetch the rows from the result sets using the fetchAll() method:

     ResultSet rs = stmt.getResultSet();
   
     // Result set rows are fetched and printed to screen.
     while (rs.next())
     {
       r++;
       System.out.print("Row: " + r + ": ");
       for (int i=1; i <= numOfColumns; i++)
       {
         System.out.print(rs.getString(i));
         if (i != numOfColumns)
         {
           System.out.print(", ");
         }
       }
       System.out.println();
     }

5. For multiple result sets, use the CallableStatement object's getNextResultSet() method to enable the following result set to be read. Then repeat the process in the previous step, where the ResultSet object accepts the current result set, and fetches the result set rows. For example:

     while (callStmt.getMoreResults())
     {
       rs = callStmt.getResultSet()
   
       ResultSetMetaData stmtInfo = rs.getMetaData();
       int numOfColumns = stmtInfo.getColumnCount();
       int r = 0;
   
       // Result set rows are fetched and printed to screen.
       while (rs.next())
       {
         r++;
         System.out.print("Row: " + r + ": ");
         for (int i=1; i <= numOfColumns; i++)
         {
           System.out.print(rs.getString(i));
           if (i != numOfColumns)
           {
             System.out.print(", ");
           }
         }
         System.out.println();
       }
     }

6. Close the ResultSet object with its close() method:

     rs.close();