Re: 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@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.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.lewis@mir3.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