Re: Returning result set from external stored procedure

Linda wrote:

I have an external stored procedure that calls an rpg program that builds a
dynamic select statement and am having a problem getting the result set
back.

Here's a synopsis of some working code:

build STATEMENT

exec sql close MBR_LIST;

exec sql prepare PREPARED_STATEMENT from :STATEMENT;
if (SQLSTATE <> SQL_NORMAL);
dump(a) 'prepare()';
return;
endif;
exec sql declare MBR_LIST scroll cursor for PREPARED_STATEMENT;

exec sql open MBR_LIST;
if (SQLSTATE <> SQL_NORMAL);
dump(a) 'open()';
return;
endif;

exec sql
set result sets for return to client
cursor MBR_LIST;

return;


CREATE PROCEDURE mylib/myproc(
IN firstName VARCHAR ( 27),
IN lastName VARCHAR ( 25),
...)
RESULT SETS 1
LANGUAGE RPGLE
NOT DETERMINISTIC READS SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME mylib/mypgm
PARAMETER STYLE GENERAL WITH NULLS

Some judicious DUMP operations can sometimes help isolate the issue, and don't forget to check SQLSTATE after every executable SQL statement!
--buck