cpostgresqlecpg

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

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)"

Solution

  • 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.