Thread: Error while retrieving generated keys

My application trew the following exception:

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

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

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

        at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(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.

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 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@icontrol.com
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Error while retrieving generated keys
Date: Thu, 8 Mar 2007 19:49:52 -0800

See my responses inline:

-----Original Message-----
From: Heikki Linnakangas [mailto:hlinnaka@gmail.com] On Behalf Of Heikki Linnakangas
Sent: Friday, March 09, 2007 11:31 AM
To: Gary Greenberg
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Error while retrieving generated keys

Gary Greenberg wrote:

> I see it now. It should have been, however, mentioned in documentation for

> the JDBC driver.

Agreed, this question has come up many times recently. Would you like to

write a paragraph or two explaining the situation and the workaround?

I don't know about an easy workaround. Especially while using frameworks.

I am using Spring framework with Hibernate mapping and a lot of low level Java code is hidden from me. I do not have a particular desire to go back to plain JDBC calls. If I have to, I'd replace INSERT statement with a call to a stored function returning key value. But as said in my original email it is a lot of additional hassle.

> In essence it means that PostgreSQL has fallen into the third world of

> database engines.

...

 > I am really disappointed that PostgreSQL is becoming a fossil.

;-) Now you're exaggerating.

Am I? Who, in his right mind will go back from using JPA or Hibernate to plain old JDBC calls?

Should we go to managing transactions manually, OR mapping, etc?

It would be exaggerating if I’d offer to go back to punch cards or coding in assembler. (I do remember it).

> If there are no plans to make this feature work in the very near future,

> I'll be pushing for replacement of the engine.

Ken Johanson posted a preliminary patch in February, but there was a

bunch of issues with it. This is an open source project, so if you'd

like to see it happen, patches are more than welcome.

I can look up at this patch and see if I can contribute to fix it. Unfortunately I am on tight project schedule.

How do I download the patch, though?

--

   Heikki Linnakangas

   EnterpriseDB   http://www.enterprisedb.com

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 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@icontrol.com
To: pgsql-jdbc@postgresql.org
Subject: [JDBC] Error while retrieving generated keys
Date: Thu, 8 Mar 2007 19:49:52 -0800