Re: Error while retrieving generated keys

from AbstractJdbc3Connection.java:

348 public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)349 throws SQLException350 {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)comTo: pgsql-jdbc(at)postgresql(dot)orgSubject: [JDBC] Error while retrieving generated keysDate: 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.

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(AbstractJdbc3C
onnection.java:352)

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

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

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

See my responses inline:

-----Original Message-----
From: Heikki Linnakangas [mailto:hlinnaka(at)gmail(dot)com] On Behalf Of Heikki
Linnakangas
Sent: Friday, March 09, 2007 11:31 AM
To: Gary Greenberg
Cc: pgsql-jdbc(at)postgresql(dot)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

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

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.

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

> See my responses inline:
>
>
>
> -----Original Message-----
> From: Heikki Linnakangas [mailto:hlinnaka(at)gmail(dot)com] On Behalf Of
> Heikki Linnakangas
> Sent: Friday, March 09, 2007 11:31 AM
> To: Gary Greenberg
> Cc: pgsql-jdbc(at)postgresql(dot)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
>
>

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

What metadata I am suppose to check? May be you meant something else or you
are just ignorant. Metadata gives you information about the database schema
(tables, columns, etc.) but not about deficiencies of the driver.
I was looking at the reference guide and JAVADOC.
I am not a driver developer but I am working with databases for 30 years and
I know my business.
I would gladly help with the patch if I'd know where the faulty code is
located. Unfortunately I cannot spend weeks searching the CVS repository for
it (unless you'll pay my wages).
Flaming won't help you in that.

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Oliver Jowett
Sent: Sunday, March 11, 2007 12:47 AM
To: Gary Greenberg
Cc: pgsql-jdbc(at)postgresql(dot)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.

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq