ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Alon Hirsch" <alon.hir...@ebam.co.za>
Subject RE: SELECT @@IDENTITY vs SELECT SCOPE_IDENTITY
Date Tue, 20 May 2008 13:29:26 GMT
Thanx 


Alon Hirsch
Development Manager / Technical Architect

eBAM Systems - Johannesburg
Glenstar House
Cnr Northfield & Queens Square
Glenhazel, JHB 
Tel:  +27 11 719 2222 
Fax:  +27 11 719 2051 / +27 86 531 4396 
email:  alon.hirsch@ebam.co.za 

The information contained in this e-mail message and in the documents
attached herewith (hereinafter "the Message") is intended only for the
individuals or the entity named above and is intended to be
confidential.  The Message contains information sent by eBAM Systems.
The reading of the Message or any retention. copying, dissemination,
distribution, disclosure of the existence of the Message or of its
contents, or any other use of the Message or any part thereof, by anyone
other than the intended recipient is strictly prohibited.  If you
received this message and you are not the intended recipient or agent
responsible for the delivery of this message to the intended recipient,
please refrain from reading it and notify us immediately by telephone
[+27] (0)11 719-2222 or telefax [+27] (0)11 719-2051, so that we can
co-ordinate with you erasure of the Message.
Although this e-mail and its attachments are believed to be free of any
virus or other defect, it is the responsibility of the recipient to
ensure that they are virus free, and no responsibility is accepted by
this firm for any loss or damage arising from receipt or use thereof.

-----Original Message-----
From: Nicholas Piasecki [mailto:nicholas@piasecki.name] 
Sent: 20 May 2008 02:23 PM
To: user-cs@ibatis.apache.org
Subject: Re: SELECT @@IDENTITY vs SELECT SCOPE_IDENTITY
Importance: High

Don't use <selectKey>. Embed SELECT SCOPE_IDENTITY() AS value at the
bottom of your insert statement and use the returnClass.

<insert id="InsertSomething" parameterClass="Foo" resultClass="int">
     INSERT INTO Foos ( Name ) VALUES ( #Name# )
     SELECT SCOPE_IDENTITY() AS value
</insert>

I was involved in a long discussion about this before, but it has to do
with the way iBATIS issues the <selectKey> query. It's sent in a
separate batch, and MSSQL's SCOPE_IDENTITY() only works at the batch
level.

It's not really a bug, just an obscure difference between the way
similar functions for other databases work.

V/R,
Nicholas Piasecki

On May 20, 2008, at 3:07 AM, Alon Hirsch wrote:

> Hi,
>
> It seems that there is an issue using SELECT SCOPE_IDENTITY() in order

> to get the ID of an inserted record using SQL Server 2005.
> When using SCOPE_IDENTITY - the returned value is 0 instead of the 
> actual value returned from the SQL, but when using @@IDENTITY, the key

> is returned.
>
> Is this a bug or by design ?
> Is there any change that the system can be made to work with 
> SCOPE_IDENTITY as well ?
>
> Thanx,
> Alon


Mime
View raw message