Returning result sets from JDBC procedures

You can develop JDBC procedures that return result sets to the invoking routine or application. In JDBC procedures, the returning of result sets is handled with ResultSet objects.

To return a result set from a JDBC procedure:

1. For each result set that is to be returned, include a parameter of type ResultSet[] in the procedure declaration. For example, the following function signature accepts an array of ResultSet objects:

     public static void getHighSalaries(
       double inSalaryThreshold,         // double input
       int[] errorCode,                  // SQLCODE output
       ResultSet[] rs)                   // ResultSet output

2. Open the invoker's database connection (using a Connection object):

     Connection con =
         DriverManager.getConnection("jdbc:default:connection");

3. Prepare the SQL statement that will generate the result set (using a PreparedStatement object). In the following example, the prepare is followed by the assignment of an input variable (called inSalaryThreshold - refer to the previously shown function signature example) to the value of the parameter marker in the query statement. A parameter marker is indicated with a "?" or a colon, followed by a name (:name).

     String query =
         "SELECT name, job, CAST(salary AS DOUBLE) FROM staff " +
         "  WHERE salary > ? " +
         "  ORDER BY salary";
   
     PreparedStatement stmt = con.prepareStatement(query);
     stmt.setDouble(1, inSalaryThreshold);

4. Execute the statement:

     rs[0] = stmt.executeQuery();

5. End the procedure body.

What to do next

If you have not done so already, develop a client application or caller routine that will accept result sets from your stored procedure.