Re: JDBC and transactions - Mailing list pgsql-jdbc

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