Обсуждение: RES: PGJDBC 8 transaction problem

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@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] Em nome de Oliver Jowett
Enviada em: segunda-feira, 5 de junho de 2006 10:25
Para: Rodrigo Willian Bonatto
Cc: pgsql-jdbc@postgresql.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?

               http://archives.postgresql.org

Rodrigo,

Then you will have to process all of the result sets that come back
from the query.

As Oliver said, each one of your queries will produce a result set.

Dave
On 5-Jun-06, at 1:20 PM, Rodrigo Willian Bonatto wrote:

> 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@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org] Em nome de Oliver Jowett
> Enviada em: segunda-feira, 5 de junho de 2006 10:25
> Para: Rodrigo Willian Bonatto
> Cc: pgsql-jdbc@postgresql.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?
>
>                http://archives.postgresql.org
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

Rodrigo Willian Bonatto wrote:

> Using BEGIN and COMMIT with PGJDBC 7.4 works perfectly but not with
> version 8.x.

BEGIN/COMMIT should work fine, they're just not the recommended way of
doing things. It sounds like you have your reasons for using them ..

As I said in my original reply, the problem lies in how you are
processing the results from a multistatement query:

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

It's not really a problem with BEGIN/COMMIT at all, I would expect that
you'd see the same issue with just an "UPDATE ...;SELECT ..." query

-O