Re: PreparedStatement

On Sat, 25 Sep 2004, Nathan Crause wrote:

> I have a need for the Connection.prepareStatement(String sql, int
> autoGeneratedKeys) method to be functional. Obviously, such keys would
> only ever be generated during an INSERT (as per the Java API
> specification: "This parameter is ignored if the SQL statement is not an
> INSERT statement").

Please see the previous discussion thread starting here:


http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&safe=off&threadm=Pine.LNX.4.33.0312301523060.5331-100000%40leary.csoft.net&rnum=3&prev=/groups%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

I know you are just trying to implement something that will make your
existing application work, but we need something that will work for all
situations.  There are a number of questions I raise in the thread that no
one has really answered.  I know you're looking to avoid the trigger
situation, so let's leave that out for now, but:

How would you return any generated keys for a statement like this:

CREATE TABLE mytable (a serial, b text);

INSERT INTO mytable (b) SELECT (b) FROM mytable;

with multiple rows currval is useless, it can even be useless in the one
row situation (for a pretty contrived case)

CREATE SEQUENCE myseq;
CREATE TABLE mytable (a int default nextval('myseq'), b int default
nextval('myseq'), c text);

INSERT INTO mytable (c) VALUES ('hi');

When writing a library (as opposed to an application) you need to be
prepared to handle all sorts of bizarre situations.  At the moment I just
don't think it's possible to make this method work for anything other than
a specific subset of situations.

Kris Jurka