Receiving Multiple Result Sets in Java

Receiving multiple result sets from a procedure in Java is an easy process as long as the outputs from a stored procedure are clearly documented. The following sections detail how multiple result sets can be receive in both a Java Application and in a Java Stored Procedure.

Receiving Multiple Result Sets in a Java Application

Receiving multiple result sets is similar to receiving a single result set discussed in the previous section. The question you would want to ask yourself when you write your client code is whether you want to process the result sets in parallel.

Processing result sets in sequence means you access each result set one at a time. The current result set will be closed before the next result set is opened. Processing result sets in parallel means you can have more than one result set open at the same time.

If the calling application is an SQL procedure, you have the option of processing the result sets in sequence or in parallel. This is covered in Chapter 8, "Nested SQL Procedures." Similarly, if the calling application is a DB2 CLI program, you will have the same options. If the calling application is a Java program, however, the result sets must be processed in sequence.

In JDBC, you can move to the next result set by calling the getMoreResults() method of the Statement interface. According to the JDBC standard, this method implicitly closes any existing ResultSet objects obtained with the method getresultSet().

Figure H.5 shows the complete Java client code for receiving and using all three returned result sets. The program simply prints all data received from each cursor, in sequence, without saving retrieved data in local variables. The program can only process a result set after the previous result set has been processed.

Figure H.5. PrintSalary.JavaAn example of receiving multiple result sets from a Java application.

 import java.lang.*; import java.util.*; import java.io.*; import java.sql.*; import COM.ibm.db2.jdbc.app.*; class PrintSalary {     static     {         try         {             Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();         }         catch (Exception e)         {             System.out.println ("\n Error loading DB2 Driver...\n");             System.out.println (e);             System.exit(1);         }     }     public static void main (String argv[])     {         Connection con = null;         try         {             double v_salary, v_bonus, v_comm;             // Connect to Sample database             String url = "jdbc:db2:sample";             con = DriverManager.getConnection(url);             CallableStatement cs = con.prepareCall("CALL db2admin.read_emp_multi()");             cs.execute();             ResultSet rs1 = cs.getResultSet();             while ( rs1.next() )                                      /*--(1)*/             {                 v_salary = rs1.getDouble(1);                 System.out.println(v_salary);             }                                                         /*--(2)*/             cs.getMoreResults();                                      /*--(3)*/             ResultSet rs2 = cs.getResultSet();             while ( rs2.next() )             {                 v_bonus = rs2.getDouble(1);                 System.out.println(v_bonus);              }              cs.getMoreResults();              ResultSet rs3 = cs.getResultSet();              while ( rs3.next() )              {                  v_comm = rs3.getDouble(1);                  System.out.println(v_comm);              }              rs1.close();              rs2.close();              rs3.close();              cs.close ();              con.close ();              System.out.println("Complete(0).");         }         catch (Exception e)         {             try             {                 if( con != null )                 {                 con.close();                 }             }             catch (Exception x)             { //ignore this exception             }             System.out.println (e);         }     } } 

Even though three different ResultSet object variables were declared and used for each result set, you still cannot process all result sets at the same time. If you moved the block of code processing the first result set between Lines (1) and (2) to after the invocation of the getMoreResults() method on Line (3), you would not be able to access the first result set anymore. If you wanted to work with the first and the second result set at the same time, you will have to declare an array to hold the first result set.

Receiving Multiple Result Sets in a Java Stored Procedure

As mentioned earlier, Java cannot process multiple result sets from the same procedure in parallel. If you wanted to rewrite the receive_multi procedure shown in Figure 8.18 in Java, you would not be able to because it processes two results sets in parallel from the emp_multi procedure. To receive equivalent functionality, you would need to split the emp_multi procedure into two procedures that return result sets. In this case, the two result sets can be processed in parallel because they are not returned from the same procedure. Figure H.6 shows the emp_multi procedure broken out into two procedures emp_mult1 and emp_multi2.

Figure H.6. Procedure emp_multi() separated into two procedures.

 CREATE PROCEDURE emp_multi1 ( IN p_dept CHAR(3) )    LANGUAGE SQL    SPECIFIC emp_multi1                            -- applies to LUW and iSeries -- WLM ENVIRONMENT <env>                          -- applies to zSeries    DYNAMIC RESULT SETS 1 em1: BEGIN     -- Procedure logic     -- Selects firstname     DECLARE v_cur1 CURSOR WITH RETURN TO CALLER         FOR SELECT firstnme             FROM employee             WHERE workdept = p_dept;         OPEN v_cur1; END em1 CREATE PROCEDURE emp_multi2 ( IN p_dept CHAR(3) )    LANGUAGE SQL    SPECIFIC emp_multi2                            -- applies to LUW and iSeries -- WLM ENVIRONMENT <env>                          -- applies to zSeries    DYNAMIC RESULT SETS 2 em2: BEGIN     -- Procedure logic     -- Selects lastname     DECLARE v_cur2 CURSOR WITH RETURN TO CALLER         FOR SELECT lastname             FROM employee             WHERE workdept = p_dept;     -- Selects salary     DECLARE v_cur3 CURSOR WITH RETURN TO CALLER         FOR SELECT salary             FROM employee             WHERE workdept = p_dept;     OPEN v_cur2;     OPEN v_cur3; END em2 

With the two procedures emp_multi1 and emp_multi2, you can now rewrite the procedure receive_multi as a Java stored procedure, as shown in Figure H.7.

Figure H.7. Rewrite of receive_multi() in Java.

 /** * JDBC Stored Procedure DB2ADMIN.ReceiveMulti * @param p_dept * @param p_names * @param p_total */ package ReceiveMulti; import java.sql.*;                   // JDBC classes public class ReceiveMulti {     public static void receiveMulti ( String p_dept,                                       String[] p_names,                                       java.math.BigDecimal[] p_total ) throws SQLException, Exception     {        // Get connection to the database        Connection con = DriverManager.getConnection("jdbc:default:connection");        CallableStatement cStmt1 = null;        CallableStatement cStmt2 = null;        ResultSet rs1 = null;        ResultSet rs2 = null;        String sql;        // parameters from procedures        String v_fname = "";        String v_lname = "";        java.math.BigDecimal v_salary = new java.math.BigDecimal("0.00");        // Set up the dynamic call to the first procedure        // Setup input parameters        // Register output parameters        // Call the procedures        sql = "CALL emp_multi1(?)";        cStmt1 = con.prepareCall(sql);        cStmt1.setString(1, p_dept);        cStmt1.execute();                                             /* --1 */        // Set up the dynamic call to the second procedure        // Setup input parameters        // Register output parameters        // Call the procedures        sql = "CALL emp_multi2(?)";        cStmt2 = con.prepareCall(sql);        cStmt2.setString(1, p_dept);        cStmt2.execute();                                             /* --2 */        // The first proc has 1 result set, the second has two        // Process the first result set of each proc in parallel        // Then process the second result set of the second proc        rs1 = cStmt1.getResultSet();                                  /* --3 */        rs2 = cStmt2.getResultSet();                                  /* --4 */        p_names[0] = "The employees are: ";        while (rs1.next()) { /* --5 */           rs2.next();           v_fname = rs1.getString(1);           v_lname = rs2.getString(1);           p_names[0] = p_names[0] + v_fname + " " + v_lname + " ";        }        rs2.next();        // Close the resources associated with the first proc       rs1.close();       cStmt1.close();       // Continue processing result sets from the second proc       cStmt2.getMoreResults();                                       /* --6 */       rs2 = cStmt2.getResultSet();       p_total[0] = new java.math.BigDecimal("0.00"); /* --7 */       while (rs2.next()) {          v_salary = rs2.getBigDecimal(1).setScale(2);          p_total[0] = p_total[0].add(v_salary);       }       // Close the result set and statement       rs2.close();       cStmt2.close();     } } 

The two procedures emp_multi1 and emp_multi2 are called on Lines (1) and (2), respectively. The result set from emp_multi1 is received in object rs1 on Line (3), and the result set from emp_multi2 is received in object rs2 on Line (4). The while loop on Line (5) processes the two result sets in parallel. emp_multi2 has a second result set that is accessed on Line (6), and the while loop on Line (7) processes the result set.

Figure H.8 shows an example of how the procedure can be registered on the iSeries platform.

Figure H.8. Registering the empMulti procedure on iSeries.

 CREATE PROCEDURE ReceiveMulti ( IN p_dept CHARACTER(3),                                 OUT p_names VARCHAR(100),                                 OUT p_total DECIMAL(9,2) )     SPECIFIC RECEIVE_MULTI     NOT DETERMINISTIC     LANGUAGE Java     EXTERNAL NAME 'DB2ADMIN.RECEIVE_MULTI:ReceiveMulti.ReceiveMulti.receiveMulti'     FENCED     PARAMETER STYLE JAVA