Re: Cursor closed (probably by commit or rollback)

Hi, ISSUE RESOLVED.

"declare with hold" alone didn't answer my concerns, the cursor was still
closed after rollback or commit. Then, I spotted in the SQL0501 message
description that I also needed to declare my commit/rollback statements with

COMMIT HOLD
or
ROLLBACK HOLD

Thanks for your help.

--
Mahefa

"La razon es tediosa y aburrida...*utilizaremos la fuerza*"
(Los Pinguinos de Madagascar)





On Fri, Jan 7, 2011 at 5:58 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

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.

--
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.