Re: JDBC and transactions

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(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of Dave Cramer
Sent: Tuesday, April 23, 2002 1:48 PM
To: Ryan Chambers
Cc: pgsql-jdbc(at)postgresql(dot)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