Thread: looking for a jdbc method.

Krishnan Trikkadeeri wrote:
> Hi,
>
> The JDBC API at the Sun website describes a two-parameter version of the method 'prepareStatement' in the interface
'Connection' with capability to retrieve auto-generated keys. One of the samples in the Sun JDBC Tutorial site also use
thismethod.  
>
> Is this method available in the postgresql jdbc driver? I have not been able to locate it so far.
> If it is not implemented as yet, is there any plan to implement this method any time soon?

A few seconds on Google turns up:

http://archives.postgresql.org/pgsql-jdbc/2004-09/msg00191.php

http://groups.google.com/group/comp.databases.postgresql.interfaces.jdbc/browse_frm/thread/eae4d9b44d566bcf?hl=en&lr&ie=UTF-8&c2coff=1&safe=off&rnum=3&prev=%2Fgroups%3Fq%3Dgenerated%2Bkeys%26hl%3Den%26lr%3D%26ie%3DUTF-8%26group%3Dcomp.databases.postgresql.interfaces.jdbc%26c2coff%3D1%26safe%3Doff%26selm%3DPine.LNX.4.33.0312301523060.5331-100000%2540leary.csoft.net%26rnum%3D3

They're pretty ancient, but has a good discussion of the issues.

You should be able to use INSERT ... RETURNING to achieve the effect
you're going for without the issues involved in using the two-argument
prepareStatement interface.

I haven't looked into the current status of the interface. It's broken
by design and should really be deprecated in the JDBC spec, so it's not
all that interesting.

INSERT statements for which the two-argument prepareStatement's return
value makes no sense include:

-- INSERT using query value-source
INSERT INTO sometable SELECT something FROM othertable;

-- Multi-valued INSERT
INSERT INTO sometable (x, y, z) VALUES
(1,2,3),
(1,4,6),
(1,9,2),
(4,2,1),
(9,1,4);

INSERT ... RETURNING can provide sane results for these cases.

See, for example:

http://archives.postgresql.org/pgsql-jdbc/2007-03/msg00090.php

--
Craig Ringer

> I haven't looked into the current status of the interface. It's broken
> by design and should really be deprecated in the JDBC spec, so it's not
> all that interesting.

Er ... I spoke too soon. I could've sworn I ran into a JDBC method that
could only return a single key, but it's not the two-argument form of
PrepareStatement:

http://www.ibm.com/developerworks/java/library/j-jdbcnew/

"the values for the generated keys are obtained by retrieving a
ResultSet from a Statement's instance method, getGeneratedKeys(). The
ResultSet contains a row for each generated key."

My bad.

So - exacltly which PostgreSQL JDBC driver version are you using,
anyway? Can you show some code that doesn't work, and how it fails?

--
Craig Ringer

On Thu, 11 Sep 2008, Craig Ringer wrote:

> So - exacltly which PostgreSQL JDBC driver version are you using,
> anyway? Can you show some code that doesn't work, and how it fails?
>

No version of the JDBC driver supports getGeneratedKeys in any of its
forms.

Kris Jurka