Writing a program or SQL procedure to receive the result sets from a stored procedure

You can write a program to receive results sets from a stored procedure for either a fixed number of result sets, for which you know the contents, or a variable number of result sets, for which you do not know the contents.

Returning a known number of result sets is simpler to write, but if you write the code to handle a varying number of result sets you do not need to make major modifications to your program if the stored procedure changes.

The basic steps for receiving result sets are as follows:

Declare a locator variable for each result set that will be returned.
If you do not know how many result sets will be returned, declare enough result set locators for the maximum number of result sets that might be returned.
Call the stored procedure and check the SQL return code.
If the SQLCODE from the CALL statement is +466, the stored procedure has returned result sets.
Determine how many result sets the stored procedure is returning.
If you already know how many result sets the stored procedure returns, you can skip this step.

Use the SQL statement DESCRIBE PROCEDURE to determine the number of result sets. DESCRIBE PROCEDURE places information about the result sets in an SQLDA or SQL descriptor.

For an SQL descriptor, when the DESCRIBE PROCEDURE statement completes, the following values can be retrieved:
- DB2_RESULT_SETS_COUNT contains the number of result sets returned by the stored procedure.
- One descriptor area item is returned for each result set:
  - DB2_CURSOR_NAME contains the name of the cursor used by the stored procedure to return the result set.
  - The DB2_RESULT_SET_ROWS contains the estimated number of rows in the result set. A value of -1 indicates that no estimate of the number of rows in the result set is available.
  - DB2_RESULT_SET_LOCATOR contains the value of the result set locator associated with the result set.
For an SQLDA, make the SQLDA large enough to hold the maximum number of result sets that the stored procedure might return. When the DESCRIBE PROCEDURE statement completes, the fields in the SQLDA contain the following values:
- SQLD contains the number of result sets returned by the stored procedure.
- Each SQLVAR entry gives information about a result set. In an SQLVAR entry:
  - The SQLNAME field contains the name of the cursor used by the stored procedure to return the result set.
  - The SQLIND field contains the estimated number of rows in the result set. A value of -1 indicates that no estimate of the number of rows in the result set is available.
  - The SQLDATA field contains the value of the result set locator, which is the address of the result set.
Link result set locators to result sets.
You can use the SQL statement ASSOCIATE LOCATORS to link result set locators to result sets. The ASSOCIATE LOCATORS statement assigns values to the result set locator variables. If you specify more locators than the number of result sets returned, the extra locators will be ignored.

If you executed the DESCRIBE PROCEDURE statement previously, the result set locator values can be retrieved from the DB2_RESULT_SET_LOCATOR in the SQL descriptor or from the SQLDATA fields of the SQLDA. You can copy the values from these fields to the result set locator variables manually, or you can execute the ASSOCIATE LOCATORS statement to do it for you.

The stored procedure name that you specify in an ASSOCIATE LOCATORS or DESCRIBE PROCEDURE statement must be a procedure name that has already been used in the CALL statement that returns the result sets.
Allocate cursors for fetching rows from the result sets.
Use the SQL statement ALLOCATE CURSOR to link each result set with a cursor. Execute one ALLOCATE CURSOR statement for each result set. The cursor names can be different from the cursor names in the stored procedure.
Determine the contents of the result sets.
If you already know the format of the result set, you can skip this step.

Use the SQL statement DESCRIBE CURSOR to determine the format of a result set and put this information in an SQL descriptor or an SQLDA. For each result set, you need an SQLDA big enough to hold descriptions of all columns in the result set.

You can use DESCRIBE CURSOR only for cursors for which you executed ALLOCATE CURSOR previously.

After you execute DESCRIBE CURSOR, if the cursor for the result set is declared WITH HOLD, for an SQL descriptor DB2_CURSOR_HOLD can be checked. For an SQLDA the high-order bit of the eighth byte of field SQLDAID in the SQLDA is set to 1.
Fetch rows from the result sets into host variables by using the cursors that you allocated with the ALLOCATE CURSOR statements.
If you executed the DESCRIBE CURSOR statement, perform these steps before you fetch the rows:
1. Allocate storage for host variables and indicator variables. Use the contents of the SQL descriptor or SQLDA from the DESCRIBE CURSOR statement to determine how much storage you need for each host variable.
2. Put the address of the storage for each host variable in the appropriate SQLDATA field of the SQLDA.
3. Put the address of the storage for each indicator variable in the appropriate SQLIND field of the SQLDA.
Fetching rows from a result set is the same as fetching rows from a table.

The following examples show C language code that accomplishes each of these steps. Coding for other languages is similar.

The following example demonstrates how you receive result sets when you know how many result sets are returned and what is in each result set.

/*************************************************************/
/* Declare result set locators.  For this example,           */
/* assume you know that two result sets will be returned.    */
/* Also, assume that you know the format of each result set. */
/*************************************************************/
  EXEC SQL BEGIN DECLARE SECTION;
   static volatile SQL TYPE IS RESULT_SET_LOCATOR loc1, loc2;
  EXEC SQL END DECLARE SECTION;
  ⋮
/*************************************************************/
/* Call stored procedure P1.                                 */
/* Check for SQLCODE +466, which indicates that result sets  */
/* were returned.                                            */
/*************************************************************/
  EXEC SQL CALL P1(:parm1, :parm2, ...);
  if(SQLCODE==+466)
  {
  /*************************************************************/
  /* Establish a link between each result set and its          */
  /* locator using the ASSOCIATE LOCATORS.                     */
  /*************************************************************/
    EXEC SQL ASSOCIATE LOCATORS (:loc1, :loc2) WITH PROCEDURE P1;
    ⋮
  /*************************************************************/
  /* Associate a cursor with each result set.                  */
  /*************************************************************/
    EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :loc1;
    EXEC SQL ALLOCATE C2 CURSOR FOR RESULT SET :loc2;
  /*************************************************************/
  /* Fetch the result set rows into host variables.            */
  /*************************************************************/
    while(SQLCODE==0)
    {
      EXEC SQL FETCH C1 INTO :order_no, :cust_no;
    ⋮
    }
    while(SQLCODE==0)
    {
      EXEC SQL FETCH C2 :order_no, :item_no, :quantity;
    ⋮
    }
  }

The following example demonstrates how you receive result sets when you do not know how many result sets are returned or what is in each result set.

/*************************************************************/
/* Declare result set locators.  For this example,           */
/* assume that no more than three result sets will be        */
/* returned, so declare three locators.  Also, assume        */
/* that you do not know the format of the result sets.       */
/*************************************************************/
  EXEC SQL BEGIN DECLARE SECTION;
   static volatile SQL TYPE IS RESULT_SET_LOCATOR loc1, loc2, loc3;
  EXEC SQL END DECLARE SECTION;
  ⋮
/*************************************************************/
/* Call stored procedure P2.                                 */
/* Check for SQLCODE +466, which indicates that result sets  */
/* were returned.                                            */
/*************************************************************/
  EXEC SQL CALL P2(:parm1, :parm2, ...);
  if(SQLCODE==+466)
  {
  /*************************************************************/
  /* Determine how many result sets P2 returned, using the     */
  /* statement DESCRIBE PROCEDURE.  :proc_da is an SQLDA       */
  /* with enough storage to accommodate up to three SQLVAR     */
  /* entries.                                                  */
  /*************************************************************/
    EXEC SQL DESCRIBE PROCEDURE P2 INTO :proc_da;
    ⋮
  /*************************************************************/
  /* Now that you know how many result sets were returned,     */
  /* establish a link between each result set and its          */
  /* locator using the ASSOCIATE LOCATORS.  For this example,  */
  /* we assume that three result sets are returned.            */
  /*************************************************************/
    EXEC SQL ASSOCIATE LOCATORS (:loc1, :loc2, :loc3) WITH PROCEDURE P2;
    ⋮
  /*************************************************************/
  /* Associate a cursor with each result set.                  */
  /*************************************************************/
    EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :loc1;
    EXEC SQL ALLOCATE C2 CURSOR FOR RESULT SET :loc2;
    EXEC SQL ALLOCATE C3 CURSOR FOR RESULT SET :loc3;

  /*************************************************************/
  /* Use the statement DESCRIBE CURSOR to determine the        */
  /* format of each result set.                                */
  /*************************************************************/
    EXEC SQL DESCRIBE CURSOR C1 INTO :res_da1;
    EXEC SQL DESCRIBE CURSOR C2 INTO :res_da2;
    EXEC SQL DESCRIBE CURSOR C3 INTO :res_da3;
    ⋮
  /*************************************************************/
  /* Assign values to the SQLDATA and SQLIND fields of the     */
  /* SQLDAs that you used in the DESCRIBE CURSOR statements.   */
  /* These values are the addresses of the host variables and  */
  /* indicator variables into which DB2 will put result set    */
  /* rows.                                                     */
  /*************************************************************/
    ⋮
  /*************************************************************/
  /* Fetch the result set rows into the storage areas          */
  /* that the SQLDAs point to.                                 */
  /*************************************************************/
    while(SQLCODE==0)
    {
      EXEC SQL FETCH C1 USING :res_da1;
    ⋮
    }
    while(SQLCODE==0)
    {
      EXEC SQL FETCH C2 USING :res_da2;
    ⋮
    }
    while(SQLCODE==0)
    {
      EXEC SQL FETCH C3 USING :res_da3;
    ⋮
    }
  }

The following example demonstrates how you receive result sets using an SQL descriptor.

This is the SQL procedure that will be called:

create procedure owntbl()  
  dynamic result sets 1                         
  begin                                         
    declare c1 cursor for                       
      select name, dbname from qsys2.systables  
        where creator = system_user ;           
    open c1 ;                                   
    return ;                                    
  end

This is the program that will process the result sets:

#include <string.h> 
#include <stdio.h>
#include <stdlib.h>

EXEC SQL INCLUDE SQLCA;
/*************************************************************/
/* Declare result set locators.  For this example,           */
/* you know that only one result set will be returned,       */
/* so only one locator is declared.                          */
/*************************************************************/
EXEC SQL BEGIN DECLARE SECTION;
 static volatile SQL TYPE IS RESULT_SET_LOCATOR loc1;
 struct {
   short len;
   char  data[128];
 } tblName;   /* table name  */

 struct {
   short len;
   char  data[128];
 } schName;   /* schema name */
EXEC SQL END DECLARE SECTION;

void main(int argc, char* argv[])
{
/*************************************************************/
/* Call the procedure that might return a result set.  Check */
/* the returned SQLCODE to see if result sets were returned. */
/*************************************************************/
  int noMoreData = 0;
  EXEC SQL WHENEVER SQLERROR GOTO error;
  EXEC SQL CALL OWNTBL ;
  if (SQLCODE != 466) {
    goto error;
  }
/*************************************************************/
/* Since you know only one result set can be returned from   */
/* this procedure, associate a locator with the result set   */
/* and define a cursor to be used with it.                   */
/*************************************************************/
  EXEC SQL ASSOCIATE LOCATORS (:loc1) WITH PROCEDUREOWNTBL ;
  EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :loc1 ;
/*************************************************************/
/* Define the descriptor to use for fetching data from the   */
/* cursor.                                                   */
/*************************************************************/
  EXEC SQL ALLOCATE DESCRIPTOR 'desc' WITH MAX 10 ;
  EXEC SQL DESCRIBE CURSOR C1 USING SQL DESCRIPTOR 'desc' ;
  EXEC SQL WHENEVER NOT FOUND GOTO enddata;
  while ( noMoreData == 0 ) {
    EXEC SQL FETCH C1 INTO SQL DESCRIPTOR 'desc' ;
    memset(tblName.data,0x00,sizeof(tblName.data));
    memset(schName.data,0x00,sizeof(schName.data));
    EXEC SQL GET DESCRIPTOR 'desc' VALUE 1 :tblName = DATA;
    EXEC SQL GET DESCRIPTOR 'desc' VALUE 2 :schName = DATA;
    printf("Table: %s   Schema: %s \n",
           tblName.data,schName.data);
  }

enddata:
  printf("All rows fetched.\n");
  return;

error:
  printf("Unexpected error, SQLCODE = %d \n", SQLCODE);
  return;
}

The following example demonstrates how you can use an SQL procedure to receive result sets. It is just a fragment of a larger SQL procedure.

DECLARE RESULT1 RESULT_SET_LOCATOR VARYING;           
DECLARE RESULT2 RESULT_SET_LOCATOR VARYING;           
⋮
CALL TARGETPROCEDURE();

ASSOCIATE RESULT SET LOCATORS(RESULT1,RESULT2)
  WITH PROCEDURE TARGETPROCEDURE;                     
ALLOCATE RSCUR1 CURSOR FOR RESULT1;                   
ALLOCATE RSCUR2 CURSOR FOR RESULT2;                   

WHILE AT_END = 0 DO                                 
  FETCH RSCUR1 INTO VAR1;                   
  SET TOTAL1 = TOTAL1 + VAR1;                         
END WHILE;                                             

WHILE AT_END = 0 DO                                 
  FETCH RSCUR2 INTO VAR2;                   
  SET TOTAL2 = TOTAL2 + VAR2;                         
END WHILE;                                             
⋮