OpenESQL applications support multiple results sets when you define your DECLARE CURSOR statements to include multiple SELECT
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:
DECLARE C1 CURSOR
SELECT CUSTID FROM COMPANY;
SELECT CUSTNAME FROM COMPANY;
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:
GET NEXT RESULT SET FOR C1
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.