Solved

return 2 result sets from a stored proc

Posted on 2008-06-24
9
735 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:daveslash
  • 4
  • 4
9 Comments
 
LVL 45

Accepted Solution

by:
Kdo earned 250 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 250 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:daveslash
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
 
LVL 45

Expert Comment

by:Kdo
ID: 21869165

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

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Author Comment

by:daveslash
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 45

Expert Comment

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

0
 
LVL 18

Author Comment

by:daveslash
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 45

Expert Comment

by:Kdo
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:daveslash
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

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…
Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

708 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

20 Experts available now in Live!

Get 1:1 Help Now