Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

return 2 result sets from a stored proc

Posted on 2008-06-24
9
Medium Priority
?
762 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Amazon Web Services EC2 Cheat Sheet

AWS EC2 is a core part of AWS’s cloud platform, allowing users to spin up virtual machines for a variety of tasks; however, EC2’s offerings can be overwhelming. Learn the basics with our new AWS cheat sheet – this time on EC2!

 
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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 …
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.

721 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