Rollback to Savepoint with H2 Database

21 views Asked by At

I've got 2 updates to execute in a row. And I would like to revert both updates if the second one fails.

I thought that using SAVEPOINT and ROLLBACK TO SAVEPOINT would work but it doesn't. I got the following error:

org.h2.jdbc.JdbcSQLException: Savepoint is invalid: "BEFORECREATE"; SQL statement:
ROLLBACK TO SAVEPOINT beforeCreate [90063-196]

My code looks like this:

// 0) Create a Savepoint
try ( PreparedStatement prep0 = conn.prepareCall("SAVEPOINT beforeCreate")) {
    prep0.execute();
}

// 2) First Update
try ( PreparedStatement prep1 = conn.prepareCall(stmtUpdate)) {
    prep.setString(1, label);
    prep.executeUpdate();
} 

// 2) Second Update;

try ( PreparedStatement prep = conn.prepareCall(stmtInsert)) {
    prep.setString(1, label);
    prep.setLong(2, parentId);
    prep.executeUpdate();
} catch (SQLException ex) {

    // In case of error, do the rollback to the savepoint
    try ( PreparedStatement prep0 = conn.prepareCall("ROLLBACK TO SAVEPOINT beforeCreate")) {
        prep0.execute();
    } // <-- this is creating the error

    throw ex;
}

Do you I define correctly the Savepoint ? And do I use the Rollback correctly ? Are the Savepoint only valid/existing at some stage of the execution ?

PS: I'm using the version 1.4.196

0

There are 0 answers