Oppolzer - Informatik / Blog

C.,

I don't quite understand your comment.

I know that there are Stored Procs returning result sets, and result sets are in
fact open cursors, and the caller of the stored proc can fetch the results from
the result set using special SQL statements like ASSOCIATE LOCATOR etc. after
the Stored Proc CALL, so the connection has to stay active for a longer time
than the CALL.

I never tested that, so I don't know too much details, and I don't know if it
works with cursors WITH HOLD and what other consequences this technique has. I
thought, it would be valid to point the OP in this direction: if the Stored Proc
has result sets, the connection has to be kept beyond the time of the CALL,
because the client works on the result sets, and CLOSE CURSOR in the SP does
make no sense, IMHO.

Kind regards

Bernd



Am 09.02.2015 um 02:38 schrieb C.B.:
>
> ("Stored Procs sometimes intentionally leave Cursors")
>
> Bernd,
>
>
>
> This business of Stored procedure would intentionally or unintentionally
> close a WITH HOLD CURSOR.
>
> I bet you don't believe any that "Bovine Scatology" Do you?  Please say no!
>
>
>
> Thanks
>
> C.
>
>
>
>
>
> From: Bernd Oppolzer [mailto:db2-l@lists.idug.org]
> Sent: Sunday, February 08, 2015 12:04 PM
> To: db2-l@lists.idug.org
> Subject: [DB2-L] - RE: releasing CURSOR WITH HOLD
>
>
>
> AFAIK, Stored Procs sometimes intentionally leave Cursors
> open, so that the callers at the client side can fetch the results.
> If your Stored Proc does this intentionally, CLOSE CURSOR is
> no option, because the client will not be able to fetch the results
> after that. I think, in this case you depend on the COMMIT that
> is issued by the client (or a DB2 timeout).
>
> Kind regards
>
> Bernd
>