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:
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;
⋮