El día miércoles, septiembre 30, 2020 a las 02:37:23p. m. -0400, Tom Lane escribió:
> Matthias Apitz <guru@xxxxxxxxxxx> 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@xxxxxxxxxxx, 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)
