Thread: BUG #1988: keygen not implemented

The following bug has been logged online:

Bug reference:      1988
Logged by:          Mike Clements
Email address:      mikec@actional.com
PostgreSQL version: 8.0.3
Operating system:   WinXP
Description:        keygen not implemented
Details:

Create a table with an integer primary key using a sequence to automatically
assign values.
Connect to the database with a JDBC client.
Insert a row into the table using:
Connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
The driver throws an exception saying this method is not yet implemented.
What it should do is create the prepared statement so when you execute it,
the returned ResultSet has the generated primary key.

Mike Clements wrote:

> Insert a row into the table using:
> Connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
> The driver throws an exception saying this method is not yet implemented.

This is an optional part of the JDBC spec, and the driver doesn't claim
to support it in the metadata it provides
(DatabaseMetaData.supportsGetGeneratedKeys() returns false).

> What it should do is create the prepared statement so when you execute it,
> the returned ResultSet has the generated primary key.

Unfortunately this requires functionality in the backend that does not
yet exist (support for "INSERT .. RETURNING ...", or similar).

-O

Thanks for the info. I found a workaround by selecting the current value of=
 the sequence after doing the insert. This however is not desirable since i=
t requires another round trip call to the DB, and it requires PostGRE SQL s=
pecific code in my "generic" JDBC client. If the driver supported getGenera=
tedKeys(), client applications could perform better and be truly generic.

Also looking at the release notes I see I'm not the only person asking for =
this feature... Good luck.

> -----Original Message-----
> From: Oliver Jowett [mailto:oliver@opencloud.com]=20
> Sent: Sunday, October 23, 2005 2:24 PM
> To: Mike Clements
> Cc: pgsql-bugs@postgresql.org
> Subject: Re: [BUGS] BUG #1988: keygen not implemented
>=20
> Mike Clements wrote:
>=20
> > Insert a row into the table using:
> > Connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
> > The driver throws an exception saying this method is not=20
> yet implemented.
>=20
> This is an optional part of the JDBC spec, and the driver=20
> doesn't claim=20
> to support it in the metadata it provides=20
> (DatabaseMetaData.supportsGetGeneratedKeys() returns false).
>=20
> > What it should do is create the prepared statement so when=20
> you execute it,
> > the returned ResultSet has the generated primary key.
>=20
> Unfortunately this requires functionality in the backend that=20
> does not=20
> yet exist (support for "INSERT .. RETURNING ...", or similar).
>=20
> -O
>=20
>=20