pg.ddx.io  pgsql-interfaces@postgresql.org mailing list archive  

help / color / mirror / Atom feed

JDBC (executing transactions coding style)
4+ messages / 4 participants
[nested] [flat]

* JDBC (executing transactions coding style)
@ 1999-04-15 10:44 Constantin Teodorescu <teo@flex.ro>
  1999-04-15 14:07 ` Re: [INTERFACES] JDBC (executing transactions coding style) Thomas Lockhart <lockhart@alumni.caltech.edu>
  1999-04-15 15:45 ` Re: [INTERFACES] JDBC (executing transactions coding style) Ari Halberstadt <ari@shore.net>
  0 siblings, 2 replies; 4+ messages in thread

From: Constantin Teodorescu @ 1999-04-15 10:44 UTC (permalink / raw)
  To: pgsql-interfaces

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [INTERFACES] JDBC (executing transactions coding style)
  1999-04-15 10:44 JDBC (executing transactions coding style) Constantin Teodorescu <teo@flex.ro>
@ 1999-04-15 14:07 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
  1 sibling, 0 replies; 4+ messages in thread

From: Thomas Lockhart @ 1999-04-15 14:07 UTC (permalink / raw)
  To: Constantin Teodorescu <teo@flex.ro>; +Cc: pgsql-interfaces

> 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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [INTERFACES] JDBC (executing transactions coding style)
  1999-04-15 10:44 JDBC (executing transactions coding style) Constantin Teodorescu <teo@flex.ro>
@ 1999-04-15 15:45 ` Ari Halberstadt <ari@shore.net>
  1 sibling, 0 replies; 4+ messages in thread

From: Ari Halberstadt @ 1999-04-15 15:45 UTC (permalink / raw)
  To: pgsql-interfaces

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;





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* RE: [INTERFACES] JDBC (executing transactions coding style)
@ 1999-04-15 13:49 Peter Mount <petermount@it.maidstone.gov.uk>
  0 siblings, 0 replies; 4+ messages in thread

From: Peter Mount @ 1999-04-15 13:49 UTC (permalink / raw)
  To: Constantin Teodorescu <teo@flex.ro>; pgsql-interfaces

Don't use BEGIN and COMMIT within JDBC. To do this, you use the
setAutoCommit() method in the Connection to enable transactions.

Although your code works at the moment, technically we should wrap all
statements with their own BEGIN ... COMMIT statements while AutoCommit
is true (which is the default).

As for the catch() block, the Connection interface has the method that
needs to be called to ABORT the transaction.

Peter

--
Peter T Mount, IT Section
petermount@it.maidstone.gov.uk
Anything I write here are my own views, and cannot be taken as the
official words of Maidstone Borough Council


-----Original Message-----
From: Constantin Teodorescu [mailto:teo@flex.ro]
Sent: Thursday, April 15, 1999 11:45 AM
To: PostgreSQL Interfaces
Subject: [INTERFACES] 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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~1999-04-15 15:45 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-15 10:44 JDBC (executing transactions coding style) Constantin Teodorescu <teo@flex.ro>
1999-04-15 14:07 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-04-15 15:45 ` Ari Halberstadt <ari@shore.net>
1999-04-15 13:49 RE: [INTERFACES] JDBC (executing transactions coding style) Peter Mount <petermount@it.maidstone.gov.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox