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