Brandon Goodin: thanks for the explanation I was worried about my
practice to do same.
Example using hsqldb I have:
<insert id="insertPerson" parameterClass="Person" >
INSERT INTO PERSONS
(FIRSTNAME, LASTNAME)
VALUES (#firstName#, #lastName#)
<selectKey resultClass="int" keyProperty="id">
CALL IDENTITY()
</selectKey>
</insert>
wich return the inserted auto-incremented (identity) key for column 'id'
key = (Integer) sqlMap.insert("insertPerson", newPerson);
replacing 'CALL IDENTITY' with select 'LAST_INSERT_ID()' should work for MySql
Thanks for the clarification
jfz.
On Sun, 27 Mar 2005 08:47:43 -0700, Brandon Goodin
<[EMAIL PROTECTED]> wrote:
> I don't think you need to worry about LAST_INSERT_ID so long as you
> have the same connection that inserted it. With IBatis this is not a
> problem.
>
> "The last ID that was generated is maintained in the server on a
> per-connection basis. This means the value the function returns to a
> given client is the most recent AUTO_INCREMENT value generated by that
> client. The value cannot be affected by other clients, even if they
> generate AUTO_INCREMENT values of their own. This behavior ensures
> that you can retrieve your own ID without concern for the activity of
> other clients, and without the need for locks or transactions."
>
> Full text from manual is below.
>
> Brandon
>
> From the MySQL manual:
>
> LAST_INSERT_ID() , LAST_INSERT_ID(expr)
>
> Returns the last automatically generated value that was inserted into
> an AUTO_INCREMENT column.
>
> mysql> SELECT LAST_INSERT_ID();
> -> 195
>
> The last ID that was generated is maintained in the server on a
> per-connection basis. This means the value the function returns to a
> given client is the most recent AUTO_INCREMENT value generated by that
> client. The value cannot be affected by other clients, even if they
> generate AUTO_INCREMENT values of their own. This behavior ensures
> that you can retrieve your own ID without concern for the activity of
> other clients, and without the need for locks or transactions.
>
> The value of LAST_INSERT_ID() is not changed if you update the
> AUTO_INCREMENT column of a row with a non-magic value (that is, a
> value that is not NULL and not 0).
>
> If you insert many rows at the same time with an insert statement,
> LAST_INSERT_ID() returns the value for the first inserted row. The
> reason for this is to make it possible to easily reproduce the same
> INSERT statement against some other server.
>
> On Sun, 27 Mar 2005 08:55:15 -0500, John Fereira <[EMAIL PROTECTED]> wrote:
> > At 01:58 PM 3/23/2005 +0000, James, Steven wrote:
> > >hi steven
> > >
> > >it was actually $$ not ##.
> > >this works for me it will also get the last insert key change to suit db
> > >ie @@identity sqlserver
> > >
> > ><insert id="test" parameterClass="string">
> > > $value$
> > > <selectKey resultClass="int">
> > > select LAST_INSERT_ID()
> > > </selectKey>
> > > </insert>
> >
> > You might want to be careful about using the LAST_INSERT_ID(). That
> > function returns the last auto_incremented value maintained by the
> > server. In a multiuser environment their is the possibility of a race
> > condition. The function is also not portable.
> >
> > As an alternative I create a Sequence table containing "name, id"
> > fields. When an insert to a table is performed, first query the sequence
> > table to get the last id, use it for the value for the primary key (don't
> > use an auto-increment value), then increment the value after the insert
> > succeeds.
> > John Fereira
> > [EMAIL PROTECTED]
> > Ithaca, NY
> >
> >
>
