Solved

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

Posted on 2009-03-29
10
1,463 Views
Last Modified: 2013-12-06
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
Comment
Question by:globalnest
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 18

Expert Comment

by:daveslash
Comment Utility

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
 
LVL 1

Author Comment

by:globalnest
Comment Utility
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
 
LVL 18

Expert Comment

by:daveslash
Comment Utility

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
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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
 
LVL 1

Author Comment

by:globalnest
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Author Comment

by:globalnest
Comment Utility
Tom,

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

Expert Comment

by:tliotta
Comment Utility
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
 
LVL 1

Author Comment

by:globalnest
Comment Utility
Hello Tom,

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


0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction How to create multiboot configuration with XP\Vista and Windows 7 on it? And most important question - how to do this correctly so not to have any kind of nightmares we get when system gets screwed? First of all one should realize t…
Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now