Query Returning Multiple Result Sets

Query Returning Multiple Result Sets

InterSystems IRIS allows you to define a stored procedure that returns multiple result sets. The InterSystems JDBC driver supports the execution of such stored procedures. Here is an example of an InterSystems IRIS stored procedure that returns two result sets (note that the two query results have different column structures):

   /// This class method produces two result sets.
   ClassMethod DRS(st) [ ReturnResultsets, SqlProc ]
   {
    $$$ResultSet("select Name from Demo.Person where Name %STARTSWITH :st")
    $$$ResultSet("select Name, DOB from Demo.Person where Name %STARTSWITH :st")
    Quit
   }

$$$ResultSet is a predefined InterSystems macro that prepares a SQL statement (specified as a string literal), executes it, and returns the resultset.

The following code executes the stored procedure and iterates through both of the returned result sets:

• Create the java.sql.CallableStatement object and initialize it using the name of the stored procedure. Set the query parameters and use execute to execute the query:

     CallableStatement cs = dbconnection.prepareCall("call Demo.Person_DRS(?)");
     cs.setString(1,"A");
     boolean success=cs.execute();
  
  

  

• Iterate through the pair of result sets displaying the data. After getResultSet retrieves the current result set, getMoreResults closes it and moves to the CallableStatement object's next result set.

     if(success) do{
        java.sql.ResultSet rs = cs.getResultSet();
        ResultSetMetaData rsmd = rs.getMetaData();
        for (int j=1; j<rsmd.getColumnCount() + 1; j++)
           System.out.print(rsmd.getColumnName(j)+ "\t\t");
        System.out.println();
        int colnum = rsmd.getColumnCount();
        while (rs.next()) {
           for (int i=1; i<=colnum; i++)
              System.out.print(rs.getString(i) + " \t ");
           System.out.println();
        }
        System.out.println();
      } while (cs.getMoreResults());