Solved

return 2 result sets from a stored proc

Posted on 2008-06-24
9
738 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
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 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

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

Suggested Solutions

Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
I don't know if many of you have made the great mistake of using the Cisco Thin Client model with the management software VXC. If you have then you are probably more then familiar with the incredibly clunky interface, the numerous work arounds, and …
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

937 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

10 Experts available now in Live!

Get 1:1 Help Now