ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Larry Meadors <larry.mead...@gmail.com>
Subject Re: Getting auto-generated keys in mySql
Date Thu, 18 Aug 2005 12:10:33 GMT
I believe that this method is "connection-aware" - so it returns the
last id generated by this connection.

On a related note, @@IDENTITY is not always safe in M$SQL, either. :-)

If you have an insert that has a trigger that does another insert, it
returns the second inserted key. The SCOPE_IDENTITY() database
function does what you want in either case...

Larry


On 8/17/05, Alan Hicks <alan.hicks@omega.ie> wrote:
> 
> Was not aware of this Larry -Thanks. Will try your solution.
> Just a thought. If you do use 'SELECT LAST_INSERT_ID()' and
> two tables have been auto-incremented which ID does it return?
> 
> Alan.
> 
> "Learn something NEW everyday"
> 
> 
> On Wed, 17 Aug 2005 22:46:19 +0100, Larry Meadors
> <larry.meadors@gmail.com> wrote:
> 
> > NO! That is really, really not thread safe!
> >
> > Imagine what happens when 2 ppl do inserts at the same time...
> >
> > [insert #1][insert #2][query #1][query #2]
> >
> > Both [query #1] and [query #2] return the same id.
> >
> > Ooops! kaboom!
> >
> > Larry
> >
> >
> > On 8/17/05, Alan Hicks <alan.hicks@omega.ie> wrote:
> >> Hi Brian,
> >>
> >> I had the same problem and could not find any working examples. Finally
> >> I
> >> stumbled upon
> >> the query where 'userId' is an auto-generated key in my mysql table. If
> >> you replace the
> >> query you have with this one you should be in business.
> >>
> >>
> >> SELECT MAX(userId) FROM tb_user
> >>
> >>
> >> Hope this helps.
> >>
> >> Alan.
> >>
> >>
> >>
> >> On Wed, 17 Aug 2005 21:43:05 +0100, Barnett, Brian W.
> >> <brian.barnett@pearson.com> wrote:
> >>
> >> > We've been using SQL Server and are moving to mySQL. Can somebody
> >> tell me
> >> > how to do this for mySql:
> >> >
> >> > <selectKey resultClass="int" keyProperty="id">
> >> >       SELECT @@IDENTITY AS id
> >> > </selectKey>
> >> >
> >> > TIA,
> >> > Brian Barnett
> >> >
> >> >
> >> ****************************************************************************
> >> > This email may contain confidential material.
> >> > If you were not an intended recipient,
> >> > Please notify the sender and delete all copies.
> >> > We may monitor email to and from our network.
> >> >
> >> ****************************************************************************
> >> >
> >> >
> >> >
> >>
> >>
> >>
> >> --
> >> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
> >>
> >
> 
> 
> 
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
>

Mime
View raw message