Returning Multiple Result Sets on iSeries

I was curious as to how a Java client could receive two result sets from iSeries stored procedures.  It turned out that the Java was simpler to write than the SQL.  The SQL is simple enough but finding exactly what is needed is not easy to find in IBM's documentation.  

SQL RPG programs are limited to returning only one result set.  I do not show it here but, a SQL wrapper like the one here could call SQL RPG programs each of which returns a result set to the client.

Here, the first stored procedure calls the following two stored procedures, each of which returns a result set.

DROP PROCEDURE MYLIB/STOPRO;               
CREATE PROCEDURE STOPRO (IN ACCT DECIMAL(9,0)) 
LANGUAGE SQL 
RESULT SETS 2  
SPECIFIC MYLIB/STOPRO                       
BEGIN  
  
  CALL STOPRO1(ACCT);                 
  CALL STOPRO2();                     
  
END                                           

DROP PROCEDURE MYLIB/STOPRO1;                     
CREATE PROCEDURE STOPRO1 (IN ACCT DECIMAL(9,0))     
LANGUAGE SQL 
RESULT SETS 1  
SPECIFIC MYLIB/STOPRO1                           
BEGIN 
  DECLARE C1 CURSOR WITH RETURN TO CLIENT           
  FOR
  SELECT ACCT#, POPNFG, DTEMTG           
  FROM CHMSTR WHERE ACCT#=ACCT;                     
  OPEN C1; 
END

DROP PROCEDURE MYLIB/STOPRO2;      
CREATE PROCEDURE STOPRO2 ()          
LANGUAGE SQL 
RESULT SETS 1
SPECIFIC MYLIB/STOPRO2                             
BEGIN 
  DECLARE C2 CURSOR WITH RETURN TO CLIENT             
  FOR         
  SELECT 'GREG' FROM SYSIBM/SYSDUMMY1 ;               
  OPEN C2; 
END 

A Simple Java Program Sufficient to Test That Two Result Sets Are Returned

The do loop in the main() method executes stmt.getMoreResults() which moves the statement object to the next result set.

import java.sql.CallableStatement;  
import java.sql.Connection; 
import java.sql.ResultSet; 
import java.sql.ResultSetMetaData; 
import java.sql.DriverManager; 

import java.sql.SQLException; 
import java.io.FileNotFoundException; 
import java.io.IOException; 

public class SimpleJDBC { 

   CallableStatement stmt; 
   Connection conn; 

   SimpleJDBC(int acct) throws Exception { 
      setConn(); 
      String query = "CALL STOPRO(?)"; 
      stmt = conn.prepareCall(query); 
      stmt.setInt(1, acct);   
   } 
    
   public void setConn() throws SQLException, ClassNotFoundException, IOException { 
       String AS400SYSTEM = "MYAS400"; 
    
       Class.forName("com.ibm.as400.access.AS400JDBCDriver"); 
       conn = DriverManager.getConnection("jdbc:as400://" + AS400SYSTEM + ";naming=system;prompt=true"); 
   }   

   void printResults(Statement s) throws Exception { 
      ResultSet rs = s.getResultSet(); 
      ResultSetMetaData rsmd = rs.getMetaData(); 
      int columnCount = rsmd.getColumnCount(); 
      while (rs.next()) { 
         for (int x = 1; x <= columnCount; x++) { 
            System.out.print("| " + rs.getString(x) + " |"); 
         } 
      } 
      System.out.println(); 
   } 
            
   public static void main(String[] args) throws Exception { 
      try { 
         SimpleJDBC simple = new SimpleJDBC(548143); 
         simple.stmt.executeQuery(); 
          
         do { 
           System.out.println("\nResult Set #"); 
           simple.printResults(simple.stmt); 
         } while (simple.stmt.getMoreResults()); 

      } catch(Exception e) { 
         e.printStackTrace(); 
      } 
   } 
}