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, ✉ g...@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)
