Use of COMMIT TRANSACTION

[Nils Tolleshaug]

When I do a series of delete operation in a H2 database as. ex remove a person recording in all related database tables, I think I can do the following:

1. SET AUTOCOMMIT FALSE
2. Beginning of delete person use PREPARE COMMIT deletePerson
3. If delete operation return normally without Exception, use COMMIT TRANSACTION deletePerson
4. If I experience an Exception, use ROLLBACK TRANSACTION deletePerson
5. In both cases the close and reopen database ?

Will this work and is it really necessary to close and reopen database?

Nils

[Nils Tolleshaug]

If I run this in H2 console:

SET AUTOCOMMIT FALSE;
PREPARE COMMIT PERS_DEL;
COMMIT TRANSACTION PERS_DEL;

the commands works, however:

Connection dataH2conn;
connectString = "jdbc:h2:" + urlH2loc + ";IFEXISTS=TRUE";
dataH2conn =  DriverManager.getConnection(connectString, userId, passWord);
Statement stmt = dataH2conn.createStatement();
stmt.executeUpdate("PREPARE COMMIT PERS_DEL;");

fails with:

Syntax error in SQL statement "PREPARE TRANSACTION [*]PERS_DEL;"; expected "(, AS"; SQL statement:

Nils

[Evgenij Ryazanov]

Hello!

These commands aren't going to work.

You need to disable auto-commit, execute your commands and commit or rollback your transaction.
In JDBC, you can use Connection.setAutoCommit(false), Connection.commit(), and Connection.rollback().
In SQL you can use SET AUTOCOMMIT OFF, COMMIT, and ROLLBACK.
After commit or rollback a new transaction will be started automatically.

Don't call PREPARE COMMIT name, COMMIT TRANSACTION name, ROLLBACK TRANSACTION name etc, these commands are used by more expensive two-phase commit protocol. Most likely you don't need it here. You can use it, of course, but in that case PREPARE COMMIT must be executed after data modification commands.

[Nils Tolleshaug]

Thanks! 

Much better solution and seems like COMMIT and ROLLBACK works as expected.

Nils