I have code similar to the simple example below that is trying to rollback a database write when an error occurs. However, half of the data seems to be left in the database rather than being deleted/rolled back.
statement->setAutoCommit(false);
statement->setMaxIterations(3);
int counter = 1;
try
{
statement->setInt(1, 1);
statement->addIteration();
statement->setInt(1, 2);
statement->addIteration();
statement->setInt(1, 3);
statement->executeUpdate();
statement->setInt(1, 4);
statement->addIteration();
statement->setInt(1, 2); // ERROR HERE (Unique constraint)
statement->addIteration();
statement->setInt(1, 6);
statement->executeUpdate();
connection->commit();
}
catch (oracle::occi::SQLException ex)
{
connection->rollback();
connection->terminateStatement(statement);
throw DatabaseException(ex.what());
}
If I get an Oracle error thrown then I want to roll back the current transaction so that NO rows were written. However this doesn't seem to be working properly.
I have a write that fails half way through, and it doesn't successfully roll back the rows. I end up with half of the data written out to the database.
Am I missing something with the setAutoCommit(false)
and connection->rollback()
command?
This could be a side-effect of batch error processing you should try and call setBatchErrorMode( false ) if you want an exception to be raised on error. Otherwise you can also use the batch-error mode and check the collected errors and decide if you want to commit or rollback