Re: Fetching generated keys

Yet even more info:

Because the returned result set *appeared* to have my data in it, I
tried not calling "next()" at all. This worked - example:

WORKS:
rs = stmt.execute();
pk = rs.getLong(1);

FAILS:
rs = stmt.execute();
rs.next(); // this throws an exception
pk = rs.getLong(1);

According to the JDK API docs for package java.sql, all results sets
should start with the cursor just before the first row. Thus one is
supposed to call "next()" to get the first row. This is how all the
result sets I've worked with in the past function.

I can work around this for now. But I'm curious - is it a bug?

Also there appears to be another bug, since calling first() on this
result set throws an NPE, but it should either return false or throw a
SQLException if it's a forward-only result set. Same goes for next() -
if there are no more rows it should return FALSE, not throw an
exception.

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Mike Clements
Sent: Tuesday, March 06, 2007 4:26 PM
To: PostgreSQL JDBC List
Subject: Re: [JDBC] Fetching generated keys

P.S.
If I call statement.execute(), it tells me the return value is a result
set. And I can get this result set (it's not null). But when I try to
use it, it fails. Calling "first()" throws a NPE, calling "getRow()" or
"next()" both fail too.

It appears that the JDBC result set of an "INSERT ... RETURNING" command
is unusable.

-----Original Message-----
From: Mike Clements
Sent: Tuesday, March 06, 2007 4:06 PM
To: PostgreSQL JDBC List
Subject: RE: [JDBC] Fetching generated keys

Hi everyone - one more question.

I can prepare this "INSERT ... RETURNING" statement, but I can't run it
or fetch the results from the JDBC driver.

I figured it would have to work one of 2 ways:

1. call executeUpdate() and then getGeneratedKeys().

2. call executeQuery() and use the result set.

But both fail!

If I call statement.executeUpdate(), it throws an exception saying it
unexpectedly returned a result set.

If I call statement.executeQuery(), it throws an exception saying the
connection is already closed ?!?!

What is the right way to get the results back from an insert using the
"INSERT ... RETURNING" clause?

Thanks

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Mike Clements
Sent: Monday, March 05, 2007 6:10 PM
To: Mark Lewis
Cc: PostgreSQL JDBC List
Subject: Re: [JDBC] Fetching generated keys

Thanks everyone for the quick help and explanations.

-----Original Message-----
From: Mark Lewis [mailto:mark(dot)lewis(at)mir3(dot)com]
Sent: Monday, March 05, 2007 5:29 PM
To: Mike Clements
Cc: A.M.; PostgreSQL JDBC List
Subject: Re: [JDBC] Fetching generated keys

On Mon, 2007-03-05 at 17:19 -0500, Mike Clements wrote:
...
> The FAQ you posted suggests that "currval" uses a level of isolation
> that is more strict than the default "read committed". If so, setting
> isolation level to serializable would be unnecessary. Is that true? Or

> should I do it just to be safe? I'd hate to do it if unnecessary due
> to the performance and locking implications.

Yes, currval definitely returns the last value returned by the sequence
in the current transaction. Anything done in other transactions is
ignored.

Just for kicks, I did a simple test with two psql sessions to
demonstrate:

psql1: BEGIN TRANSACTION;
psql1: SELECT nextval('my_seq'); -- returns 4988
psql2: BEGIN TRANSACTION;
psql2: SELECT nextval('my_seq'); -- returns 4989
psql1: SELECT currval('my_seq'); -- returns 4988

(also tested with psql2 committing the transaction before psql1 reads
currval. Made no difference.)

-- Mark Lewis

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate