| Author |
Message
|
| Partha.Baidya |
 Posted: Tue Aug 07, 2018 8:37 am Post subject: Returning multiple resultset from DB2 Stored Procedure |
 |
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
We are trying to invoke a DB2 z/OS stored procedure(SP) using ESQL. The SP will return multiple result set.
I have followed in documentation in IIB v10 knowledge center.
https://www.ibm.com/support/knowledgecenter/en/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ac17040_.htm
This documentation gives example of 2 result sets but I do not find any information on ESQL coding if there are more than 2 result sets.
Is it possible to retrieve multiple resultsets using IIB/ESQL or only 2 at this moment? |
|
| Back to top |
|
 |
| abhi_thri |
| Posted: Tue Aug 07, 2018 11:20 pm Post subject: |
|
|
Knight
Joined: 17 Jul 2017
Posts: 516
Location: UK
|
hi...as the 'The following restrictions apply to the use of stored procedures' section at that infocenter link doesn't call out any such limits I guess it should work fine.
Have you tried invoking it? Are you getting any errors? If you have access to any DB you could try creating a dummy stored proc and invoke it. |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Aug 08, 2018 1:49 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
You can return any number of result sets.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| Partha.Baidya |
| Posted: Wed Aug 08, 2018 7:33 am Post subject: |
|
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
| Quote: |
| Have you tried invoking it? Are you getting any errors? If you have access to any DB you could try creating a dummy stored proc and invoke it. |
I have tried for 3 resultset using the following syntax.
call statement
| Code: |
| CALL DBSP224 (claimnum, sqlreturncode, Environment.ResultSet1[], Environment.ResultSet2[], Environment.ResultSet3[]) IN Database.DBS4; |
SP Delcaration
| Code: |
CREATE PROCEDURE DBSP224 (IN CLAIM_NUM INT, OUT SQL_CODE INT)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 3
EXTERNAL NAME "DBSP224"; |
As you can see I need to provide no of result sets returning by SP in the ESQL SP declaration as well as providing the result set array while calling the SP.
I tried to put 'N' in order to receive any no of result sets, but ESQL syntax check throws an error if I mention N over there.
| Code: |
CREATE PROCEDURE NFOSP224 (IN CLAIM_NUM INT, OUT SQL_CODE INT)
LANGUAGE DATABASE
DYNAMIC RESULT SETS [b]N[/b]
EXTERNAL NAME "DBSP224"; |
I could not find the syntax for accepting 'N' no of result sets in ESQL library.
Could anybody has already done this? Or where can I find the correct ESQL syntax of receiving multiple result sets out of DB stored proc? |
|
| Back to top |
|
|
| mpong |
| Posted: Wed Aug 08, 2018 11:54 am Post subject: |
|
|
Disciple
Joined: 22 Jan 2010
Posts: 164
|
|
| Back to top |
|
|
| Partha.Baidya |
| Posted: Wed Aug 08, 2018 2:26 pm Post subject: |
|
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
| Quote: |
| look at Database routine example 7. May be helpful |
I have checked this example and able to fetch 3 to 5 result sets, it works fine.
But I want to know the syntax where there will multiple no of result sets which I do not know during code development. |
|
| Back to top |
|
|
| timber |
| Posted: Thu Aug 09, 2018 1:51 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
You are not asking about returning multiple result sets. You are asking about how to return an unknown number of result sets. The IIB knowledge center is 100% clear:
https://www.ibm.com/support/knowledgecenter/en/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ak04970_.htm:
| Quote: |
| The DYNAMIC RESULT SETS clause is allowed only for database routines. It is required only if a stored procedure returns one or more result sets. The integer parameter to this clause must be 0 (zero) or more, and specifies the number of result sets to be returned." |
The PASSTHRU statement might be one way around this restriction. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Aug 09, 2018 4:28 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| The PASSTHRU statement might be one way around this restriction. |
PASSTHRU will not help here as this is restricted to only returning one result set.
However, as long as you know the upper limit to the number of result sets being returned you should be able to manage. Just set the DYNAMIC RESULT SETS to the maximum to be returned (e.g. 12) and provide the same number of result set destinations on the CALL. Now as long as the procedure returns <=12 result sets (in this example) you will be OK - the CALL statement will automatically set unused results set parameters to empty - this is expected behaviour. For example if (as in this example), you returned 8 result sets you would have that last 4 result locations on the call set to empty results. If you need to know how many result sets were actually returned, you can pass this back from the procedure as a RETURN value or as an OUT or INOUT parameter.
I hope this helps.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.
Last edited by mgk on Fri Aug 10, 2018 2:22 am; edited 2 times in total |
|
| Back to top |
|
|
| Partha.Baidya |
| Posted: Thu Aug 09, 2018 1:47 pm Post subject: |
|
|
Voyager
Joined: 05 Nov 2009
Posts: 97
|
Thanks mgk, I think the solution which you proposed is going to work.
| Quote: |
You are not asking about returning multiple result sets. You are asking about how to return an unknown number of result sets. The IIB knowledge center is 100% clear: |
Hi timber, you are correct I want to know how to return unknown number of result sets.
Is there any coding option in ESQL to achieve returning unknown number of result sets?
I think the coding example given in infocentre not going to support this. |
|
| Back to top |
|
|
| timber |
| Posted: Fri Aug 10, 2018 1:50 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
| Quote: |
| Is there any coding option in ESQL to achieve returning unknown number of result sets? |
No. The approach outlined by mgk is the only option. |
|
| Back to top |
|
|
|
|
