Questions re WITH RETURN TO CLIENT

[Rhino]

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
NullPointerException when it tries to access the result set obtained by
procedure RTRN_SET. The NullPointerException 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 NullPointerException 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

[Brian Tkatch]

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.

[Rhino]

"Brian Tkatch" <Ma***********@ThePentagon.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.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