Re: [INTERFACES] JDBC (executing transactions coding style)

Constantin Teodorescu <teo(at)flex(dot)ro> wrote:
>I want to execute multiple SQL commands (also insert,updates and selects
>) in a transaction block.
>
>Is the following coding style correct ?
>...

Use Connection methods: setAutoCommit, commit, and rollback(). So it would
look like this:

if (! con.getAutoCommit())
throw new SQLException("Already in a transaction");
try {
con.setAutoCommit(false);
st.executeUpdate("insert into ...");
...
con.commit();
} catch (Exception e) {
try {
con.rollback();
} catch (SQLException sqlex) {
// ignore
}
throw e;
} finally {
con.setAutoCommit(true);
}

I've actually defined a Transaction object that wraps all of this in an
object so I can't forget anything. Using it looks like this:

Transaction trans = new ExclusiveTransaction(conn);
try {
trans.begin();
statements...
trans.commit();
} finally {
trans.end();
}

This is a bit more concise, plus it rolls back if any exception is thrown
(including runtime exceptions). You can find these classes in my Magic
Cookie utilities download at
<http://www.magiccookie.com/computers/software.html>, or just browse the
javadoc or sources in package com.magiccookie.sql.transaction.* (follow the
javadoc or source links on the preceding url).

>Statement st;
>ResultSet rs;
>
>try {
> st.executeUpdate("BEGIN");
> st.executeUpdate("INSERT INTO ...");
> st.executeUpdate("DELETE FROM ...");
> rs = st.executeQuery("SELECT FROM ...");
> if (rs != null) {
> while ( rs.next() ) {
> // do different things
> }
> }
> rs.close();
> st.executeUpdate("UPDATE ...");
> st.executeUpdate("COMMIT TRANSACTION");
>} catch (SQLException sqle) {
> sqle.printStackTrace();
> // ABORT TRANSACTION NEEDED ?
>}
>
>What I want to know : is there necessary to do a st.executeUpdate("ABORT
>TRANSACTION") in the catch instruction block ?
>I recall that someone says that an error inside a transaction block
>automatically aborts the transaction.
>Is it true ? It works here ?
>
>For other databases it might be necessary to do that.
>Then, the st.executeUpdate("ABORT"); must be included also in another
>try..catch block, true ?
>
>Thanks a lot,
>--
>Constantin Teodorescu
>FLEX Consulting Braila, ROMANIA

-- Ari Halberstadt mailto:ari(at)shore(dot)net <http://www.magiccookie.com/>
PGP public key available at <http://www.magiccookie.com/pgpkey.txt>