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

You can write a program to receive results set 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.

About this task

A program for a fixed number of result sets is simpler to write than a program for a variable number of result sets. However, if you write a program for a variable number of result sets, you do not need to make modifications to the program if the stored procedure changes.

If your program calls an SQL procedure that returns result sets, you must write the program for a fixed number of result sets.

In the following steps, you do not need to connect to the remote location when you execute these statements:

DESCRIBE PROCEDURE
ASSOCIATE LOCATORS
ALLOCATE CURSOR
DESCRIBE CURSOR
FETCH
CLOSE

Procedure

To write a program to receive the result sets from a stored procedure:

Declare a locator variable for each result set that is to be returned.
If you do not know how many result sets are to 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, 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. Make this 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 that are returned by the stored procedure.
- Each SQLVAR entry gives the following information about a result set:
  - The SQLNAME field contains the name of the SQL cursor that is used by the stored procedure to return the result set.
  - The SQLIND field contains the value -1, which 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 by performing one of the following actions:
- Use the ASSOCIATE LOCATORS statement. You must embed this statement in an application or SQL procedure. The ASSOCIATE LOCATORS statement assigns values to the result set locator variables. If you specify more locators than the number of result sets that are returned, Db2 ignores the extra locators.
- If you executed the DESCRIBE PROCEDURE statement previously, the result set locator values are in the SQLDATA fields of the SQLDA. You can copy the values from the SQLDATA fields to the result set locators 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 statement or DESCRIBE PROCEDURE statement must match the stored procedure name in the CALL statement as follows:
- If the name is unqualified in the CALL statement, do not qualify it.
- If the name is qualified with a schema name in the CALL statement, qualify it with the schema name.
- If the name is qualified with a location name and schema name in the CALL statement, qualify it with a location name and schema name.
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 differ from the cursor names in the stored procedure.

To use the ALLOCATE CURSOR statement, you must embed it in an application or SQL procedure.
Determine the contents of the result sets.
If you already know the format of the result set, skip this step.

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

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

After you execute DESCRIBE CURSOR, if the cursor for the result set is declared WITH HOLD, the high-order bit of byte 8 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.
Fetching rows from a result set is the same as fetching rows from a table.
If you executed the DESCRIBE CURSOR statement, perform the following steps before you fetch the rows:
1. Allocate storage for host variables and indicator variables. Use the contents of the 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.

Example

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

The following example demonstrates how to 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 to 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 can use an SQL procedure to receive result sets. The logic assumes that no handler exists to intercept the +466 SQLCODE, such as DECLARE CONTINUE HANDLER FOR SQLWARNING ..... Such a handler causes SQLCODE to be reset to zero. Then the test for IF SQLCODE = 466 is never true and the statements in the IF body are never executed.

DECLARE RESULT1 RESULT_SET_LOCATOR VARYING;                     
 DECLARE RESULT2 RESULT_SET_LOCATOR VARYING;                     
 DECLARE AT_END, VAR1, VAR2 INT DEFAULT 0;                       
 DECLARE SQLCODE INTEGER DEFAULT 0;                                    
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET AT_END = 99;         
 SET TOTAL1 = 0;                                               
 SET TOTAL2 = 0;                                               
 CALL TARGETPROCEDURE();                                        
 IF SQLCODE = 466 THEN                                       
   ASSOCIATE RESULT SET LOCATORS(RESULT1,RESULT2)                
      WITH PROCEDURE SPDG3091;                                     
   ALLOCATE RSCUR1 CURSOR FOR RESULT1;                           
   ALLOCATE RSCUR2 CURSOR FOR RESULT2;                           
   WHILE AT_END = 0 DO                                           
     FETCH RSCUR1 INTO VAR1;                                      
     SET TOTAL1 = TOTAL1 + VAR1;  
     SET VAR1 = 0;   /* Reset so the last value fetched is not added after AT_END */                            
   END WHILE; 
   SET AT_END = 0;  /* Reset for next loop */                                                
   WHILE AT_END = 0 DO                                           
     FETCH RSCUR2 INTO VAR2;                                     
     SET TOTAL2 = TOTAL2 + VAR2; 
     SET VAR2 = 0;   /* Reset so the last value fetched is not added after AT_END */                                   
   END WHILE;                                                    
 END IF;