Error (sqlca.sqlcode == ECPG_DUPLICATE_KEY) Handling In Ecpg PostgreSQL

Question

Need to handle a error while inserting records into table (Ecpg PostgreSQL), but job should not abort/commit/rollback if any duplicate record (Primary Key).Job should skip and continue for next.

Note:SQL_CODE = sqlca.sqlcode

if ( SQL_CODE == -403 )  Other Way (sqlca.sqlcode == ECPG_DUPLICATE_KEY)
{
   Log_error_tab();
}   
else if ( SQL_CODE != SQL_SUCCESS )
{
   Job_fail();
}

If i will handle as above its handling the error by calling function Log_error_tab(),but its failing in next DML operation with error "sqlerrm.sqlerrmc: current transaction is aborted, commands ignored until end of transaction block on line (sqlstate: 25P02)"

score 0 · Accepted Answer · answered Sep 30 '22 at 16:07

That's the way PostgreSQL works: if a statement inside a transaction fails, the transaction is aborted, and all subsequent statements will fail with that message.

So you should EXEC SQL ROLLBACK before you attempt your next SQL statement.

If you don't want to rollback the whole transaction, you can set a savepoint prior to executing the “dangerous” SQL statement:

SAVEPOINT sname

Then, when the critical part is over, you can release the savepoint:

RELEASE SAVEPOINT sname

If you hit an error, you can roll back everything since the savepoint was set, including the error, with

ROLLBACK TO SAVEPOINT sname

Note that you should use savepoints sparingly if you want decent performance.

Error (sqlca.sqlcode == ECPG_DUPLICATE_KEY) Handling In Ecpg PostgreSQL

1 Answers1