Thread: JDBC (executing transactions coding style)

I want to execute multiple SQL commands (also insert,updates and selects
) in a transaction block.

Is the following coding style correct ?

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

> 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 ?

Someone else probably has up to date information. I recall that if an
error happens within a BEGIN/END block, all other commands are
rejected until an END (or ABORT?) is executed. The assumption is that
if something fails within a transaction you want to rollback
everything else within that transaction, and the transaction isn't
over until you explicitly complete it.

                    - Tom

--
Thomas Lockhart                lockhart@alumni.caltech.edu
South Pasadena, California

Constantin Teodorescu <teo@flex.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@shore.net <http://www.magiccookie.com/>
PGP public key available at <http://www.magiccookie.com/pgpkey.txt>