Control The Flow Of Stored Procedure Result Sets
July 24, 2013 Hey, Mike
|
I have an RPG program (defined as an SQL external stored procedure) that returns a data structure array as a result set. When this procedure is called from iNavigator, I can see the result set. But in our .NET client/server environment, the result set is not returned to the .NET program. Further, the .NET program first calls a “setup” stored procedure that establishes the library list and general environment settings. Thereafter this “setup” procedure calls the RPG program that returns the result set. The RPG program runs upon request from the .NET world but no result set is returned. –B.C. Dear B.C., The answer to this puzzling behavior can be found by reviewing the SET RESULT SETS statement. When returning a result set from a stored procedure (whether the source is an array or open cursor), the SET RESULT SETS statement specifies whether the result set is intended to be consumed by the client (i.e., the outermost procedure or caller in the call stack) or the caller (the prior procedure in the call stack). These two options are known as WITH RETURN TO CLIENT and WITH RETURN TO CALLER. Figure 1 below shows the options when returning a result set with multiple call stack entries. Incidentally, the default is WITH RETURN TO CALLER.
In this illustration, there is a sequence of four procedures. The last procedure in the stack (“Procedure D”) returns a result set. If “Return to Client” is specified then “Procedure A” can consume the opened result set. If “Return to Caller” is specified then “Procedure C” can consume the opened result set. Notice that there is no way that “Procedure B” can consume the result set returned by “Procedure D.” So, in DB2 for i, the result set can be returned to the prior caller or the primary caller but not to procedures in between. Now moving to your problem, the call stack looks something like Figure 2:
Remembering that the default is WITH RETURN TO CALLER, when the .NET program attempts to call the RPG stored procedure via the intermediate “setup” procedure, the RPG program makes the result set available to the “Setup” procedure. However, by changing the RPG program to use the “WITH RETURN TO CLIENT” option on the SET RESULT SETS statement, the RPG procedure will return the result set to the .NET caller. Usually, this isn’t a problem because if the .NET program called the RPG procedure directly, either option (CALLER/CLIENT) would work. However, it’s when you start nesting procedure calls that things can get thorny. If you’re having difficulty envisioning how this works, the following stored procedure MY_RESULTS will open a cursor and return the result set to either the caller or client (based on a specified parameter value).
CREATE OR REPLACE PROCEDURE DEV.MY_RESULTS
(@RETURN_TO_CLIENT CHAR(1))
LANGUAGE SQL
RESULT SETS 1
SET OPTION COMMIT=*NONE
BEGIN
DECLARE MY_DATA INSENSITIVE CURSOR
WITH RETURN TO CLIENT FOR
SELECT *
FROM QIWS.QCUSTCDT
;
OPEN MY_DATA;
IF @RETURN_TO_CLIENT='Y' THEN
SET RESULT SETS
WITH RETURN TO CLIENT
CURSOR MY_DATA;
ELSE
SET RESULT SETS
WITH RETURN TO CALLER
CURSOR MY_DATA;
END IF;
END
;
This procedure by default declares that its cursor will be returned to the client (i.e., the outermost caller whether a DB2 procedure, ADO.NET, JDBC, or OLE DB request). However, one of two possible SET RESULT SET statements are issued that can either keep the result set as available to the client or optionally override the result set destination so the caller (i.e., the prior procedure) can access it. In conclusion, an SQL result set opened in a stored procedure can be consumed by the caller (prior call stack) or the client (initiating program on the call stack). A procedure’s result sets are not available to other procedures in the call stack that fall “in between” these two options. If you have this situation arise, I’d suggest adding a parameter to dump the result set to a temporary table so that any procedure in between could pick up the result. Finally, SET RESULT SETS can be used to conditionally override the default behavior so that a procedure can make its result set available to either the “caller” or the “client.” Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.
|
