Re: Error while retrieving generated keys

I recently found a workaround that seems to perform the same as keygen -
all in one RDB round trip, obviating the need for a second round trip to
fetch the key from the sequence.

If you add a "RETURNING pk" clause to the INSERT statement, the JDBC
execute() for this insert statement will return a ResultSet instead of a
row count. You treat this ResultSet exactly like the one that would have
been returned from GetGeneratedKeys(). That is, call next() and then
call get() to get the pk.

In other words, you never call GetGeneratedKeys(). Instead, after
calling statement.execute() you call statement.getResultSet().

Looking at the source code for the latest JDBC driver, it looks like it
would not be too difficult to implement the
Connection.prepareStatement() call that specifies the columns to return
as generated keys. Perhaps you could in this call add a RETURNING clause
to the SQL for the same columns... just a thought, I haven't gone down
that path myself because the above approach worked just fine for me.

HTH,

________________________________

From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Gary Greenberg
Sent: Friday, March 09, 2007 10:37 AM
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] Error while retrieving generated keys

I see it now. It should have been, however, mentioned in documentation
for the JDBC driver.

I have also reviewed a recent thread
http://archives.postgresql.org/pgsql-jdbc/2007-03/msg00038.php on a
similar topic.

The gist of it was how to overcome the deficiency of JDBC3 driver using
JDBC1 features.

It looks to me that you, guys, are missing the primary point:

Ability to retrieve auto-generated key is one of the key features of
JDB3. It exists for over 2 years and all JDBC3 drivers I know of,
supports it.

A lot of frameworks (JPA, Hibernate, etc.) are relying on it. It means
that they won't work with PostgreSQL.

It also means that developers must do a lot of extra tinkering to adopt
many standard apps for PostgreSQL.

In essence it means that PostgreSQL has fallen into the third world of
database engines.

I used to work a lot with PostgreSQL in mid-90s and loved it but I just
recently returned to this world from working with Oracle, Sybase, etc.

I am really disappointed that PostgreSQL is becoming a fossil.

If there are no plans to make this feature work in the very near future,
I'll be pushing for replacement of the engine.

________________________________

From: QuanZongliang [mailto:quanzongliang(at)hotmail(dot)com]
Sent: Thursday, March 08, 2007 8:48 PM
To: Gary Greenberg
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: RE: [JDBC] Error while retrieving generated keys

from AbstractJdbc3Connection.java:

348 public PreparedStatement prepareStatement(String sql, int
autoGeneratedKeys)
349 throws SQLException
350 {
351 if (autoGeneratedKeys != Statement.NO_GENERATED_KEYS)
352 throw new PSQLException(GT.tr("Returning autogenerated
keys is not supported."), PSQLState.NOT_IMPLEMENTED);
353 return prepareStatement(sql);
354 }

So, it is not supported.

Only the prepareStatement(sql, Statement.NO_GENERATED_KEYS) can be used.

________________________________

From: gary(at)icontrol(dot)com
To: pgsql-jdbc(at)postgresql(dot)org
Subject: [JDBC] Error while retrieving generated keys
Date: Thu, 8 Mar 2007 19:49:52 -0800

My application trew the following exception:

org.postgresql.util.PSQLException: Returning autogenerated keys is not
supported.

at
org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJd
bc3Connection.java:352)

at
org.apache.commons.dbcp.DelegatingConnection.prepareStatement(Delegating
Connection.java:394)

at
org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.pre
pareStatement(PoolingDataSource.java:370)

I am using 8.2-504.jdbc3 driver with the PostgreSql 8.0.8 on SUSE Linux.

I have not seen anywhere in the documentation that this standard JDBC3
feature is not supported.

Can anyone explain it to me, please.