From: | "Rodrigo Willian Bonatto" <bonatto(at)diuno(dot)com(dot)br> |
---|---|
To: | "Oliver Jowett" <oliver(at)opencloud(dot)com> |
Cc: | <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | RES: PGJDBC 8 transaction problem |
Date: | 2006-06-05 17:20:46 |
Message-ID: | 54CC7CADE26E884EB2BCEC863F5C1F7001012FEF@moscou.diuno.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Hi Oliver and Dave,
I agree with you. I could use conn.commit(), but I have a particular
environment here and I'll try to explain.
My application use XML in my business tier to create and validate my
business rules.
My application use the connection provided by a data source Application
Server and I use my own XML tags, like <query/> to get some information
from database to be used in my business rules.
Some times I need to update some records and use these data in my
business rules and I need to this using my own XML tags.
In some cases I use BEGIN with COMMIT in the body of my own XML tag
<query/> to perform some updates and queries without java code.
Using BEGIN and COMMIT with PGJDBC 7.4 works perfectly but not with
version 8.x.
I could use another XML tag to perform update separately from another
SQL queries, but I would like to know if PGJDBC 8 supports BEGIN and
COMMIT in a single statement.
Regards,
Rodrigo
-----Mensagem original-----
De: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] Em nome de Oliver Jowett
Enviada em: segunda-feira, 5 de junho de 2006 10:25
Para: Rodrigo Willian Bonatto
Cc: pgsql-jdbc(at)postgresql(dot)org
Assunto: Re: [JDBC] PGJDBC 8 transaction problem
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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2006-06-05 18:09:10 | Re: RES: PGJDBC 8 transaction problem |
Previous Message | Oliver Jowett | 2006-06-05 13:25:06 | Re: PGJDBC 8 transaction problem |