Re: JDBC-Interface - Behaviour on Update, Insert or Delete returning ResultSets - Inconsistency to Console & ODBC

On Sat, 18 Aug 2007, Otto Weichselbaum wrote:

> While using views calling functions on INSERT, UPDATE and DELETE via
> according rules, I noticed an inconsistent behaviour of the
> JDBC-interface of postgres;
> =A0an INSERT-, UPDATE- or DELETE-statement producing tuples as return-val=
ue
> (in our case through rules calling functions but although via the
> 'RETURNING'-clause of a single INSERT- or UPDATE-statement) is returning
> the expected number of tuples when called via the console (even through
> pgAdmin) or via ODBC
> BUT
> when called via JDBC only an 'UpdateCount' of 0 is returned;
> debugging to protocol-level showed, that the postgreSQL-server does not
> even differ between a 'simple' UPDATE or one returning tuples;

I believe this differing results you are seeing is based upon whether the=
=20
query is executed using the simple or extended query protocol.  The JDBC=20
driver always uses extended query protocol when speaking to a V3 protocol=
=20
capable server.  Another workaround you can use is adding=20
?protocolVersion=3D2 to your URL to make it use the V2 protocol and simple=
=20
query execution.

It works with the simple query protocol because you send the server a=20
query and then loop retrieving any number of results that are returned.=20
The extended query protocol is designed around single query gives a=20
single result, so it isn't really prepared to handle the update count=20
and results at the same time.

Kris Jurka