• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 793
  • Last Modified:

return 2 result sets from a stored proc


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
Dave Ford
Asked:
Dave Ford
  • 4
  • 4
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
momi_sabagCommented:
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
 
Dave FordSoftware Developer / Database AdministratorAuthor Commented:

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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Kent OlsenData Warehouse Architect / DBACommented:

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

0
 
Dave FordSoftware Developer / Database AdministratorAuthor Commented:

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
 
Kent OlsenData Warehouse Architect / DBACommented:
I'll be in San Luis Obispo in August.  :)

0
 
Dave FordSoftware Developer / Database AdministratorAuthor Commented:

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
 
Kent OlsenData Warehouse Architect / DBACommented:

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

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


Kent
0
 
Dave FordSoftware Developer / Database AdministratorAuthor Commented:

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now