Обсуждение: PreparedStatement

Hi guys,<br /><br /> First time posting to this mailing list, so please be patient with me.<br /><br /> I have a need
forthe Connection.prepareStatement(String sql, int autoGeneratedKeys) method to be functional. Obviously, such keys
wouldonly ever be generated during an INSERT (as per the Java API specification: "This parameter is ignored if the SQL
statementis not an <code>INSERT</code> statement").<br /><br /> Unless someone strongly objects, I am going to be
lookinginto coding such functionality. I do have several questions, though. Now, I personally have only ever seen
INSERTstatements for a single table. Is it even vaguely possible for an INSERT statement to directly affect more than
onetable (by directly, I mean excluding any tables which may be affected by a trigger)?<br /><br /> If not, then what I
amproposing is for the Connection.prepareStatement() method to use regular expression parsing (via the java.util.regex
package)to extract the table name being inserted into, and querying the system tables to check for an int (or bigint)
columnswhich contain a default which refers to the "NEXTVAL" function, and which do NOT form part of the actual insert
statement(i.e. the default value will be inserted, hence using the sequence number system).<br /><br /> I would
obviouslyneed to create a special PreparedStatement implentation which would store these column names. Upon execution
ofthis prepared statement, the class would then internally execute all "CURRVAL()" queries for the columns known to
havebeen autogenerated.<br /><br /> So, I'm thinking that someone is probably wondering why the heck I want to do this?
SurelyI could just code the "CURRVAL()" queries right within my program. Well, my motivation is that the company I am
workingfor is trying to move our systems from a MySQL database to PostgreSQL. Many of the data insertion areas on the
systemuse the auto-generated-keys resultset feature, and I personally don't want to fish through all the programs and
changethem. Hehehe - lazy dude <span class="moz-smiley-s4"><span> :-P </span></span><br /><br /><br /> 

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

On Sat, 25 Sep 2004, Nathan Crause wrote:

> Surely the use of CURRVAL in general would be useless in your example
> when using a SELECT in conjunction with the INSERT? Not only that,
> considering that you cannot guarantee the ORDER in which the SELECT will
> be returning records, I don't see how ANY autogenerated key result set
> would be meaningful, regardless of database engine (unless issuing a
> ORDER BY clause in the select).

Right, this is more a problem I have with the spec.  It just says it
returns a ResultSet, but it doesn't tell us anything about what it
contains or how it should work for multiple rows.

> I do agree with your ascertion that when writing a library all sorts of
> bazaar situations need to be taken into account - that's why I asked the
> question I did on my original post. That said, you are contending that no
> development at all is better than something that may solve at least some
> situations. If that is the case, then I dare say that when Postgres was
> at version 7.2, 90% of the DatabaseMetaData implementation for the JDBC
> needed to be scrapped, because it returned absolute bollocks (I haven't
> tried with the more recent releases).
>

The DatabaseMetaData got a significant upgrade for the 7.3 release.  Right
now the problems I'm aware of are that it always returns JDBC2 results
even though JDBC3 has added some more columns to some results and
getTypeInfo doesn't sort correctly.  If you've noticed other problems
please complain about them.

Regarding your original post, I was perhaps too harsh.  I don't mean to
discourge you, I just wanted to make sure you knew what you were up
against.  I don't have a problem with a partial implementation as long as
that implementation does not return incorrect results for a situation it
cannot handle.  If it errors out then, that's fine, I just suspect it will
be difficult to detect these cases.

Kris Jurka

On Mon, 27 Sep 2004, Nathan Crause wrote:

> I'm wondering if there is some way to make a trigger on the sequences
> themselves. Create some sort of session-specific temporary table which
> contains records of all auto-generated sequences in that session, and
> then figure out some way to return only the portion which was created by
> the last INSERT (perhaps something as simple as flushing the table after
> each resultset it returned to the connection).
>

This looks like an application kind of fix, not a library solution.  A
JDBC driver can't go around creating tables and triggers and other things.

It might be worthwhile to investigate how other JDBC drivers handle this.
So far I've only looked at MySQL, but checking MaxDB and Firebird's driver
source would be revealing.  Checking commercial drivers would be good too,
but I'm not sure how legal that is.

Kris Jurka

On Tue, 28 Sep 2004, Kris Jurka wrote:

> It might be worthwhile to investigate how other JDBC drivers handle this.
> So far I've only looked at MySQL, but checking MaxDB and Firebird's driver
> source would be revealing.  Checking commercial drivers would be good too,
> but I'm not sure how legal that is.
>

So far I've checked MaxDB, Firebird, and Oracle.  All of them throw
an Exception saying the method is unimplemented.  This makes me even less
excited about throwing in a hack solution.  I was just assuming that
everyone else implemented this and we needed something to "keep up" with
portable applications.

Kris Jurka