Обсуждение: currval bug??

Hi all,
i have recently upgraded my development environment:
FROM postgresql 7.4 TO postgresql 8.0beta5
FROM jdk1.4 TO jdk1.5
FROM pg74.215.jdbc3.jar TO pg80b1.308.jdbc3.jar

now, i have a FRUSTRATING bug:

With this query (which works fine with previous pg74jdbc):

*insert into tablename (fieldname) values ('testvalue');select
currval('tablename_id_seq');*

this exeception was throwed:

*org.postgresql.util.PSQLException: No results were returned by the query.*

Obviously the query has to return a result.

Any idea?
thanks!!
gaboc

--
_____________________________________________________________

"Microsoft isn't the answer. Microsoft is the question, and my answer is no!"

On Tue, 21 Dec 2004, Gabriele Carteni wrote:

> Hi all,
> i have recently upgraded my development environment:
> FROM postgresql 7.4 TO postgresql 8.0beta5
> FROM jdk1.4 TO jdk1.5
> FROM pg74.215.jdbc3.jar TO pg80b1.308.jdbc3.jar
>
> now, i have a FRUSTRATING bug:
>
> With this query (which works fine with previous pg74jdbc):
>
> *insert into tablename (fieldname) values ('testvalue');select
> currval('tablename_id_seq');*
>
> *org.postgresql.util.PSQLException: No results were returned by the query.*
>
> Obviously the query has to return a result.

The 7.4 driver sends the whole query string together to the backend and
gets a result.  The 8.0 driver splits the query into two and sends them
individually.  This is required to use the V3 extended query protocol.
When it gets the result from the first part it is expecting a result and
isn't getting one.  I'm not sure if this should be an error or not, in
general mashing queries together isn't a great idea.  Perhaps you should
be using Statement.execute() and then iterating through any/all results
returned?

Kris Jurka

Hi Kris and thank you for your fast answer..

my problem is to retrieve the last autogenerated key after an insert
statement.
i always used this method (mashing queries together where the last is a
currval query).
What i should use to have the same result in pgsql8?

i apologize that java.sql.Statement.getGeneratedKeys() isn't still
implemented.

thanks!
gaboc

Kris Jurka wrote:

>On Tue, 21 Dec 2004, Gabriele Carteni wrote:
>
>
>
>>Hi all,
>>i have recently upgraded my development environment:
>>FROM postgresql 7.4 TO postgresql 8.0beta5
>>FROM jdk1.4 TO jdk1.5
>>FROM pg74.215.jdbc3.jar TO pg80b1.308.jdbc3.jar
>>
>>now, i have a FRUSTRATING bug:
>>
>>With this query (which works fine with previous pg74jdbc):
>>
>>*insert into tablename (fieldname) values ('testvalue');select
>>currval('tablename_id_seq');*
>>
>>*org.postgresql.util.PSQLException: No results were returned by the query.*
>>
>>Obviously the query has to return a result.
>>
>>
>
>The 7.4 driver sends the whole query string together to the backend and
>gets a result.  The 8.0 driver splits the query into two and sends them
>individually.  This is required to use the V3 extended query protocol.
>When it gets the result from the first part it is expecting a result and
>isn't getting one.  I'm not sure if this should be an error or not, in
>general mashing queries together isn't a great idea.  Perhaps you should
>be using Statement.execute() and then iterating through any/all results
>returned?
>
>Kris Jurka
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>


--
_____________________________________________________________
Ing. Gabriele Carteni
Center for Advanced Computational Technologies / ISUFI
University of Lecce, Italy

office: +39.0832.298.122
ICQ#: 128087012

gpg --recv-keys --keyserver pgp.mit.edu 0x54E5C362

"Microsoft isn't the answer. Microsoft is the question, and my answer is no!"

On Tue, 21 Dec 2004, Gabriele Carteni wrote:

> my problem is to retrieve the last autogenerated key after an insert
> statement. i always used this method (mashing queries together where the
> last is a currval query). What i should use to have the same result in
> pgsql8?
>

Well, as I mentioned in my previous email, I'm not sure if this is an
error in the JDBC driver.  We could conceivably return the first result
found from a combined query like you've shown.  I'm not sure what the code
implications are, but it seems like a reasonable thing to do from an
interface perspective.

To get this working with the current code you will probably have to split
it up into individual queries.  In my previous email I also mentioned that
using Statement.execute() instead of Statement.executeQuery() might work,
but I've not tested it.

Kris Jurka