Re: Support of getting generated keys in driver version 8.4devel

Hello!

This is my environment:
Driver version: PostgreSQL 8.4devel JDBC3 (build 700)
PostgreSQL version: 8.3.4

I am looking for getting generated keys support in the driver. The
changelog says it has basic generated keys support. However,
DatabaseMetaData.supportsGetGeneratedKeys() returns false and when I
execute:

con.prepareStatement("insert into tab0 values(default, 444)", new
String[] {"id"});

I am receiving:

Caused by: org.postgresql.util.PSQLException: Returning autogenerated
keys is not supported.
at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement
(AbstractJdbc3Connection.java:448)

Am I missing something? If not, is the generated keys support going to
be implemented in a future build of 8.4devel?

Happy New Year,
Milosz

On Wed, 31 Dec 2008, Mi³osz Tylenda wrote:

> I am looking for getting generated keys support in the driver. The
> changelog says it has basic generated keys support. However,
> DatabaseMetaData.supportsGetGeneratedKeys() returns false

I intentionally left supportsGetGeneratedKeys false because we don't
support returning generated keys by column indexes. Without knowing how
used that method is, I'm not sure if this is more or less helpful. I'm
open to input if people think we should change it to true.

> and when I execute:
>
> con.prepareStatement("insert into tab0 values(default, 444)", new
> String[] {"id"});
>
> I am receiving:
>
> Caused by: org.postgresql.util.PSQLException: Returning autogenerated
> keys is not supported.

This was an oversight on my part. I added support for the Statement
methods, but somehow not for PreparedStatement methods. Shouldn't be too
tough to add, let me take a look and get back to you in a day or two.

Kris Jurka

AFAIK it is still not implemented however, you may try to use RETURNS
clause in INSERT statement.
This works perfectly good.
Tomisław Kityński

Miłosz Tylenda pisze:
> Hello!
>
> This is my environment:
> Driver version: PostgreSQL 8.4devel JDBC3 (build 700)
> PostgreSQL version: 8.3.4
>
> I am looking for getting generated keys support in the driver. The
> changelog says it has basic generated keys support. However,
> DatabaseMetaData.supportsGetGeneratedKeys() returns false and when I
> execute:
>
> con.prepareStatement("insert into tab0 values(default, 444)", new
> String[] {"id"});
>
> I am receiving:
>
> Caused by: org.postgresql.util.PSQLException: Returning autogenerated
> keys is not supported.
> at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement
> (AbstractJdbc3Connection.java:448)
>
> Am I missing something? If not, is the generated keys support going to
> be implemented in a future build of 8.4devel?
>
> Happy New Year,
> Milosz
>
>

> I intentionally left supportsGetGeneratedKeys false because we don't
> support returning generated keys by column indexes.  Without knowing how
> used that method is, I'm not sure if this is more or less helpful.  I'm
> open to input if people think we should change it to true.

Kris, my picture is that I work with Apache OpenJPA - an object-
relational mapper. OpenJPA checks whether generated keys are supported
by calling DatabaseMetaData.supportsGetGeneratedKeys(). If true, the
generated keys are retrieved from PreparedStatement by giving column
names. If false, a second query is executed to get the value of
sequence associated with serial column. So, in the OpenJPA context,
supportsGetGeneratedKeys returning true would be more helpful, but I
can also imagine a case where it would break things.

Greetings,
Milosz

> AFAIK it is still not implemented however, you may try to use RETURNS
> clause in INSERT statement.
> This works perfectly good.
> Tomisław Kityński

Tomisław, thanks. I have also tried this and it works. However, I am
more interested in driver support for generated keys because modifying
INSERT statements is not a piece of cake in my case.

Cheers,
Miłosz

On Mon, 5 Jan 2009, Miosz Tylenda wrote:

>> I intentionally left supportsGetGeneratedKeys false because we don't
>> support returning generated keys by column indexes. Without knowing how
>> used that method is, I'm not sure if this is more or less helpful. I'm
>> open to input if people think we should change it to true.
>
> Kris, my picture is that I work with Apache OpenJPA - an object-
> relational mapper. OpenJPA checks whether generated keys are supported
> by calling DatabaseMetaData.supportsGetGeneratedKeys(). If true, the
> generated keys are retrieved from PreparedStatement by giving column
> names. If false, a second query is executed to get the value of
> sequence associated with serial column. So, in the OpenJPA context,
> supportsGetGeneratedKeys returning true would be more helpful, but I
> can also imagine a case where it would break things.
>

Please try this updated driver which supports generated keys for prepared
statements as well. I've also flipped supportsGetGeneratedKeys to true as
I believe that will be more helpful to more users.

http://ejurka.com/pgsql/jars/genkey/

Kris Jurka

> Please try this updated driver which supports generated keys for prepared
> statements as well. I've also flipped supportsGetGeneratedKeys to true as I
> believe that will be more helpful to more users.
>
> http://ejurka.com/pgsql/jars/genkey/

Kris,

Thanks for the update. I have done a few tests and it works.
supportsGetGeneratedKeys returns true and I was able to successfully
insert rows and get generated keys by using column names.

Best Regards,
Milosz

Miłosz Tylenda wrote:
>> Please try this updated driver which supports generated keys for prepared
>> statements as well. I've also flipped supportsGetGeneratedKeys to true as I
>> believe that will be more helpful to more users.
>>
>> http://ejurka.com/pgsql/jars/genkey/
>
> Thanks for the update. I have done a few tests and it works.
> supportsGetGeneratedKeys returns true and I was able to successfully
> insert rows and get generated keys by using column names.
>

I've committed this code to CVS.

Kris Jurka