Receiving procedure result sets in SQLJ applications and routines

You can receive result sets from procedures you invoke from an SQLJ routine or application.

Procedure

To accept procedure result sets from within an SQLJ routine or application:

Open a database connection (using a Connection object):

  Connection con =
      DriverManager.getConnection("jdbc:db2:sample", userid, passwd);

Set the default context (using a DefaultContext object):

  DefaultContext ctx = new DefaultContext(con);            
  DefaultContext.setDefaultContext(ctx);

Set the execution context (using an ExecutionContext object):
```
  ExecutionContext execCtx = ctx.getExecutionContext();
```
Invoke a procedure that returns result sets. In the following example, a procedure named GET_HIGH_SALARIES is invoked, and is passed an input variable (called inSalaryThreshold):
```
  #sql {CALL GET_HIGH_SALARIES(:in inSalaryThreshold, :out outErrorCode)};
```

Declare a ResultSet object, and use the ExecutionContext object's getNextResultSet() method to accept result sets from the procedure. For multiple result sets, put the getNextResultSet() call in a loop structure. Each result set returned by the procedure will spawn a loop iteration. Inside the loop, you can fetch the result set rows method, and then close the result set object (with the ResultSet object's close() method). For example:

  ResultSet rs = null;

  while ((rs = execCtx.getNextResultSet()) != null)
  {
    ResultSetMetaData stmtInfo = rs.getMetaData();
    int numOfColumns = stmtInfo.getColumnCount();
    int r = 0;

    // Result set rows are fetched and printed to screen.
    while (rs.next())
    {
      r++;
      System.out.print("Row: " + r + ": ");
      for (int i=1; i <= numOfColumns; i++)
      {
        System.out.print(rs.getString(i));
        if (i != numOfColumns)
        {
          System.out.print(", ");
        }
      }
      System.out.println();
    }

    rs.close();
  }