Re: Problem close curser after rollback - Mailing list pgsql-general

From Matthias Apitz
Subject Re: Problem close curser after rollback
Date
Msg-id 20200930171312.GA14156@r314251-amd64
Whole thread Raw
In response to Re: Problem close curser after rollback  (Laurenz Albe <laurenz.albe@cybertec.at>)
Responses Re: Problem close curser after rollback
List pgsql-general
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@unixarea.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)



pgsql-general by date:

Previous
From: Jack Douglas
Date:
Subject: Re: Yum repository RPM behind release
Next
From: Maria Elba Salerno
Date:
Subject: Procedure to install and configure pgadmin4 in desktop mode in Red Hat Linux 8 and other Linux distributions