How to return multiple resultset from a rpgile procedure to a java method

The following code will return two result-sets.

Just remember to combine the SET RESULT SETS for the two cursors into one statement.

see this question: https://www.experts-exchange.com/questions/23512641/return-2-result-sets-from-a-stored-proc.html

HTH,
DaveSlash

CREATE PROCEDURE MyProc (
  ... some parameters ...
)
RESULT SETS 2                                 
LANGUAGE C NOT DETERMINISTIC MODIFIES SQL DATA
EXTERNAL NAME MYPROC
PARAMETER STYLE GENERAL WITH NULLS
 
... some stuff ...
 
exec sql
DECLARE resultset CURSOR FOR
select sectionTitle
from   TTSECTDTLS
;
 
exec sql
DECLARE resultset2 CURSOR FOR
select freeTextID,
       freetextData
from   TTFOOTDTLS
;
 
EXEC SQL OPEN resultset; 
EXEC SQL OPEN resultset2;
 
exec sql
SET RESULT SETS CURSOR resultset, CURSOR resultset2;

Select allOpen in new window

Dear daveslash,
Thanks for your reply. However, we are using a external rpgle procedure which uses the DB2/UDB on a iSeries. I have created a sample programm ( using which the procedure will be created)

The trickey part is we are doing some businesss logic implementation on the data retrived from DB.
which means we are not gettingdirect rows from the database.

We are using a multiple occurence Datastructure in rpgle returned as resultset in Java.

attached code is a sample code. to return the DS.

MULTPLE-RS.doc

As I noted in my posting above, you need to combine your SET RESULT SETS statements from three into one.

e.g.
SET RESULT SETS CURSOR resultset, CURSOR resultset2;

globalnest:

DaveSlash's answer seems to be your best course; he deserves the credit.

He's saying to make this change:

Report back results if additional help is needed.

Tom

Hello Tom and daveslash,

Thank you for your reply & I am sorry for the delay in responce.

I have tried as per Tom's suggestion. However, I am getting a syntex error if I
write the code as above  (c+ set result sets ARRAY  :A FOR 2 ROWS , Array :b for 2 rows            )

I have tried another alternative to create a cursor on the DS which got compiled but when the procedure called from the Java , it returns null for the resultset.

Thank you again for giving me your precious time.

Regards,
 Rahul

c/exec sql declare N cursor  for            
c+ select *     from A                      
c/end-exec                                  
                                            
c/exec sql declare M cursor  for            
c+ select *     from B                      
c/end-exec                                  
C/EXEC SQL                                  
C+ OPEN N                                   
C/END-EXEC                                  
C/EXEC SQL                                  
C+ OPEN M                                   
C/END-EXEC                                  
c/exec sql                                  
c+ set  result sets Cursor M,               
c+                  Cursor N                
c/end-exec

Select allOpen in new window

Tom,

The error I  got while compiling the code is
"Invalid Token :  valid token are: CURSOR "

globalnest:

Before anything else (and we have asked first), what version of i5/OS are you running? What is the DB2 group PTF level?

Tom

Hello Tom,

We are using a V5R4 and for the DB2 group PTF level is 15

globalnest:

The oldest DB2 level that I have available is 18, with a cume level of 8305.

I copy/pasted your last code snippet directly into a source member, added only a SETON LR statement, and compiled with no errors.

I can only guess that a more current fix level is needed for compiles.

Tom