473,573 Members | 2,944 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Questions re WITH RETURN TO CLIENT

I am trying to verify that I correctly understand something I saw in the DB2
Information Center.

I am running DB2 Personal Edition V8.2.1 on Windows. I came across the
following in the Info Center:

To return a result set from a procedure to the originating application, use
the WITH RETURN TO CLIENT clause. When WITH RETURN TO CLIENT is specified on
a result set, no nested procedures can access the result set.

This excerpt can be found on the page entitled "Returning result sets from
SQL and embedded SQL procedures".

Based on some experiments I have done with Java client programs calling SQL
PL stored procedures, I *think* the excerpt means that a result set returned
by a procedure will only be visible to the client application if the cursor
was declared WITH RETURN TO CLIENT and if the procedure is directly called
by the client application. In other words, if the cursor was declared WITH
RETURN TO CLIENT and the procedure itself was NOT directly called by the
client, the client is not going to be able to access the result set.

I've done some experiments that tend to support this understanding but I
want to be sure I'm not missing some other factor that explains this
behaviour.

I wrote the following procedures in the Development Center:
a) an SQL PL procedure called RTRN_SET which declares and opens a cursor
WITH RETURN TO CLIENT
b) an SQL PL procedure called RECEIVE01 which calls RTRN_SET and handles its
result set
c) an SQL PL procedure called RECEIVE02 which calls RTRN_SET but does
nothing at all with its result set

I tried the following tests in the Development Center:
a) I executed procedure RTRN_SET in the Dev. Ctr. as a standalone procedure
it worked fine and displayed the result set correctly.
b) I executed procedure RECEIVE1 in the Dev. Ctr., it called RTRN_SET and
failed on SQL0423N as soon as it attempted to allocate a cursor for the
result set.
c) I executed procedure RECEIVE2 in the Dev. Ctr., it called RTRN_SET and
ended cleanly. The result set was also visible in the "Results" panel of the
Output View.

I wrote some Java applications to call the same stored procedures:
i) ClientRtrnSet calls procedure RTRN_SET directly and displays its result
set.
ii) ClientReceive1 calls procedure RECEIVE1 which in turn calls procedure
RTRN_SET.
iii) ClientReceive2 calls procedure RECEIVE2 which in turn calls procedure
RTRN_SET.

When I executed the Java applications, I got the following results:
i) ClientRtrnSet works fine and displays the result set obtained by
procedure RTRN_SET.
ii) ClientReceive1 gets SQL0423N when it executes procedure RECEIVE1.
iii) ClientReceive2 executes RECEIVE2 successfully but then gets a
NullPointerExce ption when it tries to access the result set obtained by
procedure RTRN_SET. The NullPointerExce ption indicates that the result set
either doesn't exist or cannot be accessed by the Java client application.

My analysis of the various tests is:
- (a) and (i) both worked fine, exactly as they should have.
- (b) and (ii) failed, exactly as they should have. Procedure RECEIVE1 tried
to access a result set from called stored procedure RTRN_SET that had
defined the result set as WITH RETURN TO CLIENT. Since RECEIVE1 is another
procedure, not a client program, it was not allowed to look at the result
set.
- I'm not sure what to make of (c). I'm surprised that the result set was
visible in the Results page when executing RECEIVE2 in the Dev. Ctr. Since
RECEIVE2 didn't do anything with the result set from RTRN_SET, I expected
the result set to be unavailable to RECEIVE2 in the Dev. Ctr.
- I'm not sure what to make of (iii). I thought that the result set might be
accessible to ClientReceive2 since procedure RECEIVE2 only called RTRN_SET
but didn't touch its result set. The NullPointerExce ption on the result set
suggests strongly that the result set is not accessible by the client.
That's why I think the excerpt I quoted at the top of the post means that a
client can only access a result set from a procedure whose cursor is defined
as WITH RETURN TO CLIENT *if the procedure is called **DIRECTLY** by the
client; in my case, RTRN_SET is NOT called directly by ClientReceive2, it is
called by procedure RECEIVE2 which in turn is called by ClientReceive2.
Therefore, the only legitimate way for a client application to get the
result set from RTRN_SET is to call it directly, never indirectly via an
intermediate procedure, assuming the result set is declared WITH RETURN TO
CLIENT.

Have I analyzed all of this correctly? If not, could someone tell me where
I've gone wrong?

Also, to take this all one step further, what exactly is the point behind
WITH RETURN TO CLIENT? Why not just use WITH RETURN TO CALLER in every case
and let the calling procedure always handle the result set(s) returned by
the called procedure?

By the way, I'd be happy to post the different programs and procedures I've
described in this post if it would help anyone understand the question or
answer it.

Rhino
---
rhino1 AT sympatico DOT ca
"There are two ways of constructing a software design. One way is to make it
so simple that there are obviously no deficiencies. And the other way is to
make it so complicated that there are no obvious deficiencies." - C.A.R.
Hoare
Nov 12 '05 #1
2 4656
I thought it had to do with intermediate routines seeing it.

That is

1) Client CALLs A1()
2) A1() CALLs A2()
3) A2 DECLAREs a CURSOR

With RETURN TO CALLER, A() would see the rows, not the client. With
RETURN TO CLIENT the client sees them.

CREATE PROCEDURE A1() BEGIN \
DECLARE List CURSOR \
WITH RETURN TO CALLER FOR VALUES(1); \
OPEN List; \
END

CREATE PROCEDURE A2() CALL A1()

CALL A1

Result set 1
--------------

1
-----------
1

1 record(s) selected.

Return Status = 0

CREATE PROCEDURE A1() BEGIN \
DECLARE List CURSOR \
WITH RETURN TO CALLER FOR VALUES(1); \
OPEN List; \
END

DROP PROCEDURE A2
CREATE PROCEDURE A2() CALL A1()
CALL A2

Return Status = 0

CALL A1
Result set 1
--------------

1
-----------
1

1 record(s) selected.

Return Status = 0

DROP PROCEDURE A2
DROP PROCEDURE A1
COMMIT

Note that the client only showed the RESULT SET when it was RETURN TO
CLIENT.

Nov 12 '05 #2

"Brian Tkatch" <Ma***********@ ThePentagon.com > wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.com...
I thought it had to do with intermediate routines seeing it.

That is

1) Client CALLs A1()
2) A1() CALLs A2()
3) A2 DECLAREs a CURSOR

With RETURN TO CALLER, A() would see the rows, not the client.
When you said 'A()' on the previous line, did you mean A1() or A2()?
With RETURN TO CLIENT the client sees them.

CREATE PROCEDURE A1() BEGIN \
DECLARE List CURSOR \
WITH RETURN TO CALLER FOR VALUES(1); \
OPEN List; \
END

CREATE PROCEDURE A2() CALL A1()

CALL A1

Result set 1
--------------

1
-----------
1

1 record(s) selected.

Return Status = 0

CREATE PROCEDURE A1() BEGIN \
DECLARE List CURSOR \
WITH RETURN TO CALLER FOR VALUES(1); \
OPEN List; \
END

DROP PROCEDURE A2
CREATE PROCEDURE A2() CALL A1()
CALL A2

Return Status = 0

CALL A1
Result set 1
--------------

1
-----------
1

1 record(s) selected.

Return Status = 0

DROP PROCEDURE A2
DROP PROCEDURE A1
COMMIT

Note that the client only showed the RESULT SET when it was RETURN TO
CLIENT.

Your example is very confusing; at the beginning, you state that proc A1()
calls proc A2() and A2() declares a cursor but, in your code, both versions
of A1 (!!) declare the cursor and A2 (!!) calls A1(), not the other way
around. Also, *both* of your definitions of A1 declare the cursor WITH
RETURN TO CALLER, never as WITH RETURN TO CLIENT.

In any case, I've reported my experience with both formulations - WITH
RETURN TO CLIENT and WITH RETURN TO CALLER - and can readily replicate it at
any time. Should I post the code so that you (or others) can do the same?

My client applications are written in Java; I don't know if you are familiar
with it or have a development environment for Java. My procs are written in
SQL PL.

I've summarized my experience with this as follows:

---
Given the following situation: a client program, Client1, calls a stored
procedure Proc1, which in turn calls another stored procedure Proc2 which
generates a result set.

If Proc2 defines its result set as WITH RETURN TO CLIENT, Client1 will be
able to call Proc1 but Proc1 will not be able to access the result set.
However, if Client1 calls Proc2 directly, without Proc1 in the middle, it
can access the result set.

If Proc2 defines its result set as WITH RETURN TO CALLER, Client1 will be
able to call Proc1 and Proc1 will be able to access the result set. Again,
if Client1 calls Proc2 directly, without Proc1 in the middle, it can access
the result set.

In short, defining the result set as WITH RETURN TO CLIENT seems to ensure
that the result set can only be accessed by having the client program
directly call the procedure which generates the result set, i.e. Client1
directly calls Proc2. Defining the result set as WITH RETURN TO CALLER
allows the immediate caller of the stored procedure which generates the
result to access the result set, even if the immediate caller is in turn
called by a higher-level procedure or a client, but prevents Client1 from
having any access to the result set, i.e. Proc1 can see the result from
Proc2 but Client1 can't see the result.

The net effect of all this is that you can nest procedures up to 16 levels
deep but you can only access a result set from one of those result sets:

a) at the level that called the result-set-generating proc, if the result
set is defined as WITH RETURN TO CALLER

b) at the client if the client *DIRECTLY* calls the result-set-generating
proc.

In effect, b) means that the result-set-generating proc needs to be at the
highest level of the nested procs if the client is going to be able to see
its result; in any other case, the client is not going to be able to see the
result set, presumably due to the intermediate levels of procs.

---

I am not certain that my analysis is correct though. Maybe I've made a
coding error, although I can't see it if I have. Perhaps I am not taking
some additional factor into account.

I was hoping that someone who has more experience with WITH RETURN TO CLIENT
and WITH RETURN TO CALLER can confirm that I am analyzing the behaviour of
my code correctly OR that they can tell me what is wrong with my
understanding of how this works.
Rhino
Nov 12 '05 #3

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

Similar topics

3
1690
by: Tony | last post by:
Hi, I hope I can find answers to my questions here. 1. How to execute a Private Sub in VB, from a Javascript function event. 2. How to get a JavaScript Var value from the html page and return to the VB Codebehind. 3. If there is a way to execute a JavaScript function before the VB Code behind page_load event.
2
1843
by: Matthew Hood | last post by:
My company has expressed a desire to convert an existing MS Access application to a full VB.NET application. My experience is with VB6 so I want to ask a few questions and get some input on the best way to handle the following design: Situation: We want to allow our customers to install with one of the following options: 1. Use an Access...
13
2636
by: coloradowebdev | last post by:
i am working on basically a proxy server that handles requests via remoting from clients and executes transactions against a third-party server via TCP. the remoting site works like a champ. my problem is executing the transactions against the remote server and returning the response to the remoting client. i can open the socket fine and,...
1
1099
by: Land | last post by:
Hi. I have some questions about WebService. Most time, we use a "identity" field as the primary key for the table in SQL databse, and that "identity" primary key can be migrated to child table as the foreign key. all those "identity" field will be filled by SQL system when we insert record. But for WebService, user input the information at...
2
2289
by: Jonny | last post by:
Hiya all Using: ASP.NET Version 1.1 I am learning ASP.NET & would like to know:- How do I add a Open Browser Dialog behind a server button & validate a chosen file as a new e-mail attachment? How do I close an aspx window (client-side)?
6
1537
by: xfile | last post by:
Hello, I am very new to donet and wondering how to solve the following scenario: (1) Our current hosted site has .Net 1.1 and can be upgraded to 2.0 if needed. Some downtime are expected and may have unexpected problems though. (2) Local environment installed both 1.1 and 2.0 framework and 2.0 SDK. (3) Currently, all our ASP and HTML...
4
6058
by: shamirza | last post by:
4 9 6 18.ATLAS-AJAX Note: - As an IT professional it's useful to know what the difference is between Hype and usefulness. For instance if there is a new technology coming in many programmers just want to implement it because they want to learn it?. But any new technology becomes useful if it is useful to the user. And Ajax is one of the...
5
1713
by: Soumen | last post by:
For one of my project, I want to use auto_ptr. But I'm facing few issues. 1. Say I've Controller class. I don't want to allow user to create object of this class unless all arguments are valid. So I've made the constructor private and given one static method to create an object of this class. Since one of senior members in the team doesn't...
8
2621
by: Frank Millman | last post by:
Hi all I have been using my own home-brewed client/server technique for a while, using socket and select. It seems to work ok. The server can handle multiple clients. It does this by creating a new thread for each connection. Each thread runs its own select loop. I am making some fairly big changes, so I thought I would look at asyncore....
0
7686
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8009
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8198
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7771
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6406
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5289
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3730
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2194
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1296
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.