The results-set return facility enables the Java stored procedure caller to reference the cursor that is acquired by the execution of the SELECT statement within the Java stored procedure.

The following figure provides an overview of the results-set return facility.

Figure 9-11 Overview of the results-set return facility (for a Java stored procedure)

The calling-source languages that support the results-set return facility are as follows:

• Java
• C
• C++
• COBOL^#
• OOCOBOL

#: COBOL can be used if the RDB file input/output facility is not used.

This example obtains columns rank, name, and age, which satisfy condition rank<10 in tables emps_1 and emps_2 within a Java stored procedure. The caller receives two result sets to manipulate them.

• Defining the Java stored procedure
  

  CREATE PROCEDURE proc2(IN param1 INTEGER) ...........1 DYNAMIC RESULT SETS 2 .............................2 LANGUAGE JAVA .....................................3 EXTERNAL NAME .....................................4 'mypack.jar:JStrSmp1.getEmp2(int, ResultSet[] , ResultSet[])' ..4 PARAMETER STYLE JAVA; .............................5

Explanation

1. Defines the procedure name and parameters.
2. Specifies the number of retrieval result sets to be returned.
3. Specifies LANGUAGE.
4. Associates with the Java method.
5. Specifies PARAMETER STYLE.

• Java stored procedure
  

  import java.sql.*; ..................................1 public class JStrSmp1{ ..............................2 public static void getEmp2 ........................3 (int jparam1, ResultSet[] rs1_out , ResultSet[] rs2_out) ..............4 throws SQLException { .....................4 Connection con = DriverManager.getConnection ( ...5 "jdbc:hitachi:hirdb"); .........5 con.setAutoCommit(false); .......................6 PreparedStatement pstmt1 = con.prepareStatement ..7 ("select rank,name,age from emps_1 where rank < ? ......................7 order by rank"); ...........................7 pstmt1.setInt(1, jparam1); ......................7 ResultSet rs1 = pstmt1.executeQuery(); ..........8 rs1_out[0] = rs1; ...............................9 PreparedStatement pstmt2 = con.prepareStatement ..10 ("select rank,name,age from emps_2 where rank < ? ......................10 order by rank"); ...........................10 pstmt2.setInt(1, jparam1); ......................10 ResultSet rs2 = pstmt2.executeQuery(); ....... ..11 rs2_out[0] = rs2; ........................... ...12 return; .................................. ......13 } }

Explanation

1. Imports the java.sql package.
2. Defines the class name.
3. Defines the method name.
4. Defines the parameter name (the second and third arguments are for returning result sets).
5. Obtains the Connection object.
6. Suppresses automatic commit processing.
7. Preprocesses the SELECT statement.
8. Executes the SELECT statement.
9. Sets the obtained result set rs1 in the second argument of the ResultSet[] type.
10. Preprocesses the SELECT statement.
11. Executes the SELECT statement.
12. Sets the obtained result set rs1 in the third argument of the ResultSet[] type.
13. Terminates the call and returns the result sets.

• Executing the Java stored procedure (caller)
  

  import java.sql.*; ......................................................1 public class Caller{ ....................................................2 public static void main(String[] args) throws SQLException { .........................3 Connection con = DriverManager.getConnection( .......................4 "jdbc:hitachi:hirdb","USER1","PASS1"); .............4 ,"PASS1"); .........................................4 CallableStatement cstmt = con.prepareCall ("{call proc2(?)}"); ...............................5 cstmt.setInt(1, 10); ................................................5 ResultSet rs; .......................................................6 int emp_rank; .......................................................6 String emp_name; ....................................................6 int emp_age; ........................................................6 if(cstmt.execute()){ ................................................7 rs = cstmt.getResultSet(); .......................................8 System.out.println("*** emps_1 ***"); ............................9 while(rs.next()){ ................................................9 emp_rank = rs.getInt(1); .......................................9 emp_name = rs.getString(2); ....................................9 emp_age = rs.getInt(3); ........................................9 System.out.println("RANK =" + emp_rank + .......................9 " NAME = " + emp_name + " AGE = " + emp_age); ...........................9 } } if(cstmt.getMoreResults()){ .........................................10 rs= cstmt.getResultSet(); ........................................11 System.out.println("*** emps_2 ***"); ............................12 while(rs.next()){ ................................................12 emp_rank = rs.getInt(1); .......................................12 emp_name = rs.getString(2); ....................................12 emp_age = rs.getInt(3); ........................................12 System.out.println("RANK =" + emp_rank + .......................12 " NAME = " + emp_name + " AGE = " + emp_age); ................................................12 } rs.close(); ......................................................13 } } }

Explanation

1. Imports the java.sql package.
2. Defines the class name.
3. Defines the method name.
4. Obtains the Connection object.
5. Preprocesses the CALL statement.
6. Declares variables.
7. Executes the CALL statement.
8. Obtains the result set.
9. Outputs information obtained from the first result set.
10. Checks to see if there are any more result sets.
11. Obtains the next result set.
12. Outputs information obtained from the second result set.
13. Closes the result sets.

(a) When defining a Java stored procedure with CREATE PROCEDURE

1. In the DYNAMIC RESULT SETS clause, specify the maximum number of result sets to be returned from within the Java stored procedure. If a value of 0 is specified, the system does not use the results-set return facility.
2. For a parameter in CREATE PROCEDURE, do not specify the OUT parameter of ResultSet[] type that is specified for a parameter in the Java stored procedure.
3. When using EXTERNAL NAME to define correspondence with a Java program, include arguments of the ResultSet[] type.

(b) When creating a caller's method

1. In the CALL statement's parameters, do not include a parameter of the ResultSet[] type in the method for Java stored procedure.
2. If there is more than one result set to be returned, to receive the second or subsequent result set, use the getMoreResult method (to determine whether there are more result sets) and the getResultSet method (to receive the next result).

(c) When creating a method for a Java stored procedure

Specify the retrieval result (ResultSet) in the OUT parameter of the ResultSet[] type without closing it.