[h2] Re: Use of COMMIT TRANSACTION
Thanks! Much better solution and seems like COMMIT and ROLLBACK works as expected. Nils fredag 15. september 2023 kl. 09:54:43 UTC+2 skrev 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. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/954b0be2-abad-43db-8e27-7bf2e4ce7921n%40googlegroups.com.
[h2] Re: Use of COMMIT TRANSACTION
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. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/4821816e-3cb1-43a5-be08-7b8b6efb7d8bn%40googlegroups.com.
[h2] Re: Use of COMMIT TRANSACTION
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
fredag 15. september 2023 kl. 09:11:07 UTC+2 skrev 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
>
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to h2-database+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/bbecc681-7102-4285-a0ec-28041380d651n%40googlegroups.com.
