Working With OpenESQL Cursors and Result Sets

OpenESQL applications support multiple results sets when you define your DECLARE CURSOR statements to include multiple SELECT statements.

Note: You can also reference multiple result sets by executing a stored procedure. For more information on how this works, see Working With OpenESQL Stored Procedures and Result Sets.

To return multiple result sets for a particular cursor, its DECLARE CURSOR statement in your application must open the cursor and also contain a SELECT statement for each result set you want to return. For example:

      EXEC SQL 
          DECLARE C1 CURSOR
          FOR
              SELECT CUSTID FROM COMPANY;
              SELECT CUSTNAME FROM COMPANY;
      END-EXEC
      EXEC SQL OPEN C1 END-EXEC

In this example, we return two result sets. After opening the C1 cursor, execute a FETCH statement to consume the first result set - that defined by the SELECT CUSTID FROM COMPANY statement.

When FETCH returns an SQLCODE of 100 (signifying the end of the current result set), issue the GET NEXT RESULT SET statement to fetch the result set for the next SELECT statement. For example:

      EXEC SQL 
          GET NEXT RESULT SET FOR C1
      END-EXEC

Once the result set for SELECT CUSTNAME FROM COMPANY is consumed, GET NEXT RESULT SET returns an SQLCODE of 100, indicating that no additional result sets are available.

Note: If another result set is available, meaning your DECLARE CURSOR statement contains at least one additional SELECT statement that has not been processed, GET NEXT RESULT SET returns an SQLCODE of 0. To retrieve all available result sets, continue issuing the GET NEXT RESULT SET statement until it returns an SQLCODE of 100.