473,383 Members | 1,733 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,383 software developers and data experts.

Returning multiple result sets

I have a need to return multiple result sets from a stored procedure.
Want that SP to call others to get the data.

Win2003, db2 8.1.5.

Can't figure out how to handle open cursors, and return >1 result
sets.

Thought about global temp tables.

Some of the data may be quite long, eg 50 LOBs from one join. Some
quite small.

Suggestions?
Nov 12 '05 #1
5 8650

"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google.c om...
I have a need to return multiple result sets from a stored procedure.
Want that SP to call others to get the data.

Win2003, db2 8.1.5.

Can't figure out how to handle open cursors, and return >1 result
sets.

Thought about global temp tables.

Some of the data may be quite long, eg 50 LOBs from one join. Some
quite small.

Suggestions?


You haven't said what language you want to use for your stored procedures so
I don't know if this suggestion is appropriate for you.

I use Java stored procedures and it is just as easy to generate 5 result
sets as 1. Each result set is created the same way. The client program that
calls the stored procedure uses getResultSet() to obtain each result set in
turn. Your stored procs will need to leave each result set open: the result
set must still be open when the client program goes to read it; the client
can close the result set when it is finished with it. You shouldn't need
global temporary tables at all.

I am using DB2 V7.2 and it has a limitation that Java stored procedures
can't call other Java stored procedures. However, I believe that this
limitation is gone in DB2 V8 so that you should have no problem calling one
stored procedure and then have it call other stored procedures.

I'm not aware of any limitations in the size of result sets in either
version of DB2 but you may want to check the docs just to be sure. There
*is* a limit of 90 parameters for each stored proc but I'm not aware of any
limit on the size of the columns that each parameter can represent.

If you are not using Java stored procs, you'll need to research this issue
on your own; I haven't done enough non-Java procs to tell you how they
behave.

Rhino
Nov 12 '05 #2
Thanks Rhino (??? Nose ???)

I am, in fact, writing in DB2 SQL procedural language. I'll see if
there are equivalents to what you have written.

In the meantime, I'd much appreciate responses from others who have
encountered the same problem,

SS

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<tZ********************@news20.bellglobal.com >...
"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google.c om...
I have a need to return multiple result sets from a stored procedure.
Want that SP to call others to get the data.

Win2003, db2 8.1.5.

Can't figure out how to handle open cursors, and return >1 result
sets.

Thought about global temp tables.

Some of the data may be quite long, eg 50 LOBs from one join. Some
quite small.

Suggestions?


You haven't said what language you want to use for your stored procedures so
I don't know if this suggestion is appropriate for you.

I use Java stored procedures and it is just as easy to generate 5 result
sets as 1. Each result set is created the same way. The client program that
calls the stored procedure uses getResultSet() to obtain each result set in
turn. Your stored procs will need to leave each result set open: the result
set must still be open when the client program goes to read it; the client
can close the result set when it is finished with it. You shouldn't need
global temporary tables at all.

I am using DB2 V7.2 and it has a limitation that Java stored procedures
can't call other Java stored procedures. However, I believe that this
limitation is gone in DB2 V8 so that you should have no problem calling one
stored procedure and then have it call other stored procedures.

I'm not aware of any limitations in the size of result sets in either
version of DB2 but you may want to check the docs just to be sure. There
*is* a limit of 90 parameters for each stored proc but I'm not aware of any
limit on the size of the columns that each parameter can represent.

If you are not using Java stored procs, you'll need to research this issue
on your own; I haven't done enough non-Java procs to tell you how they
behave.

Rhino

Nov 12 '05 #3
I forgot to ask if anyone has experience with using more than one
connection to return result sets faster in VB6 on XP with Win 2003
server?

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<tZ********************@news20.bellglobal.com >...
"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google.c om...
I have a need to return multiple result sets from a stored procedure.
Want that SP to call others to get the data.

Win2003, db2 8.1.5.

Can't figure out how to handle open cursors, and return >1 result
sets.

Thought about global temp tables.

Some of the data may be quite long, eg 50 LOBs from one join. Some
quite small.

Suggestions?


You haven't said what language you want to use for your stored procedures so
I don't know if this suggestion is appropriate for you.

I use Java stored procedures and it is just as easy to generate 5 result
sets as 1. Each result set is created the same way. The client program that
calls the stored procedure uses getResultSet() to obtain each result set in
turn. Your stored procs will need to leave each result set open: the result
set must still be open when the client program goes to read it; the client
can close the result set when it is finished with it. You shouldn't need
global temporary tables at all.

I am using DB2 V7.2 and it has a limitation that Java stored procedures
can't call other Java stored procedures. However, I believe that this
limitation is gone in DB2 V8 so that you should have no problem calling one
stored procedure and then have it call other stored procedures.

I'm not aware of any limitations in the size of result sets in either
version of DB2 but you may want to check the docs just to be sure. There
*is* a limit of 90 parameters for each stored proc but I'm not aware of any
limit on the size of the columns that each parameter can represent.

If you are not using Java stored procs, you'll need to research this issue
on your own; I haven't done enough non-Java procs to tell you how they
behave.

Rhino

Nov 12 '05 #4
Stanley Sinclair wrote:
Thanks Rhino (??? Nose ???)

I am, in fact, writing in DB2 SQL procedural language. I'll see if
there are equivalents to what you have written.

In the meantime, I'd much appreciate responses from others who have
encountered the same problem,

SS

"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<tZ********************@news20.bellglobal.com >...
"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google .com...
I have a need to return multiple result sets from a stored procedure.
Want that SP to call others to get the data.

Win2003, db2 8.1.5.

Can't figure out how to handle open cursors, and return >1 result
sets.

Thought about global temp tables.

Some of the data may be quite long, eg 50 LOBs from one join. Some
quite small.

Suggestions?


You haven't said what language you want to use for your stored procedures so
I don't know if this suggestion is appropriate for you.

I use Java stored procedures and it is just as easy to generate 5 result
sets as 1. Each result set is created the same way. The client program that
calls the stored procedure uses getResultSet() to obtain each result set in
turn. Your stored procs will need to leave each result set open: the result
set must still be open when the client program goes to read it; the client
can close the result set when it is finished with it. You shouldn't need
global temporary tables at all.

I am using DB2 V7.2 and it has a limitation that Java stored procedures
can't call other Java stored procedures. However, I believe that this
limitation is gone in DB2 V8 so that you should have no problem calling one
stored procedure and then have it call other stored procedures.

I'm not aware of any limitations in the size of result sets in either
version of DB2 but you may want to check the docs just to be sure. There
*is* a limit of 90 parameters for each stored proc but I'm not aware of any
limit on the size of the columns that each parameter can represent.

If you are not using Java stored procs, you'll need to research this issue
on your own; I haven't done enough non-Java procs to tell you how they
behave.

Rhino


Check SQLLIB\samples\sqlproc\rsultset.db2 for a sample of an SQL
procedure that returns two result sets.

Dan
Nov 12 '05 #5
Thanks, Rhino:

I'm using v8.1.4 in Win 32, programming in VisualBasic 6 with latest update.

I will try to follow through on your example.

But, if someone knows how to do this in SQL procedural language, I'd apprecieate.

SS
"Rhino" <rh****@NOSPAM.sympatico.ca> wrote in message news:<tZ********************@news20.bellglobal.com >...
"Stanley Sinclair" <st*************@bellsouth.net> wrote in message
news:6f**************************@posting.google.c om...
I have a need to return multiple result sets from a stored procedure.
Want that SP to call others to get the data.

Win2003, db2 8.1.5.

Can't figure out how to handle open cursors, and return >1 result
sets.

Thought about global temp tables.

Some of the data may be quite long, eg 50 LOBs from one join. Some
quite small.

Suggestions?


You haven't said what language you want to use for your stored procedures so
I don't know if this suggestion is appropriate for you.

I use Java stored procedures and it is just as easy to generate 5 result
sets as 1. Each result set is created the same way. The client program that
calls the stored procedure uses getResultSet() to obtain each result set in
turn. Your stored procs will need to leave each result set open: the result
set must still be open when the client program goes to read it; the client
can close the result set when it is finished with it. You shouldn't need
global temporary tables at all.

I am using DB2 V7.2 and it has a limitation that Java stored procedures
can't call other Java stored procedures. However, I believe that this
limitation is gone in DB2 V8 so that you should have no problem calling one
stored procedure and then have it call other stored procedures.

I'm not aware of any limitations in the size of result sets in either
version of DB2 but you may want to check the docs just to be sure. There
*is* a limit of 90 parameters for each stored proc but I'm not aware of any
limit on the size of the columns that each parameter can represent.

If you are not using Java stored procs, you'll need to research this issue
on your own; I haven't done enough non-Java procs to tell you how they
behave.

Rhino

Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Darko Jovisic | last post by:
Hi! Another silly question: If a stored procedure returns multiple result sets, how do I choose the one I want to insert into a table? For example sp_spaceused returns two result sets if...
17
by: Roland Hall | last post by:
Is there a way to return multiple values from a function without using an array? Would a dictionary object work better? -- Roland Hall /* This information is distributed in the hope that it...
4
by: randy.p.ho | last post by:
Using JDBC, is there a way to call a stored procedure with multiple return values? Thanks.
1
by: Todd Peterson | last post by:
I'm a newbie to DB2 and am trying to figure out how to write a stored procedure, using dynamic SQL statements to return a result set. I believe the majority of the hurdles I have been facing might...
15
by: Daniel Rudy | last post by:
Hello, Consider the following code: /* resolve_hostname this resolves the hostname into an ip address. */ static void resolve_hostname(char result, const char hostname, const char server) {
1
by: randall g | last post by:
I have a stored procedure which returns multiple result sets, enclosing each in its own tag. This works in ADO but not ADO.NET, where an error is returned by ExecuteXmlReader: "Invalid command...
10
by: Henk van Lingen | last post by:
Hi, docs say (19.2.1): When trust authentication is specified, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database as whatever database user he...
13
by: Karl Groves | last post by:
I'm missing something very obvious, but it is getting late and I've stared at it too long. TIA for responses I am writing a basic function (listed at the bottom of this post) that returns...
10
by: =?Utf-8?B?UGV0ZXI=?= | last post by:
I'm using this coding to get 2 resultsets thru datareader and then load them into 2 datatables and bind the datatables to datagridviews. But sdrGrid.NextResult() is returning false for some...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.