Re: Problem close curser after rollback

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:
> > we use the following statements in our applications, as described on the site:
> >
> > https://www.postgresql.org/docs/11/ecpg-commands.html#ECPG-TRANSACTIONS
> >
> > EXEC SQL PREPARE stmt1 FROM "SELECT oid,datname FROM pg_database WHERE oid > ?";
> > EXEC SQL DECLARE foo_bar CURSOR 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 100;
> > ...
> > while (1)
> > {
> > EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname;
> > ...
> > }
> >
> > EXEC SQL CLOSE foo_bar;
> >
> > After every fetch we open a transaction which is terminated with a rollback in the event of an error.
> >
> > The problem we now have with this is, that the cursor is obviously closed with the rollback,
> > so the next fetch ends with the error that the cursor no longer exists (sqlcode -400
> >
> > What can I do so that the cursor is retained despite rollback?
> >
> > The procedure described is very often found in our applications, which have been ported from sybase to postgreSQL.
>
> You cannot start a transaction while you are reading a cursor; you probably
> get a warning "there is already a transaction in progress".

Sigrid was not fully correct saying 'After every fetch we open a transaction...'

Our layer between application and ESQL/C has a function call DB_strT()
to start a transaction. But this does nothing for PostgreSQL because in
PostgreSQL' ESQL/C there is no method "START TRANSACTION", see

https://www.postgresql.org/docs/11/ecpg-commands.html#ECPG-TRANSACTIONS

There are only COMMIT and ROLLBACK.

I think we will prepare the ten-liner in ESQL/C for further discussion.

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)