You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by "Barnett, Brian W." <br...@pearson.com> on 2005/08/17 22:43:05 UTC
Getting auto-generated keys in mySql
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.
****************************************************************************
Re: Getting auto-generated keys in mySql
Posted by Larry Meadors <la...@gmail.com>.
M$SQL to MySQL?! Good for you!
SELECT LAST_INSERT_ID()
Larry
PS: I'd use pgsql instead though. ;-)
On 8/17/05, Barnett, Brian W. <br...@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.
> ****************************************************************************
>
Re: Getting auto-generated keys in mySql
Posted by Larry Meadors <la...@gmail.com>.
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 <al...@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
> <la...@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 <al...@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.
> >> <br...@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/
>
Re: Getting auto-generated keys in mySql
Posted by Alan Hicks <al...@omega.ie>.
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
<la...@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 <al...@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.
>> <br...@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/
RE: Getting auto-generated keys in mySql
Posted by Meindert <me...@pastelebusiness.com>.
Hi all,
I forgot to use the mysql syntax and it still worked....
<insert id="insertStateAccess" parameterClass="StateAccess">
INSERT INTO StateAccess(SupervisorLevel, GroupId, EmpId, LayoutId)
VALUES(#supervisorLevel#, #groupId#, #empId#, #layoutId#)
<selectKey keyProperty="stateAccessId" resultClass="int">
SELECT @@IDENTITY AS ID
</selectKey>
</insert>
Populated state.getOwnerAccess.stateAccessId just fine !
Don't know why!
Using
mysql 4.1.8
ibatis 2.1.5 build# 582
I only noticed today that the SqlMapClient API methode insert() returns the
primairy key value of the newly inserted row.
MEINDERT HOVING
-----Original Message-----
From: Larry Meadors [mailto:larry.meadors@gmail.com]
Sent: 17 August 2005 11:46 PM
To: user-java@ibatis.apache.org
Subject: Re: Getting auto-generated keys in mySql
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 <al...@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.
> <br...@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/
>
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.10/73 - Release Date: 15/08/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.12/75 - Release Date: 17/08/2005
Re: Getting auto-generated keys in mySql
Posted by Larry Meadors <la...@gmail.com>.
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 <al...@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.
> <br...@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/
>
Re: Getting auto-generated keys in mySql
Posted by Alan Hicks <al...@omega.ie>.
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.
<br...@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/