Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (asf.osuosl.org: domain of larry.meadors@gmail.com
 designates 64.233.162.197 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=D0xVJB9c93mP+LyF95lIbOicuplFhTdOZqmkz/DeZqgQRu9BBS2InXuKRv+BPxjY8Z8Q/ux4d26U192382cvq+dJOKFR5jxTrTWOS31tHOrpxhWIvyLsZlEDaLEbjYTohDcuSjOAN42mPdlT7EGF/usuXsrPY+vuTkala1w96CM=
Message-ID: <a10a823c050818051053640e6a@mail.gmail.com>
Date: Thu, 18 Aug 2005 06:10:33 -0600
From: Larry Meadors <larry.meadors@gmail.com>
Reply-To: lmeadors@apache.org
To: user-java@ibatis.apache.org
Subject: Re: Getting auto-generated keys in mySql
In-Reply-To: <op.svoeq6vpsbbnoh@linux.site>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: 
 <42E7C401A4CBCD49829BB53DDD538BA401E19537@chmpgexgmail3.ic.ncs.com>
	 <op.svn7vdqisbbnoh@alanmini>
	 <a10a823c05081714468110f00@mail.gmail.com>
	 <op.svoeq6vpsbbnoh@linux.site>

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:
>=20
> 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?
>=20
> Alan.
>=20
> "Learn something NEW everyday"
>=20
>=20
> On Wed, 17 Aug 2005 22:46:19 +0100, Larry Meadors
> <larry.meadors@gmail.com> wrote:
>=20
> > 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. Finall=
y
> >> I
> >> stumbled upon
> >> the query where 'userId' is an auto-generated key in my mysql table. I=
f
> >> 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=3D"int" keyProperty=3D"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/
> >>
> >
>=20
>=20
>=20
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
>