Re: Trying to make a RPG Stored Procedure that returns a resultset

On 2/6/2013 5:12 PM, Mike Wills wrote:

I have found many sources on line that uses occurs for the array. But I am
using dim. Mine doesn't seem to work. What do I need to change or fix?

'Doesn't work' is a bit vague. Presume you are calling the stored
procedure in iNav and do not see the result set, but are getting a good
return code from DB2.

Also, how do I test to make sure it is returning data before I add to my
program?

I test with iNav. Java works too.

D rowCount S 10I 0
D pMessages DS likeds(MessageList) dim(30)

P AddMessage B
D AddMessage PI
D message 50A varying const
D warningError N const
D terminalError N const
/free
index += 1;
pMessages(index).Message = message;
pMessages(index).TerminalError = terminalError;
pMessages(index).WarningError = warningError;
rowCount += 1;
return;
/end-free
P AddMessage E

exec sql
set result sets array:pmessages for:rowcount rows;

None of my stored procedures use a LIKEDS(). I explicitly declare each
column in the result set. Also, make sure your CREATE PROCEDURE matches
your parameter list and parameter style.

CREATE PROCEDURE mylib/mysp
(in rowCount int)
RESULT SETS 1
LANGUAGE RPGLE
NOT DETERMINISTIC READS SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME mylib/mysp
PARAMETER STYLE GENERAL

dmysp pi
d rowCount 10i 0

drs ds dim(1000) qualified
d count 10i 0
d name 50 varying

exec sql
set result sets array :RS for :ROWCOUNT rows;

--buck