1.9.3 Results-set return facility

The use of the results-set return facility allows you to reference, at the source of the call, the results of a search using a cursor in a procedure.

This section explains how to return a results set in a procedure, and how to receive, into a UAP, the results set that is returned by the procedure.

Organization of this subsection
(1) In an SQL procedure definition
(2) In an external Java procedure definition
(3) By creating a Java method that is an external Java procedure entity
(4) By creating an embedded type UAP
(5) By creating a UAP using Java
(6) Notes

(1) In an SQL procedure definition

In the DYNAMIC RESULT SETS clause of CREATE PROCEDURE, specify the maximum number of result sets (the maximum number of cursors to be returned to the source of the call). In addition, in the cursor declaration for the cursors that are returned as a results set from a procedure, specify WITH RETURN.

Closing the procedure with the cursors that were declared open by specifying WITH RETURN allows you to return the cursor results set to the source of the call. If there are two or more result sets to be returned, the system returns them in the order in which the cursors were opened.

An example of an SQL procedure definition is given below:

CREATE PROCEDURE ORDERED_EMPS(IN REGION INTEGER)
   DYNAMIC RESULT SETS 2
   BEGIN
       DECLARE CUR1 CURSOR WITH RETURN
           FOR SELECT id_no, name FROM emps_1
               WHERE id_no < REGION ORDER BY id_no;
       DECLARE CUR2 CURSOR WITH RETURN
           FOR SELECT id_no, name FROM emps_2
               WHERE id_no < REGION ORDER BY id_no;
       OPEN CUR1;
       OPEN CUR2;
   END;

(2) In an external Java procedure definition

In the DYNAMIC RESULT SETS clause of CREATE PROCEDURE, specify the maximum number of result sets (the maximum number of cursors to be returned to the source of the call).

The following shows an example of an external Java procedure definition:

CREATE PROCEDURE ORDERED_EMPS(IN REGION INTEGER)
   DYNAMIC RESULT SETS 2 LANGUAGE JAVA
   EXTERNAL NAME
     'jfile.jar:Routines3.orderedEmps
      (int,java.sql.ResultSet[],java.sql.ResultSet[])
      returns void'
   PARAMETER STYLE JAVA;

(3) By creating a Java method that is an external Java procedure entity

For the last argument of the Java method that is an external Java procedure entity, specify a java.sql.ResultSet[] type parameter. The Java method executes the SQL statement, receives result sets, and sets a variable that is a parameter of the java.sql.ResultSet[] type.

An example of how to create a Java method is given below:

import java.sql.*;

public class Routines3 {
   public static void orderedEmps(int region,
       java.sql.ResultSet[] rs1, java.sql.ResultSet[] rs2)
   throws SQLException {
       java.sql.Connection conn=DriverManager.getConnection(
           "jdbc:hitachi:PrdbDrive","USER1","USER1");
       java.sql.PreparedStatement stmt1=
         conn.prepareStatement(
             "SELECT id_no,name FROM emps_1 WHERE id_no < ? ORDER BY id_no");
       stmt1.setInt(1, region);
       rs1[0]=stmt1.executeQuery();
       java.sql.PreparedStatement stmt2=
         conn.prepareStatement(
             "SELECT id_no,name FROM emps_2 WHERE id_no < ? ORDER BY id_no");
       stmt2.setInt(1, region);
       rs2[0]=stmt2.executeQuery();
       return;
  }
}

(4) By creating an embedded type UAP

When an embedded type UAP is created, a procedure is executed from the embedded type UAP, and a group of the result sets returned by the ALLOCATE CURSOR statement is assigned to a cursor. The cursor is associated with the first result set, and data can be fetched from the result set using the FETCH statement.

The second and subsequent result sets are allocated to the group of result sets. Executing the CLOSE statement on the cursor associated with the previous result set allows you to associate the new result set with the cursor and fetch data from the new result set using the FETCH statement.

An example of how to create an embedded type UAP in the C language is given below:

EXEC SQL WHENEVER SQLERROR GOTO error_end;
EXEC SQL CALL ORDERED_EMP(1000);
if (SQLCODE==120) {    /* A group of result sets was returned */
   EXEC SQL ALLOCATE GLOBAL :cur1 FOR PROCEDURE ORDERED_EMP;
                   /* A cursor is allocated */
                   /* Specify a cursor name in cur1 */
   while (1) {
       while (1) {
           EXEC SQL WHENEVER NOT FOUND DO break;
           EXEC SQL FETCH GLOBAL :cur1 INTO :emp_id, :emp_name;
           printf("ID No.=%s\n", emp_id);
           printf("Name=%s\n", emp_name);
      }
       EXEC SQL WHENEVER NOT FOUND DO break;
       EXEC SQL CLOSE GLOBAL :cur1;
  }
}
error_end:

(5) By creating a UAP using Java

A procedure is executed from a UAP coded in Java, and the result sets sent from the procedure are received.

An example of how to create a UAP using Java is given below, in which the UAP receives result sets by using java.sql.PreparedStatement.execute().

java.sql.CallableStatement stmt=conn.preparecall(
   "{call ordered_emps(?)}");
stmt.setInt(1,3);
stmt.execute();
java.sql.ResultSet rs=stmt.getResultSet();
                              //Receives result sets
while(rs.next()) {
   int id_no=rs.getInt(1);
   java.lang.String name=rs.getString(2);
   System.out.println("ID No.="+id_no);
   System.out.println("Name="+name);
   System.out.println();
}
rs.close();
while (stmt.getMoreResults()){
   rs = stmt.getResultSet();
   while(rs.next()) {
       int id_no=rs.getInt(1);
       java.lang.String name=rs.getString(2);
       System.out.println("ID No.="+id_no);
       System.out.println("Name="+name);
       System.out.println();
  }
   rs.close();
}

(6) Notes

The result set (ResultSet) that the method returns, which is in the class DatabaseMetaData in the external Java procedure, cannot be returned as a dynamic result set. To obtain the information, use the connection metadata from the call to the external Java procedure.