Nonstop Database, HiRDB Version 9 UAP Development Guide
You can use the results-set return facility by specifying 1 or a greater value in the DYNAMIC RESULT SETS clause in CREATE PROCEDURE during Java stored procedure definition. The results-set return facility is not supported for Java stored functions.
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:
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.
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 |
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 } } |
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 } } } |
Specify the retrieval result (ResultSet) in the OUT parameter of the ResultSet[] type without closing it.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.