Hi,
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
withthe 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.
Thanks and kind regards,
Sigrid