Re: Fetching generated keys - Mailing list pgsql-jdbc

From Mike Clements
Subject Re: Fetching generated keys
Date
Msg-id 626C0646ACE5D544BC9675C1FB81846B338938@MAIL03.bedford.progress.com
Whole thread Raw
In response to Re: Fetching generated keys  ("Mike Clements" <mclement@progress.com>)
Responses Re: Fetching generated keys
List pgsql-jdbc
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

pgsql-jdbc by date:

Previous
From: Dave Cramer
Date:
Subject: Re: [HACKERS] Plan invalidation vs. unnamed prepared statements
Next
From: "Mike Clements"
Date:
Subject: Re: Fetching generated keys