Message-Id: <v04003a00b33bb79caae3@[192.168.1.2]>
In-Reply-To: <3715C329.9739047D@flex.ro>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Date: Thu, 15 Apr 1999 11:45:31 -0400
To: PostgreSQL Interfaces <pgsql-interfaces@postgresql.org>
From: Ari Halberstadt <ari@shore.net>
Subject: Re: [INTERFACES] JDBC (executing transactions coding style)

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>