Re: Problem close curser after rollback

El día miércoles, septiembre 30, 2020 a las 02:37:23p. m. -0400, Tom Lane escribió:

> Matthias Apitz <guru(at)unixarea(dot)de> writes:
> > El día miércoles, septiembre 30, 2020 a las 05:26:39p. m. +0200, Laurenz Albe escribió:
> >> On Wed, 2020-09-30 at 13:32 +0000, Wiltsch,Sigrid wrote:
> >>> What can I do so that the cursor is retained despite rollback?
>
> >> You cannot start a transaction while you are reading a cursor; you probably
> >> get a warning "there is already a transaction in progress".
>
> > I think we will prepare the ten-liner in ESQL/C for further discussion.
>
> I don't think you really need to: the point seems clear enough.

I did wrote the ten-liner to play around with. Interestingly, there exists an
undocumented ESQL/C statement 'EXEC SQL START TRANSACTION' which gives
in the esqlc log:

ECPGtrans on line 48: action "start transaction"; connection "sisis"

What as well does work is the following sequence:

EXEC SQL PREPARE stmt1 FROM "SELECT tstchar25, tstint FROM dbctest WHERE tstint > ?";
EXEC SQL DECLARE foo_bar CURSOR WITH HOLD FOR stmt1 ;

/* when end of result set reached, break out of while loop */
EXEC SQL WHENEVER NOT FOUND DO BREAK;

EXEC SQL OPEN foo_bar USING 1;

while (1)
{
EXEC SQL FETCH NEXT FROM foo_bar INTO :tstchar25, :tstint;

EXEC SQL COMMIT ;

EXEC SQL START TRANSACTION ;
printf("fetched: [%s] [%d] \n", tstchar25, tstint);

// ... do something with the fetched data and because
// it went wrong, we issue a ROLLBACK

EXEC SQL ROLLBACK ;
}

This fetches nicely through the table in the while-loop; without the
additional COMMIT, the START TRANSACTION gives

ECPGtrans on line 48: action "start transaction"; connection "sisis"
ECPGnoticeReceiver: there is already a transaction in progress

We will think now about what we have learned and how to repair our
application.

matthias

--
Matthias Apitz, ✉ guru(at)unixarea(dot)de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Без книги нет знания, без знания нет коммунизма (Влaдимир Ильич Ленин)
Without books no knowledge - without knowledge no communism (Vladimir Ilyich Lenin)
Sin libros no hay saber - sin saber no hay comunismo. (Vladimir Ilich Lenin)