Writing a Java stored procedure to return result sets

You can write your Java stored procedures to return multiple query result sets to a client program.

Before you begin

A stored procedure can return multiple query result sets to a client program if the following conditions are satisfied:

The client supports the DRDA code points that are used to return query result sets.
The value of DYNAMIC RESULT SETS in the stored procedure definition is greater than 0.

Procedure

For each result set that you want to be returned, your Java stored procedure must perform the following actions:

For each result set, include an object of type java.sql.ResultSet[] or an array of an SQLJ iterator class in the parameter list for the stored procedure method.
If the stored procedure definition includes a method signature, for each result set, include java.sql.ResultSet[] or the fully-qualified name of an array of a class that is declared as an SQLJ iterator in the method signature. These result set parameters must be the last parameters in the parameter list or method signature. Do not include a java.sql.ResultSet array or an iterator array in the SQL parameter list of the stored procedure definition.
Execute a SELECT statement to obtain the contents of the result set.
Retrieve any rows that you do not want to return to the client.
Assign the contents of the result set to element 0 of the java.sql.ResultSet[] object or array of an SQLJ iterator class that you declared in step 1.
Do not close the ResultSet, the statement that generated the ResultSet, or the connection that is associated with the statement that generated the ResultSet.
Db2 does not return result sets for ResultSets that are closed before the stored procedure terminates.

Example

The following code shows an example of a Java stored procedure that uses an SQLJ iterator to retrieve a result set.

Figure 1. Java stored procedure that returns a result set

package s1;

import sqlj.runtime.*;
import java.sql.*;
import java.math.*;
#sql iterator NameSal(String LastName, BigDecimal Salary);                 1 
public class S1Sal
{
  public static void getSals(BigDecimal[] AvgSalParm, 
                             java.sql.ResultSet[] rs)                      2 
    throws SQLException 
  {
    NameSal iter1;
    try
    {
      #sql iter1 = {SELECT LASTNAME, SALARY FROM EMP                       3 
                      WHERE SALARY>0 ORDER BY SALARY DESC};
      #sql {SELECT AVG(SALARY) INTO :(AvgSalParm[0]) FROM EMP};            4 
    }
    catch (SQLException e)
    {  
      System.out.println("SQLCODE returned: " + e.getErrorCode());
      throw(e);
    }
    rs[0] = iter1.getResultSet();                                          5 
  }
}

Notes to Figure 1:

Note	Explanation
1	This SQLJ clause declares the iterator named NameSal, which is used to retrieve the rows that will be returned to the stored procedure caller in a result set.
2	The declaration for the stored procedure method contains declarations for a single passed parameter, followed by the declaration for the result set object.
3	This SQLJ clause executes the SELECT to obtain the rows for the result set, constructs an iterator object that contains those rows, and assigns the iterator object to variable iter1.
4	This SQLJ clause retrieves a value into the parameter that is returned to the stored procedure caller.
5	This statement uses the `getResultSet` method to assign the contents of the iterator to the result set that is returned to the caller.