Thread: Error while retrieving generated keys

Error while retrieving generated keys

From
"Gary Greenberg"
Date:

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.

Re: Error while retrieving generated keys

From
QuanZongliang
Date:
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

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.

Re: Error while retrieving generated keys

From
Dave Cramer
Date:
Gary,

Check the list archives please, we have just had this discussion at length very recently.

The short version is that it is not currently implemented.

Dave
On 8-Mar-07, at 10:49 PM, Gary Greenberg wrote:

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.



Re: Error while retrieving generated keys

From
"Gary Greenberg"
Date:

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@hotmail.com]
Sent: Thursday, March 08, 2007 8:48 PM
To: Gary Greenberg
Cc: pgsql-jdbc@postgresql.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@icontrol.com
To: pgsql-jdbc@postgresql.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(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.

Re: Error while retrieving generated keys

From
Heikki Linnakangas
Date:
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?

> 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.

> 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.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: Error while retrieving generated keys

From
"Gary Greenberg"
Date:

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

Re: Error while retrieving generated keys

From
Heikki Linnakangas
Date:
Gary Greenberg wrote:
> I don't know about an easy workaround. Especially while using frameworks.

There's plenty of options, for example calling lastval, or using INSERT
RETURNING, or stored procedures. With frameworks, I'm sure you can
usually work-around by configuring the framework properly. A little bit
of googling tells me that you might be able to set
hibernate.jdbc.use_get_generated_keys to false, though not being a
Hibernate user myself I have no idea where to put that.

In any case, it would help a lot if you could write a FAQ entry of
paragraph or two when you find a solution.

>  > 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?

The lack of one fairly new JDBC API call hardly makes the whole database
management system a fossil, that's what I'm saying.

> 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?

See the archives for the patch and discussion:

http://archives.postgresql.org/pgsql-jdbc/2007-02/msg00074.php

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: Error while retrieving generated keys

From
"Mike Clements"
Date:
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@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Gary Greenberg
Sent: Friday, March 09, 2007 10:37 AM
To: pgsql-jdbc@postgresql.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@hotmail.com]
Sent: Thursday, March 08, 2007 8:48 PM
To: Gary Greenberg
Cc: pgsql-jdbc@postgresql.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@icontrol.com
To: pgsql-jdbc@postgresql.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(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.

Re: Error while retrieving generated keys

From
Dave Cramer
Date:

On 9-Mar-07, at 4:14 PM, Gary Greenberg wrote:

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.


Hibernate deals with this just fine. You just have to specify the correct KeyGenerator.

 

> 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



Re: Error while retrieving generated keys

From
Oliver Jowett
Date:
Gary Greenberg wrote:
> I see it now. It should have been, however, mentioned in documentation
> for the JDBC driver.

It's documented in the metadata. Don't you check the metadata?

> 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.

Send us a patch.

-O