Solved

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

Posted on 2009-03-29
10
1,490 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
ID: 24020321

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
ID: 24030042
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
ID: 24030177

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
ID: 24032221
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
ID: 24051733
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:globalnest
ID: 24051763
Tom,

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

Expert Comment

by:tliotta
ID: 24053823
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
ID: 24061410
Hello Tom,

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


0
 
LVL 27

Expert Comment

by:tliotta
ID: 24063065
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
ID: 24094153
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many people tend to confuse the function of a virus with the one of adware, this misunderstanding of the basic of what each software is and how it operates causes users and organizations to take the wrong security measures that would protect them ag…
In this article we will discuss all things related to StageFright bug, the most vulnerable bug of android devices.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 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

24 Experts available now in Live!

Get 1:1 Help Now