• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1747
  • Last Modified:

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

I want a create a procedure to generate 3 different resultsets and these resultset will be returned to a method.
Ihave tried to return multiple resultset ,(If these the program is debugged in green screen it shows all 3 resultset are populated)
However. if the Java procedure is debuged it shows only the first DS as resultset and remaining DS are not returned (Java gets a null if tries to get it)

PS: java method cstmt.getMoreResults() returns false for the resultsets after first.
0
globalnest
Asked:
globalnest
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Dave FordSoftware Developer / Database AdministratorCommented:

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: http://www.experts-exchange.com/Database/DB2/Q_23512641.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;

Open in new window

0
 
globalnestAuthor Commented:
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
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

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;
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
tliottaCommented:
globalnest:

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

He's saying to make this change:

c/exec sql                                                                
c+ set result sets ARRAY  :A FOR 2 ROWS ,                                  
c+                              ARRAY  :B FOR 2 ROWS ,                                  
c+                              ARRAY  :C FOR 2 ROWS                                    
c/end-exec                                                                
Report back results if additional help is needed.

Tom
0
 
globalnestAuthor Commented:
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

Open in new window

0
 
globalnestAuthor Commented:
Tom,

The error I  got while compiling the code is
"Invalid Token :  valid token are: CURSOR "
0
 
tliottaCommented:
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
0
 
globalnestAuthor Commented:
Hello Tom,

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


0
 
tliottaCommented:
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
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Rahul,

1) Only one ARRAY is supported in a SET RESULTS SET statement:

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/db2/rbafzmsthsetrs.htm

2) To call indirectly via JDBC and return multiple result sets, try specifying "SET RESULT SETS WITH RETURN TO CLIENT":
3) On your CREATE PROCEDURE statement for this external stored procedure, specify "DYNAMIC RESULT SETS 3" (or higher).  If fewer result sets are declared here than in the RPG, the smaller number will be returned.
4) Do not compile the RPG program with ACTGRP(*NEW).

http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/db2/rbafzmstcrtpef.htm

If you still can't return multiple result sets, please post your Java code and stored procedure DDL.

Regards,

- Gary Patterson
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now