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.
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;
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).
An example of a Java procedure definition is given below:
CREATE PROCEDURE ORDERD_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;
In the last argument in a Java method, which is the entity for a Java procedure, specify a parameter of the java.sql.ResultSet[] type. 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:
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;
}
}
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:
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.ResoltSet rs=smmt.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();
}
Result sets (ResultSet) returned by a method in the DatabaseMetaData acquired in a Java procedure cannot be returned as dynamic result sets. In this case, data should be acquired by using the metadata on the connection for the source of the call.