Connecting to Your Database
Using Embedded SQL with SAP Adaptive Server Enterprise

To access rows returned in a result set, you use the FETCH statement the same way you use it for cursors. The FETCH statement can be executed after any EXECUTE statement that refers to a procedure that returns a result set.

For example:

FETCH emp_proc INTO :emp_name_var;

Example 1

Database stored procedures can return multiple result sets. Assume you define a database stored procedure proc2 as:

CREATE PROCEDURE proc2 AS
   SELECT emp_name FROM employee
   SELECT part_name FROM parts

PowerBuilder provides access to both result sets:

// Declare the procedure.
DECLARE emp_proc2 PROCEDURE FOR proc2;

// Declare some variables to hold results.
string    emp_name_var
string    part_name_var

// Execute the stored procedure.
EXECUTE emp_proc2;

// Loop through all rows in the first result
// set.
DO WHILE SQLCA.sqlcode = 0

// Fetch the next row from the first result set.
   FETCH emp_proc2 INTO :emp_name_var;
LOOP

// At this point we have exhausted the first
// result set. After this occurs, 
// PowerBuilder notes that there is another
// result set and internally shifts result sets. 
// The next FETCH executed will retrieve the 
// first row from the second result set.
// Fetch the first row from the second result
// set.
if SQLCA.sqlcode = 100 then
   FETCH emp_proc2 INTO :part_name_var;
end if

// Loop through all rows in the second result 
// set. 
DO WHILE SQLCA.sqlcode = 0

// Fetch the next row from the second result
// set.
   FETCH emp_proc2 INTO :part_name_var;
LOOP

// Close the procedure.
CLOSE emp_proc2;

The result sets that will be returned when a database stored procedure executes cannot be determined at compile time. Therefore, you must code FETCH statements that exactly match the format of a result set returned by the stored procedure when it executes.

Example 2

In the preceding example, if instead of coding the second fetch statement as:

FETCH emp_proc2 INTO :part_name_var;

you coded it as:

FETCH emp_proc2 INTO :part_var1,:part_var2,:part_var3;

the statement would compile without errors. But an execution error would occur: the number of columns in the FETCH statement does not match the number of columns in the current result set. The second result set returns values from only one column.