You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-cs@ibatis.apache.org by Alon Hirsch <al...@ebam.co.za> on 2008/05/20 09:07:10 UTC
SELECT @@IDENTITY vs SELECT SCOPE_IDENTITY
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
RE: SELECT @@IDENTITY vs SELECT SCOPE_IDENTITY
Posted by Alon Hirsch <al...@ebam.co.za>.
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
Re: SELECT @@IDENTITY vs SELECT SCOPE_IDENTITY
Posted by Nicholas Piasecki <ni...@piasecki.name>.
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