Re: retrieving auto-generated keys on bulk insert

[Dane Foster]

<Paul wrote>
It's still not a JDBC issue.  The MySQL driver cannot give you what the
client/server protocol does not make available.

I think you'll have to work around this by issuing separate single-row
INSERT statements and collecting the aut-generated keys yourself.  Is
that a feasible alternative for your application?
</Paul wrote>

Not really,  I'm doing bulk inserts of up to 50K rows.  Fortunately, there is a
simple algorithm I can use to accomplish the same thing.



Dane Foster
Equity Technology Group, Inc.
http://www.equitytg.com
954.360.9800
----- Original Message -----
From: "Paul DuBois" <paul@stripped>
To: "Dane Foster" <dfoster@stripped>
Cc: <java@stripped>
Sent: Wednesday, January 08, 2003 11:50
Subject: Re: retrieving auto-generated keys on bulk insert


At 2:20 -0500 1/8/03, Dane Foster wrote:
>Paul DuBois wrote:
>
>"It certainly does.  The behavior you describe below is how INSERT is
>documented in the manual to work with respect to LAST_INSERT_ID()
>and multiple-row inserts -- that is, LAST_INSERT_ID() returns the
>*first* auto-generated ID value.
>
>This isn't a JDBD issue."
>
>I would like to point out that yes it is a JDBC issue because (the
>following is
>copied from the JDBC javadoc):
><copy>
>getGeneratedKeys
>public ResultSet getGeneratedKeys()throws SQLExceptionRetrieves any
>auto-generated keys created as a result of executing this Statement object. If
>this Statement object did not generate any keys, an empty ResultSet object is
>returned.
>
>
>     Returns:
>     a ResultSet object containing the auto-generated key(s) generated by the
>execution of this Statement object
>     Throws:
>     SQLException - if a database access error occurs
>     Since:
>     1.4
></copy>
>Notice that the word key is plural, meaning if a statement causes the creating
>of more than one key then the ResultSet object should return all
>keys, not just
>the first key.  So from the JDBC perspective the MySQL JDBC driver's
>implementation of the getGeneratedKeys method is not consistent with the JDBC
>documentation.
>
>Dane Foster
>Equity Technology Group, Inc.
>http://www.equitytg.com
>954.360.9800

It's still not a JDBC issue.  The MySQL driver cannot give you what the
client/server protocol does not make available.

I think you'll have to work around this by issuing separate single-row
INSERT statements and collecting the aut-generated keys yourself.  Is
that a feasible alternative for your application?