sqloracletransactionsocci

Rolling back transaction with Oracle OCCI

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?

Solution

  • 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