Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

return 2 result sets from a stored proc

Posted on 2008-06-24
9
Medium Priority
?
771 Views
Last Modified: 2013-12-06

Greetings, experts!

I have a stored procedure that is supposed to return two result sets, but only one result set is coming back. I verified that both queries return valid data, but (for some reason) only the last result set is returned.

The procedure is actually a C-program (an "external" procedure) with embedded SQL.

Please take a look, and let me know what I'm doing wrong. I'm sure the answer is blindingly obvious, but I've been looking at this for so long that I've lost all objectivity.

Thank you very much!
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; 
EXEC SQL SET RESULT SETS CURSOR resultset2;

Open in new window

0
Comment
Question by:Dave Ford
  • 4
  • 4
9 Comments
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 1000 total points
ID: 21862174
Hi Dave,

Odd seeing you on the asking side of a question here.   :)

I've never done this and won't have my DB2 system nearby until the morning.  In the meantime, I suspect that the cursor will need to be declared WITH HOLD.

Also, there's supposed to be an example of returning multiple result sets in the release materials.  If you have the DB2 client installed, check out this file:

  SQLLIB\samples\sqlproc\rsultset.db2


Good Luck,
Kent
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 1000 total points
ID: 21862628
what happens if you reverse the order of your operations ?
do you get the other result set back to the calling program, or do you get the same one?

have you tried to change "result sets 2" to "dynamic result sets 2" ?
how many results sets should be returned according to the catalog table sysroutines?

have you tried defining the cursors - "WITH RETURN TO CALLER" ?
are you sure you receive the results set correctly in the calling routine?
0
 
LVL 18

Author Comment

by:Dave Ford
ID: 21867958

OK, go ahead and say it.  I deserve it.

" READ THE STINKIN' MANUAL, DAVE !! "

So, I broke out the "DB2 for iSeries - SQL Reference" manual, and it gave me the answer:

Combine the two SET RESTULT SETS statements into one.

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

That one works beautifully.

Thanks for your guidance, gentlemen.
DaveSlash
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 21869165

Since this is just between, "us guys", the payoff should be in beer.   :)

0
 
LVL 18

Author Comment

by:Dave Ford
ID: 21869502

I'm all for that!  

I've been homebrewing my own beer for 11 years, so if you ever make it to Southern California, I'll have a six-pack ready for you!  :-)

-- DaveSlash
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 21869840
I'll be in San Luis Obispo in August.  :)

0
 
LVL 18

Author Comment

by:Dave Ford
ID: 21869917

If you can make the 200-mile drive down to the L.A. area, I'll buy you dinner AND give you the 6-pack.  

-- DaveSlash
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 21870114

Geez -- what is there to do in LA?  :)

Let me check with my wife.  She'll be accompanying me.


Kent
0
 
LVL 18

Author Comment

by:Dave Ford
ID: 21911703

Whoops. I almost forgot to close this question.

Have some points, guys.  Your input and questions guided me to where I found the answer.

-- DaveSlash
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Introduction Often we come across situations wherein our batch files would be needing to reboot Windows for a variety of reasons. A few of them would be like: (1) Setup files have been updated whose changes can take effect only after a reboot …
I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
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.

885 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