pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedJDBC (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>
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 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
* Re: [INTERFACES] JDBC (executing transactions coding style)
@ 1999-04-15 14:07 Thomas Lockhart <lockhart@alumni.caltech.edu>
parent: Constantin Teodorescu <teo@flex.ro>
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 15:45 Ari Halberstadt <ari@shore.net>
parent: Constantin Teodorescu <teo@flex.ro>
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
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