Postgres Pro
Downloads
Home   >   mailing lists

Thread: JDBC and transactions

JDBC and transactions

From
"Ryan Chambers"
Date:
Hi

I'm trying to figure out how to use transactions with JDBC and PostGresql,
without much success. In particular, I can't figure out how to mark the
start of a transaction and how to do a rollback.

I'm iterating through a list of objects and I insert them into the DB one by
one. However, it is possible to get a NoSuchElement runtime exception while
going through the list. If this happens, I want to rollback all the previous
inserts.

The code/pseudo-code looks like this.

Connection conn = // get the connection
conn.setAutoCommit(false);

boolean rollBack = false;

while(myList.hasNext()) {
    MyObject obj = null;
    try {
        obj = (MyObject)myList.next();
    }
    catch(NoSuchElementException e) {
        rollBack = true;
        break;
    }

    try {
        persistToDB(conn, obj);
    }
    catch(SQLException e) {
        rollBack = true;
        break;
    }
}

if(rollBack) {
    try {
        conn.rollback();
    }
    catch(SQLException e) {}
}
else {
    try {
        conn.commit();
    }
    catch(SQLException e) {}
}

conn.close();


I run this code and, in order to test it, deliberately throw a
NoSuchElementException. My code correctly detects this and calls the
rollback method on the connection. But when I look in the database, the rows
before the exception have been inserted, and the rollback hasn't done
anything. So how do I specify the start of the transaction? I've even tried
executing Statements and PreparedStatements with the query "begin", trying
to emulate the PostGresql "begin" syntax, but this doesn't work either. I
know the syntax of the statements I use to persist the object are good, and
I'm not seeing any exceptions when I do any of the SQL commands. (I ignore
some of the exceptions because these haven't caused my any problems).

Has anybody successfully used the rollback method for a group of SQL
commands using the PostGresql JDBC driver?

Thanks

Ryan

Re: JDBC and transactions

From
Dave Cramer
Date:
Ryan,

This certainly should work as you have it. The only thing I can think of
is the persistToDb code is doing a commit? What does the postgres
backend log report?

Dave

On Tue, 2002-04-23 at 16:23, Ryan Chambers wrote:
>
> Hi
>
> I'm trying to figure out how to use transactions with JDBC and PostGresql,
> without much success. In particular, I can't figure out how to mark the
> start of a transaction and how to do a rollback.
>
> I'm iterating through a list of objects and I insert them into the DB one by
> one. However, it is possible to get a NoSuchElement runtime exception while
> going through the list. If this happens, I want to rollback all the previous
> inserts.
>
> The code/pseudo-code looks like this.
>
> Connection conn = // get the connection
> conn.setAutoCommit(false);
>
> boolean rollBack = false;
>
> while(myList.hasNext()) {
>     MyObject obj = null;
>     try {
>         obj = (MyObject)myList.next();
>     }
>     catch(NoSuchElementException e) {
>         rollBack = true;
>         break;
>     }
>
>     try {
>         persistToDB(conn, obj);
>     }
>     catch(SQLException e) {
>         rollBack = true;
>         break;
>     }
> }
>
> if(rollBack) {
>     try {
>         conn.rollback();
>     }
>     catch(SQLException e) {}
> }
> else {
>     try {
>         conn.commit();
>     }
>     catch(SQLException e) {}
> }
>
> conn.close();
>
>
> I run this code and, in order to test it, deliberately throw a
> NoSuchElementException. My code correctly detects this and calls the
> rollback method on the connection. But when I look in the database, the rows
> before the exception have been inserted, and the rollback hasn't done
> anything. So how do I specify the start of the transaction? I've even tried
> executing Statements and PreparedStatements with the query "begin", trying
> to emulate the PostGresql "begin" syntax, but this doesn't work either. I
> know the syntax of the statements I use to persist the object are good, and
> I'm not seeing any exceptions when I do any of the SQL commands. (I ignore
> some of the exceptions because these haven't caused my any problems).
>
> Has anybody successfully used the rollback method for a group of SQL
> commands using the PostGresql JDBC driver?
>
> Thanks
>
> Ryan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>

Re: JDBC and transactions

From
"Ryan Chambers"
Date:
Hi Dave

Arrghh... I _was_ doing a commit in the persistToDb() method. That's my
fault; it was some sloppy cutting-and-pasting of code. Everything is working
okay now.

Thanks for your help.

Ryan


-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
Sent: Tuesday, April 23, 2002 1:48 PM
To: Ryan Chambers
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] JDBC and transactions


Ryan,

This certainly should work as you have it. The only thing I can think of
is the persistToDb code is doing a commit? What does the postgres
backend log report?

Dave

On Tue, 2002-04-23 at 16:23, Ryan Chambers wrote:
>
> Hi
>
> I'm trying to figure out how to use transactions with JDBC and PostGresql,
> without much success. In particular, I can't figure out how to mark the
> start of a transaction and how to do a rollback.
>
> I'm iterating through a list of objects and I insert them into the DB one
by
> one. However, it is possible to get a NoSuchElement runtime exception
while
> going through the list. If this happens, I want to rollback all the
previous
> inserts.
>
> The code/pseudo-code looks like this.
>
> Connection conn = // get the connection
> conn.setAutoCommit(false);
>
> boolean rollBack = false;
>
> while(myList.hasNext()) {
>     MyObject obj = null;
>     try {
>         obj = (MyObject)myList.next();
>     }
>     catch(NoSuchElementException e) {
>         rollBack = true;
>         break;
>     }
>
>     try {
>         persistToDB(conn, obj);
>     }
>     catch(SQLException e) {
>         rollBack = true;
>         break;
>     }
> }
>
> if(rollBack) {
>     try {
>         conn.rollback();
>     }
>     catch(SQLException e) {}
> }
> else {
>     try {
>         conn.commit();
>     }
>     catch(SQLException e) {}
> }
>
> conn.close();
>
>
> I run this code and, in order to test it, deliberately throw a
> NoSuchElementException. My code correctly detects this and calls the
> rollback method on the connection. But when I look in the database, the
rows
> before the exception have been inserted, and the rollback hasn't done
> anything. So how do I specify the start of the transaction? I've even
tried
> executing Statements and PreparedStatements with the query "begin", trying
> to emulate the PostGresql "begin" syntax, but this doesn't work either. I
> know the syntax of the statements I use to persist the object are good,
and
> I'm not seeing any exceptions when I do any of the SQL commands. (I ignore
> some of the exceptions because these haven't caused my any problems).
>
> Has anybody successfully used the rollback method for a group of SQL
> commands using the PostGresql JDBC driver?
>
> Thanks
>
> Ryan
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>




---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html