ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nicholas L. Piasecki" <nicho...@piasecki.name>
Subject Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
Date Mon, 17 Sep 2007 22:12:14 GMT
Hello all,

I'm just throwing this out there because I experienced a strange quirk with
SQL Server 2005, and we might want to clarify this in the documentation for
<selectKey> (forgive me if it's already there, I didn't see it).

iBATIS appears to issue the <selectKey> SQL in another batch. Unfortunately,
SCOPE_IDENTITY() is only valid for the current scope, not the current
transaction / connection / session / whatever. So in my case, using
SCOPE_IDENTITY() and <selectKey> always returns null.

The solution is to modify the insert query to look like the following,
having the insert statement itself have a return value and then manually
assigning it to a property (I figured this out by looking at some of iBATIS'
unit tests):

<insert id="SomeInsertStatement" parameterClass="string" resultClass="int">
  INSERT INTO Blah ( ColumnA ) VALUES ( #value# )
  SELECT SCOPE_IDENTITY() AS value
</insert>

@@IDENTITY and IDENT_CURRENT work with <selectKey>, but SCOPE_IDENTITY()
seems to be the preferred method these days (less prone to race conditions /
funky interactions with triggers and replication).

Could someone clarify that this is "the right way" with MSSQL? If so, I
humbly suggest that we document this somewhere. Thanks!

V/R,
Nicholas Piasecki


Mime
View raw message