Skip site navigation (1) Skip section navigation (2)

Re: PGJDBC 8 transaction problem

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Rodrigo Willian Bonatto <bonatto(at)diuno(dot)com(dot)br>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: PGJDBC 8 transaction problem
Date: 2006-06-05 13:25:06
Message-ID: 448430B2.6020602@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

pgsql-jdbc by date

Next:From: Rodrigo Willian BonattoDate: 2006-06-05 17:20:46
Subject: RES: PGJDBC 8 transaction problem
Previous:From: Dave CramerDate: 2006-06-05 13:14:32
Subject: Re: PGJDBC 8 transaction problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group