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
