Home > mailing lists

Thread: PGJDBC 8 transaction problem

PGJDBC 8 transaction problem

From

"Rodrigo Willian Bonatto"

Date:

05 June 2006, 13:03:00

Hi,

I have a problem using transactions with PGJDBC 8.0 or greater.

For example:

In the database I have the following record in the “employee” table:

name age

John 27

If I use the PGJDBC 7.4 to update and list the same record in a single statement like this:

Class.forName("org.postgresql.Driver");

Connection conn = DriverManager.getConnection(url, "postgres", "postgres");

conn.setAutoCommit(false);

Statement stmt = conn.createStatement();

StringBuffer query = new StringBuffer();

query.append("BEGIN;");

query.append("update employee set age = 28 where age = 27;");

query.append("COMMIT;");

query.append("select name from employee where age = 28");

ResultSet rs = stmt.executeQuery(query.toString());

Here the resultset will return “John”, but if I use PGJDBC driver version 8 or greater, the statement return any result.

Any ideas?

Thanks in advanced!

Rodrigo

Re: PGJDBC 8 transaction problem

From

Dave Cramer

Date:

05 June 2006, 13:19:29

Rodrigo,

You don't need the begin, and end

you use connection.commit() instead

Dave

On 5-Jun-06, at 9:01 AM, Rodrigo Willian Bonatto wrote:

Hi,

I have a problem using transactions with PGJDBC 8.0 or greater.

For example:

In the database I have the following record in the “employee” table:

name age
John 27

If I use the PGJDBC 7.4 to update and list the same record in a single statement like this:

Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection(url, "postgres", "postgres");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
StringBuffer query = new StringBuffer();
query.append("BEGIN;");
query.append("update employee set age = 28 where age = 27;");
query.append("COMMIT;");
query.append("select name from employee where age = 28");
ResultSet rs = stmt.executeQuery(query.toString());

Here the resultset will return “John”, but if I use PGJDBC driver version 8 or greater, the statement return any result.

Any ideas?

Thanks in advanced!

Rodrigo

Re: PGJDBC 8 transaction problem

From

Oliver Jowett

Date:

05 June 2006, 13:25:50

Rodrigo Willian Bonatto wrote:

> query.append("BEGIN;");
> query.append("update employee set age = 28 where age = 27;");
> query.append("COMMIT;");
> query.append("select name from employee where age = 28");
> ResultSet rs = stmt.executeQuery(query.toString());
>
> Here the resultset will return “John”, but if I use PGJDBC driver
> version 8 or greater, the statement return any result.

The more recent drivers implement support for returning multiple
resultsets from a query correctly, which older drivers didn't do. Your
Statement will actually have four results associated with it -- one for
each of BEGIN, UPDATE, COMMIT, SELECT. I would expect executeQuery() to
throw an exception because the query returned something other than a
single resultset.

You will need to use Statement.execute() / Statement.getMoreResults() /
Statement.getResultSet() to step to the 4th result and retrieve the
SELECT's results (you could also get at the UPDATE's update count in a
similar way).

Also, you should avoid explicit BEGIN/COMMIT statements if you can --
Connection.setAutocommit() / Connection.commit() is the recommended way
to manage transaction boundaries.

-O