Re: Cursor closed (probably by commit or rollback)

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Try declaring your cursor WITH HOLD, from the manual:

-WITHOUT HOLD or WITH HOLD-
Specifies whether the cursor should be prevented from being closed as
a consequence of a commit operation.
--WITHOUT HOLD--
Does not prevent the cursor from being closed as a consequence of a
commit operation. This is the default.
--WITH HOLD--
Prevents the cursor from being closed as a consequence of a commit
operation. A cursor declared using the WITH HOLD clause is implicitly
closed at commit time only if the connection associated with the
cursor is ended during the commit operation.

When WITH HOLD is specified, a commit operation commits all the
changes in the current unit of work, and releases all locks except
those that are required to maintain the cursor position. Afterwards, a
FETCH statement is required before a Positioned UPDATE or DELETE
statement can be executed.

All cursors are implicitly closed by a CONNECT (Type 1) or rollback
operation. All cursors associated with a connection are implicitly
closed by a disconnect of the connection. A cursor is also implicitly
closed by a commit operation if WITH HOLD is not specified, or if the
connection associated with the cursor is in the release-pending state.

If a cursor is closed before the commit operation, the effect is the
same as if the cursor was declared without the WITH HOLD option.


HTH,
Charles


On Fri, Jan 7, 2011 at 9:35 AM, mahefa randimbisoa
<mahefa.randimbisoa@xxxxxxxxx> wrote:
> Hi,
>
> I've got a SQLRPG (OPM) program that do the following :
>
> declare cursor
> open cursor
> //loop
> fetch
> call pgm2 (under some circumstances)
> //endloop
> close cursor
>
> After each time pgm2 is executed, my cursor in the calling program is
> closed, and I get SQL0501 in my fetch.
> Note that pgm2 ends with either COMMIT or ROLLBACK
>
> Is there any chance I get my cursor in the main program not closed by the
> call of pgm2?
>
> None of my programs are in ILE.
>
> Thanks in advance.
>
> --
> Mahefa
>
> "La razon es tediosa y aburrida...*utilizaremos la fuerza*"
> (Los Pinguinos de Madagascar)
> --
> This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.