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 "Nicholas L. Piasecki" <ni...@piasecki.name> on 2007/09/18 00:12:14 UTC
Do not use with SCOPE_IDENTITY and MSSQL?
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
Re: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by Sean Blakemore <se...@gmail.com>.
Add my vote also.. For reference, here is what I'm using and I believe this
represents the best option for now:
<selectKey property="id" type="post" resultClass="int">
SELECT cast(IDENT_CURRENT('tableName') as int) AS value
</selectKey>
On 9/18/07, Tony Selke <to...@gmail.com> wrote:
>
> You still should be using SCOPE_IDENTITY() to get your inserted key
> values. As mentioned, IDENT_CURRENT() will not restrain itself to the scope
> of the procedure/trigger being executed and you still get the potential for
> race conditions, etc. Granted, it is a smaller chance than if you were
> using @@IDENTITY because you are limiting yourself to one table instead of
> all tables, but in a case of a heavily inserted table it is still a
> possibility that should be avoided.
>
>
>
> We ran into this same issue a few months back and we had to do the
> "insert, set variable to SCOPE_IDENTITY, select variable back out to an int
> type result class" batch method to get around the problem. I would
> definitely vote for an update to make iBATIS use this function (I would have
> done it myself had we the slack int eh project schedule; isn't that always
> the way?).
>
>
>
> Tony
>
>
>
> *From:* Clough, Samuel (USPC.PRG.Atlanta) [mailto:
> Samuel_Clough@princetonrg.com]
> *Sent:* Tuesday, September 18, 2007 7:13 AM
> *To:* user-cs@ibatis.apache.org
> *Subject:* RE: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
>
>
>
> I wasn't aware of ident_current. That seems like the best solution.
>
>
>
> *From:* Sean Blakemore [mailto:sean.blakemore@gmail.com]
> *Sent:* Tuesday, September 18, 2007 7:10 AM
> *To:* user-cs@ibatis.apache.org
> *Subject:* Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
>
>
>
> The reason being that if for example you have a trigger which updates some
> audit table when you perform an insert on an entity table, with @@IDENTITY
> there is no scope and the returned identity will be /could be the new row in
> the audit table and not the new row in the entity table. SCOPE_IDENTITY does
> not have this problem, as the trigger would be out of scope of the current
> session. The midground between the two is IDENT_CURRENT('TableName') which
> like @@IDENTITY is not scoped to any session, however it is scoped to the
> provided table, somewhat aleviating the risk of the audit trigger issue.
>
> Sean
>
> On 9/18/07, *Clough, Samuel (USPC.PRG.Atlanta)* <
> Samuel_Clough@princetonrg.com> wrote:
>
> Like the original email mentioned, wasn't there an issue with @@IDENTITY
> that would make it somewhat unreliable? I don't remember specifics, but
> there was a reason I switched from it to SCOPE_IDENTITY a few years back.
> Perhaps it was just as issue in an older version of MS SQL.
>
>
>
> *From:* Gilles Bayon [mailto:ibatis.net@gmail.com]
> *Sent:* Tuesday, September 18, 2007 2:35 AM
> *To:* user-cs@ibatis.apache.org
> *Subject:* Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
>
>
>
> SCOPE_IDENTITY / @@IDENTITY
> Returns the last identity value inserted into an identity column in the
> same scope.
> A scope is a module: a stored procedure, trigger, function, or batch.
> Therefore, two statements are in the same scope if they are in the same
> stored procedure, function, or batch.
>
> SCOPE_IDENTITY and @@IDENTITY return the last identity values that are
> generated in any table in the current session.
> However, SCOPE_IDENTITY returns values inserted only within the current
> scope; @@IDENTITY is not limited to a specific scope.
>
> From http://msdn2.microsoft.com/en-us/library/ms190315.aspx
>
> So if you want to use the selectKey statement, use the @@IDENTITY.
>
> If you want you can make an entry in the wiki http://opensource.atlassian.com/confluence/oss/display/IBATIS/Home
>
>
> --
> Cheers,
> Gilles
>
> <a href=" http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>
> ------------------------------
>
> *Princeton Retirement Group, Inc - Important Terms *
>
> This E-mail is not intended for distribution to, or use by, any person or
> entity in any location where such distribution or use would be contrary to
> law or regulation, or which would subject Princeton Retirement Group, Inc.
> or any affiliate to any registration requirement within such location.
>
> This E-mail may contain privileged or confidential information or may
> otherwise be protected by work product immunity or other legal rules. No
> confidentiality or privilege is waived or lost by any mistransmission.
> Access, copying or re-use of information by non-intended or non-authorized
> recipients is prohibited. If you are not an intended recipient of this
> E-mail, please notify the sender, delete it and do not read, act upon,
> print, disclose, copy, retain or redistribute any portion of this E-mail.
>
> The transmission and content of this E-mail cannot be guaranteed to be
> secure or error-free. Therefore, we cannot represent that the information in
> this E-mail is complete, accurate, uncorrupted, timely or free of viruses,
> and Princeton Retirement Group, Inc. cannot accept any liability for E-mails
> that have been altered in the course of delivery. Princeton Retirement
> Group, Inc. reserves the right to monitor, review and retain all electronic
> communications, including E-mail, traveling through its networks and systems
> (subject to and in accordance with local laws). If any of your details are
> incorrect or if you no longer wish to receive mailings such as this by
> E-mail please contact the sender by reply E-mail.
> ------------------------------
>
>
>
Re: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by Gilles Bayon <ib...@gmail.com>.
Yes but the selectKey is too restrict, and doing what you think it's more
complicate to implement (just a wart).
The other solution is more open, you can do the same as the selectKey +
multiple update.
And so selectKey can be remove and the insert statement will be compliant
with other statement.
--
Cheers,
Gilles
<a href="http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>
RE: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by "Clough, Samuel (USPC.PRG.Atlanta)" <Sa...@princetonrg.com>.
At first glance, this sounds like a better suggestion. My guess is that
it would require co-ordination with the other iBatis versions (i.e.
Java, Ruby) to keep the api consistent.
From: Nicholas L. Piasecki [mailto:nicholas@piasecki.name]
Sent: Tuesday, September 18, 2007 1:29 PM
To: user-cs@ibatis.apache.org
Subject: RE: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
Just thinking out loud ... would it be possible to have a type="current"
in addition to the "post" and "pre" types on <selectKey>? The
MappedStatement.cs' ExecuteInsert() would somehow "merge" the
<selectKey> statement into the same batch as the rest of the insert.
This way the property mapping features of <selectKey> would be
consistent with the pre- and post- mechanisms that other databases use,
and SQL Server 2005 would be much less of a special case.
V/R,
Nicholas Piasecki
From: Gilles Bayon [mailto:ibatis.net@gmail.com]
Sent: Tuesday, September 18, 2007 1:20 PM
To: user-cs@ibatis.apache.org; tony@selkefamily.com
Subject: Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
Agree, a new API like sqlMap.Insert("InsertCategoryScope", category,
category) can do the work ?
It will be genric so you can even uptade one or more field of the result
object, any idea, suggestion ?
--
Cheers,
Gilles
<a href="http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>
--------------------------------------------------------
Princeton Retirement Group, Inc - Important Terms
This E-mail is not intended for distribution to, or use by, any person or entity in any location where such distribution or use would be contrary to law or regulation, or which would subject Princeton Retirement Group, Inc. or any affiliate to any registration requirement within such location.
This E-mail may contain privileged or confidential information or may otherwise be protected by work product immunity or other legal rules. No confidentiality or privilege is waived or lost by any mistransmission. Access, copying or re-use of information by non-intended or non-authorized recipients is prohibited. If you are not an intended recipient of this E-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute any portion of this E-mail.
The transmission and content of this E-mail cannot be guaranteed to be secure or error-free. Therefore, we cannot represent that the information in this E-mail is complete, accurate, uncorrupted, timely or free of viruses, and Princeton Retirement Group, Inc. cannot accept any liability for E-mails that have been altered in the course of delivery. Princeton Retirement Group, Inc. reserves the right to monitor, review and retain all electronic communications, including E-mail, traveling through its networks and systems (subject to and in accordance with local laws). If any of your details are incorrect or if you no longer wish to receive mailings such as this by E-mail please contact the sender by reply E-mail.
--------------------------------------------------------
RE: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by "Nicholas L. Piasecki" <ni...@piasecki.name>.
Just thinking out loud . would it be possible to have a type="current" in
addition to the "post" and "pre" types on <selectKey>? The
MappedStatement.cs' ExecuteInsert() would somehow "merge" the <selectKey>
statement into the same batch as the rest of the insert. This way the
property mapping features of <selectKey> would be consistent with the pre-
and post- mechanisms that other databases use, and SQL Server 2005 would be
much less of a special case.
V/R,
Nicholas Piasecki
From: Gilles Bayon [mailto:ibatis.net@gmail.com]
Sent: Tuesday, September 18, 2007 1:20 PM
To: user-cs@ibatis.apache.org; tony@selkefamily.com
Subject: Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
Agree, a new API like sqlMap.Insert("InsertCategoryScope", category,
category) can do the work ?
It will be genric so you can even uptade one or more field of the result
object, any idea, suggestion ?
--
Cheers,
Gilles
<a href="http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>
Re: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by Gilles Bayon <ib...@gmail.com>.
Agree, a new API like sqlMap.Insert("InsertCategoryScope", category,
category) can do the work ?
It will be genric so you can even uptade one or more field of the result
object, any idea, suggestion ?
--
Cheers,
Gilles
<a href="http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>
Re: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by Sean Blakemore <se...@gmail.com>.
I'd have to go with extending the API in an ideal world, if you could neatly
encapsulate the whole identity issue into the API and remove any related XML
config, personally that would feel far more intuitive.
On 9/18/07, Gilles Bayon <ib...@gmail.com> wrote:
>
> Not so strange (it's coherent with the selectKey execution) but good hack
> :-))
>
> --
> Cheers,
> Gilles
>
> <a href="http://www.amazon.com/gp/registry/6JCP7AORB0LE ">Wish List</a>
>
Re: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by Gilles Bayon <ib...@gmail.com>.
Not so strange (it's coherent with the selectKey execution) but good hack
:-))
--
Cheers,
Gilles
<a href="http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>
RE: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by "Nguyen, Tom" <To...@rels.info>.
Cool, I never knew about this until you guys brought it up. I use
stored proc for all my insert and parameter guid mapping works as I want
it.
I played around with the problem and here's another workaround..
Problem:
<insert id="IbatisDemo.Note.Create"
parameterClass="Note" resultClass="int">
INSERT INTO [dbo].[Notes] (
[Note]
) VALUES (
#Notes#
)
<selectKey resultClass="int" property="_id" type="post">
SELECT SCOPE_IDENTITY() -- @@IDENTITY FOR MSACCESS
</selectKey>
</insert>
Profiler Output:
exec sp_executesql N'INSERT INTO [dbo].[Notes] ( [Note]
) VALUES (
@param0 )',N'@param0 nvarchar(4)',@param0=N'test'
go
SELECT SCOPE_IDENTITY() -- @@IDENTITY FOR MSACCESS
Go
Workaround:
<insert id="IbatisDemo.Note.Create"
parameterClass="Note" resultClass="int">
SELECT 1
<selectKey resultClass="int" property="_id" type="post">
INSERT INTO [dbo].[Notes] (
[Note]
) VALUES (
#Notes#
)
SELECT SCOPE_IDENTITY() -- @@IDENTITY FOR MSACCESS
</selectKey>
</insert>
Profiler Output:
SELECT 1
go
exec sp_executesql N'INSERT INTO [dbo].[Notes] ( [Note]
) VALUES (
@param0 ) SELECT SCOPE_IDENTITY() --
@@IDENTITY FOR MSACCESS',N'@param0
nvarchar(4)',@param0=N'test'
go
Strange? But it works. Though with the workaround, you might have to
live with the dummy SELECT 1 statement.
Regards,
Tom Nguyen
Sr. Developer
tom.nguyen@rels.info <ma...@rels.info>
________________________________
From: Tony Selke [mailto:tony.selke@gmail.com]
Sent: Tuesday, September 18, 2007 12:14 PM
To: user-cs@ibatis.apache.org
Subject: RE: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
You are absolutely correct and that is exactly what I described as our
work-around. However, we should be able to use the <insert> tag as it
was intended and not need to fall back to the <statement> tag to work
around it. I am not suggesting that there isn't a way to do it, only
that the mechanism that used to work (<insert> tags with @@IDENTITY)
should be on the list of things to update to use the current mechanism
(SCOPE_IDENTITY()).
Tony
From: Gilles Bayon [mailto:ibatis.net@gmail.com]
Sent: Tuesday, September 18, 2007 12:48 PM
To: user-cs@ibatis.apache.org; Tony Selke
Subject: Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
You already have all at your finger, sample
Category category = new Category();
category.Name = "toto";
category.Guid = Guid.NewGuid();
sqlMap.QueryForObject ("InsertCategoryScope", category,
category);
with
<resultMap id="resultMapScope" class="Category">
<result property="Id" column="Category_ID"/>
</resultMap>
<statement id="InsertCategoryScope" parameterClass="Category"
resultMap="resultMapScope">
insert into Categories
(Category_Name, Category_Guid)
values
(#Name#, #Guid:UniqueIdentifier#);
select SCOPE_IDENTITY() as Category_ID
</statement>
--
Cheers,
Gilles
<a href=" http://www.amazon.com/gp/registry/6JCP7AORB0LE
<http://www.amazon.com/gp/registry/6JCP7AORB0LE> ">Wish List</a>
************************************************************************************
This e-mail message and any files transmitted herewith, are intended solely for the
use of the individual(s) addressed and may contain confidential, proprietary or
privileged information. If you are not the addressee indicated in this message
(or responsible for delivery of this message to such person) you may not review,
use, disclose or distribute this message or any files transmitted herewith. If you
receive this message in error, please contact the sender by reply e-mail and delete
this message and all copies of it from your system.
************************************************************************************
RE: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by Tony Selke <to...@gmail.com>.
You are absolutely correct and that is exactly what I described as our
work-around. However, we should be able to use the <insert> tag as it was
intended and not need to fall back to the <statement> tag to work around it.
I am not suggesting that there isn't a way to do it, only that the mechanism
that used to work (<insert> tags with @@IDENTITY) should be on the list of
things to update to use the current mechanism (SCOPE_IDENTITY()).
Tony
From: Gilles Bayon [mailto:ibatis.net@gmail.com]
Sent: Tuesday, September 18, 2007 12:48 PM
To: user-cs@ibatis.apache.org; Tony Selke
Subject: Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
You already have all at your finger, sample
Category category = new Category();
category.Name = "toto";
category.Guid = Guid.NewGuid();
sqlMap.QueryForObject ("InsertCategoryScope", category,
category);
with
<resultMap id="resultMapScope" class="Category">
<result property="Id" column="Category_ID"/>
</resultMap>
<statement id="InsertCategoryScope" parameterClass="Category"
resultMap="resultMapScope">
insert into Categories
(Category_Name, Category_Guid)
values
(#Name#, #Guid:UniqueIdentifier#);
select SCOPE_IDENTITY() as Category_ID
</statement>
--
Cheers,
Gilles
<a href=" <http://www.amazon.com/gp/registry/6JCP7AORB0LE>
http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>
Re: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by Gilles Bayon <ib...@gmail.com>.
You already have all at your finger, sample
Category category = new Category();
category.Name = "toto";
category.Guid = Guid.NewGuid();
sqlMap.QueryForObject("InsertCategoryScope", category,
category);
with
<resultMap id="resultMapScope" class="Category">
<result property="Id" column="Category_ID"/>
</resultMap>
<statement id="InsertCategoryScope" parameterClass="Category"
resultMap="resultMapScope">
insert into Categories
(Category_Name, Category_Guid)
values
(#Name#, #Guid:UniqueIdentifier#);
select SCOPE_IDENTITY() as Category_ID
</statement>
--
Cheers,
Gilles
<a href="http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>
RE: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by Tony Selke <to...@gmail.com>.
You still should be using SCOPE_IDENTITY() to get your inserted key values.
As mentioned, IDENT_CURRENT() will not restrain itself to the scope of the
procedure/trigger being executed and you still get the potential for race
conditions, etc. Granted, it is a smaller chance than if you were using
@@IDENTITY because you are limiting yourself to one table instead of all
tables, but in a case of a heavily inserted table it is still a possibility
that should be avoided.
We ran into this same issue a few months back and we had to do the "insert,
set variable to SCOPE_IDENTITY, select variable back out to an int type
result class" batch method to get around the problem. I would definitely
vote for an update to make iBATIS use this function (I would have done it
myself had we the slack int eh project schedule; isn't that always the
way?).
Tony
From: Clough, Samuel (USPC.PRG.Atlanta)
[mailto:Samuel_Clough@princetonrg.com]
Sent: Tuesday, September 18, 2007 7:13 AM
To: user-cs@ibatis.apache.org
Subject: RE: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
I wasn't aware of ident_current. That seems like the best solution.
From: Sean Blakemore [mailto:sean.blakemore@gmail.com]
Sent: Tuesday, September 18, 2007 7:10 AM
To: user-cs@ibatis.apache.org
Subject: Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
The reason being that if for example you have a trigger which updates some
audit table when you perform an insert on an entity table, with @@IDENTITY
there is no scope and the returned identity will be /could be the new row in
the audit table and not the new row in the entity table. SCOPE_IDENTITY does
not have this problem, as the trigger would be out of scope of the current
session. The midground between the two is IDENT_CURRENT('TableName') which
like @@IDENTITY is not scoped to any session, however it is scoped to the
provided table, somewhat aleviating the risk of the audit trigger issue.
Sean
On 9/18/07, Clough, Samuel (USPC.PRG.Atlanta)
<Sa...@princetonrg.com> wrote:
Like the original email mentioned, wasn't there an issue with @@IDENTITY
that would make it somewhat unreliable? I don't remember specifics, but
there was a reason I switched from it to SCOPE_IDENTITY a few years back.
Perhaps it was just as issue in an older version of MS SQL.
From: Gilles Bayon [mailto:ibatis.net@gmail.com]
Sent: Tuesday, September 18, 2007 2:35 AM
To: user-cs@ibatis.apache.org
Subject: Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
SCOPE_IDENTITY / @@IDENTITY
Returns the last identity value inserted into an identity column in the same
scope.
A scope is a module: a stored procedure, trigger, function, or batch.
Therefore, two statements are in the same scope if they are in the same
stored procedure, function, or batch.
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are
generated in any table in the current session.
However, SCOPE_IDENTITY returns values inserted only within the current
scope; @@IDENTITY is not limited to a specific scope.
>From http://msdn2.microsoft.com/en-us/library/ms190315.aspx
So if you want to use the selectKey statement, use the @@IDENTITY.
If you want you can make an entry in the wiki
http://opensource.atlassian.com/confluence/oss/display/IBATIS/Home
--
Cheers,
Gilles
<a href=" http://www.amazon.com/gp/registry/6JCP7AORB0LE
<http://www.amazon.com/gp/registry/6JCP7AORB0LE> ">Wish List</a>
_____
Princeton Retirement Group, Inc - Important Terms
This E-mail is not intended for distribution to, or use by, any person or
entity in any location where such distribution or use would be contrary to
law or regulation, or which would subject Princeton Retirement Group, Inc.
or any affiliate to any registration requirement within such location.
This E-mail may contain privileged or confidential information or may
otherwise be protected by work product immunity or other legal rules. No
confidentiality or privilege is waived or lost by any mistransmission.
Access, copying or re-use of information by non-intended or non-authorized
recipients is prohibited. If you are not an intended recipient of this
E-mail, please notify the sender, delete it and do not read, act upon,
print, disclose, copy, retain or redistribute any portion of this E-mail.
The transmission and content of this E-mail cannot be guaranteed to be
secure or error-free. Therefore, we cannot represent that the information in
this E-mail is complete, accurate, uncorrupted, timely or free of viruses,
and Princeton Retirement Group, Inc. cannot accept any liability for E-mails
that have been altered in the course of delivery. Princeton Retirement
Group, Inc. reserves the right to monitor, review and retain all electronic
communications, including E-mail, traveling through its networks and systems
(subject to and in accordance with local laws). If any of your details are
incorrect or if you no longer wish to receive mailings such as this by
E-mail please contact the sender by reply E-mail.
_____
RE: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by "Clough, Samuel (USPC.PRG.Atlanta)" <Sa...@princetonrg.com>.
I wasn't aware of ident_current. That seems like the best solution.
From: Sean Blakemore [mailto:sean.blakemore@gmail.com]
Sent: Tuesday, September 18, 2007 7:10 AM
To: user-cs@ibatis.apache.org
Subject: Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
The reason being that if for example you have a trigger which updates
some audit table when you perform an insert on an entity table, with
@@IDENTITY there is no scope and the returned identity will be /could be
the new row in the audit table and not the new row in the entity table.
SCOPE_IDENTITY does not have this problem, as the trigger would be out
of scope of the current session. The midground between the two is
IDENT_CURRENT('TableName') which like @@IDENTITY is not scoped to any
session, however it is scoped to the provided table, somewhat aleviating
the risk of the audit trigger issue.
Sean
On 9/18/07, Clough, Samuel (USPC.PRG.Atlanta)
<Sa...@princetonrg.com> wrote:
Like the original email mentioned, wasn't there an issue with @@IDENTITY
that would make it somewhat unreliable? I don't remember specifics, but
there was a reason I switched from it to SCOPE_IDENTITY a few years
back. Perhaps it was just as issue in an older version of MS SQL.
From: Gilles Bayon [mailto:ibatis.net@gmail.com]
Sent: Tuesday, September 18, 2007 2:35 AM
To: user-cs@ibatis.apache.org
Subject: Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
SCOPE_IDENTITY / @@IDENTITY
Returns the last identity value inserted into an identity column in the
same scope.
A scope is a module: a stored procedure, trigger, function, or batch.
Therefore, two statements are in the same scope if they are in the same
stored procedure, function, or batch.
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are
generated in any table in the current session.
However, SCOPE_IDENTITY returns values inserted only within the current
scope; @@IDENTITY is not limited to a specific scope.
>From http://msdn2.microsoft.com/en-us/library/ms190315.aspx
So if you want to use the selectKey statement, use the @@IDENTITY.
If you want you can make an entry in the wiki
http://opensource.atlassian.com/confluence/oss/display/IBATIS/Home
--
Cheers,
Gilles
<a href=" http://www.amazon.com/gp/registry/6JCP7AORB0LE
<http://www.amazon.com/gp/registry/6JCP7AORB0LE> ">Wish List</a>
_____
Princeton Retirement Group, Inc - Important Terms
This E-mail is not intended for distribution to, or use by, any person
or entity in any location where such distribution or use would be
contrary to law or regulation, or which would subject Princeton
Retirement Group, Inc. or any affiliate to any registration requirement
within such location.
This E-mail may contain privileged or confidential information or may
otherwise be protected by work product immunity or other legal rules. No
confidentiality or privilege is waived or lost by any mistransmission.
Access, copying or re-use of information by non-intended or
non-authorized recipients is prohibited. If you are not an intended
recipient of this E-mail, please notify the sender, delete it and do not
read, act upon, print, disclose, copy, retain or redistribute any
portion of this E-mail.
The transmission and content of this E-mail cannot be guaranteed to be
secure or error-free. Therefore, we cannot represent that the
information in this E-mail is complete, accurate, uncorrupted, timely or
free of viruses, and Princeton Retirement Group, Inc. cannot accept any
liability for E-mails that have been altered in the course of delivery.
Princeton Retirement Group, Inc. reserves the right to monitor, review
and retain all electronic communications, including E-mail, traveling
through its networks and systems (subject to and in accordance with
local laws). If any of your details are incorrect or if you no longer
wish to receive mailings such as this by E-mail please contact the
sender by reply E-mail.
_____
Re: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by Sean Blakemore <se...@gmail.com>.
The reason being that if for example you have a trigger which updates some
audit table when you perform an insert on an entity table, with @@IDENTITY
there is no scope and the returned identity will be /could be the new row in
the audit table and not the new row in the entity table. SCOPE_IDENTITY does
not have this problem, as the trigger would be out of scope of the current
session. The midground between the two is IDENT_CURRENT('TableName') which
like @@IDENTITY is not scoped to any session, however it is scoped to the
provided table, somewhat aleviating the risk of the audit trigger issue.
Sean
On 9/18/07, Clough, Samuel (USPC.PRG.Atlanta) <Sa...@princetonrg.com>
wrote:
>
> Like the original email mentioned, wasn't there an issue with @@IDENTITY
> that would make it somewhat unreliable? I don't remember specifics, but
> there was a reason I switched from it to SCOPE_IDENTITY a few years back.
> Perhaps it was just as issue in an older version of MS SQL.
>
>
>
> *From:* Gilles Bayon [mailto:ibatis.net@gmail.com]
> *Sent:* Tuesday, September 18, 2007 2:35 AM
> *To:* user-cs@ibatis.apache.org
> *Subject:* Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
>
>
>
> SCOPE_IDENTITY / @@IDENTITY
> Returns the last identity value inserted into an identity column in the
> same scope.
> A scope is a module: a stored procedure, trigger, function, or batch.
> Therefore, two statements are in the same scope if they are in the same
> stored procedure, function, or batch.
>
> SCOPE_IDENTITY and @@IDENTITY return the last identity values that are
> generated in any table in the current session.
> However, SCOPE_IDENTITY returns values inserted only within the current
> scope; @@IDENTITY is not limited to a specific scope.
>
> From http://msdn2.microsoft.com/en-us/library/ms190315.aspx
>
> So if you want to use the selectKey statement, use the @@IDENTITY.
>
> If you want you can make an entry in the wiki
> http://opensource.atlassian.com/confluence/oss/display/IBATIS/Home
>
> --
> Cheers,
> Gilles
>
> <a href=" http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>
> ------------------------------
> *
>
> Princeton Retirement Group, Inc - Important Terms
> *
>
> This E-mail is not intended for distribution to, or use by, any person or
> entity in any location where such distribution or use would be contrary to
> law or regulation, or which would subject Princeton Retirement Group, Inc.
> or any affiliate to any registration requirement within such location.
>
> This E-mail may contain privileged or confidential information or may
> otherwise be protected by work product immunity or other legal rules. No
> confidentiality or privilege is waived or lost by any mistransmission.
> Access, copying or re-use of information by non-intended or non-authorized
> recipients is prohibited. If you are not an intended recipient of this
> E-mail, please notify the sender, delete it and do not read, act upon,
> print, disclose, copy, retain or redistribute any portion of this E-mail.
>
> The transmission and content of this E-mail cannot be guaranteed to be
> secure or error-free. Therefore, we cannot represent that the information in
> this E-mail is complete, accurate, uncorrupted, timely or free of viruses,
> and Princeton Retirement Group, Inc. cannot accept any liability for E-mails
> that have been altered in the course of delivery. Princeton Retirement
> Group, Inc. reserves the right to monitor, review and retain all electronic
> communications, including E-mail, traveling through its networks and systems
> (subject to and in accordance with local laws). If any of your details are
> incorrect or if you no longer wish to receive mailings such as this by
> E-mail please contact the sender by reply E-mail.
> ------------------------------
>
RE: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by Carlos Peix <pe...@praxia.com.ar>.
Hi,
Suppose you have table1 (auto increment PK) with a insert trigger that inserts a
row in table2 (auto increment PK).
When you insert a row in table1, the trigger will insert a row in the table2.
@@IDENTITY (after the table1 insert) will return the key generated for table2,
not table1 as expected/desired.
SCOPE_IDENTITY() (after the table1 insert) will return the key generated for
table1 because the insert statement to table1 runs in a context and the trigger
that inserts in table2 runs in another.
Just as Gilles explained.
Carlos Peix
_____
From: Clough, Samuel (USPC.PRG.Atlanta) [mailto:Samuel_Clough@princetonrg.com]
Sent: Martes, 18 de Septiembre de 2007 07:19 a.m.
To: user-cs@ibatis.apache.org
Subject: RE: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
Like the original email mentioned, wasn't there an issue with @@IDENTITY that
would make it somewhat unreliable? I don't remember specifics, but there was a
reason I switched from it to SCOPE_IDENTITY a few years back. Perhaps it was
just as issue in an older version of MS SQL.
From: Gilles Bayon [mailto:ibatis.net@gmail.com]
Sent: Tuesday, September 18, 2007 2:35 AM
To: user-cs@ibatis.apache.org
Subject: Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
SCOPE_IDENTITY / @@IDENTITY
Returns the last identity value inserted into an identity column in the same
scope.
A scope is a module: a stored procedure, trigger, function, or batch. Therefore,
two statements are in the same scope if they are in the same stored procedure,
function, or batch.
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated
in any table in the current session.
However, SCOPE_IDENTITY returns values inserted only within the current scope;
@@IDENTITY is not limited to a specific scope.
>From http://msdn2.microsoft.com/en-us/library/ms190315.aspx
So if you want to use the selectKey statement, use the @@IDENTITY.
If you want you can make an entry in the wiki
http://opensource.atlassian.com/confluence/oss/display/IBATIS/Home
--
Cheers,
Gilles
<a href=" <http://www.amazon.com/gp/registry/6JCP7AORB0LE>
http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>
_____
Princeton Retirement Group, Inc - Important Terms
This E-mail is not intended for distribution to, or use by, any person or entity
in any location where such distribution or use would be contrary to law or
regulation, or which would subject Princeton Retirement Group, Inc. or any
affiliate to any registration requirement within such location.
This E-mail may contain privileged or confidential information or may otherwise
be protected by work product immunity or other legal rules. No confidentiality
or privilege is waived or lost by any mistransmission. Access, copying or re-use
of information by non-intended or non-authorized recipients is prohibited. If
you are not an intended recipient of this E-mail, please notify the sender,
delete it and do not read, act upon, print, disclose, copy, retain or
redistribute any portion of this E-mail.
The transmission and content of this E-mail cannot be guaranteed to be secure or
error-free. Therefore, we cannot represent that the information in this E-mail
is complete, accurate, uncorrupted, timely or free of viruses, and Princeton
Retirement Group, Inc. cannot accept any liability for E-mails that have been
altered in the course of delivery. Princeton Retirement Group, Inc. reserves the
right to monitor, review and retain all electronic communications, including
E-mail, traveling through its networks and systems (subject to and in accordance
with local laws). If any of your details are incorrect or if you no longer wish
to receive mailings such as this by E-mail please contact the sender by reply
E-mail.
_____
RE: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by "Clough, Samuel (USPC.PRG.Atlanta)" <Sa...@princetonrg.com>.
Like the original email mentioned, wasn't there an issue with @@IDENTITY
that would make it somewhat unreliable? I don't remember specifics, but
there was a reason I switched from it to SCOPE_IDENTITY a few years
back. Perhaps it was just as issue in an older version of MS SQL.
From: Gilles Bayon [mailto:ibatis.net@gmail.com]
Sent: Tuesday, September 18, 2007 2:35 AM
To: user-cs@ibatis.apache.org
Subject: Re: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
SCOPE_IDENTITY / @@IDENTITY
Returns the last identity value inserted into an identity column in the
same scope.
A scope is a module: a stored procedure, trigger, function, or batch.
Therefore, two statements are in the same scope if they are in the same
stored procedure, function, or batch.
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are
generated in any table in the current session.
However, SCOPE_IDENTITY returns values inserted only within the current
scope; @@IDENTITY is not limited to a specific scope.
>From http://msdn2.microsoft.com/en-us/library/ms190315.aspx
So if you want to use the selectKey statement, use the @@IDENTITY.
If you want you can make an entry in the wiki
http://opensource.atlassian.com/confluence/oss/display/IBATIS/Home
--
Cheers,
Gilles
<a href=" http://www.amazon.com/gp/registry/6JCP7AORB0LE
<http://www.amazon.com/gp/registry/6JCP7AORB0LE> ">Wish List</a>
--------------------------------------------------------
Princeton Retirement Group, Inc - Important Terms
This E-mail is not intended for distribution to, or use by, any person or entity in any location where such distribution or use would be contrary to law or regulation, or which would subject Princeton Retirement Group, Inc. or any affiliate to any registration requirement within such location.
This E-mail may contain privileged or confidential information or may otherwise be protected by work product immunity or other legal rules. No confidentiality or privilege is waived or lost by any mistransmission. Access, copying or re-use of information by non-intended or non-authorized recipients is prohibited. If you are not an intended recipient of this E-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute any portion of this E-mail.
The transmission and content of this E-mail cannot be guaranteed to be secure or error-free. Therefore, we cannot represent that the information in this E-mail is complete, accurate, uncorrupted, timely or free of viruses, and Princeton Retirement Group, Inc. cannot accept any liability for E-mails that have been altered in the course of delivery. Princeton Retirement Group, Inc. reserves the right to monitor, review and retain all electronic communications, including E-mail, traveling through its networks and systems (subject to and in accordance with local laws). If any of your details are incorrect or if you no longer wish to receive mailings such as this by E-mail please contact the sender by reply E-mail.
--------------------------------------------------------
Re: Do not use with SCOPE_IDENTITY and MSSQL?
Posted by Gilles Bayon <ib...@gmail.com>.
SCOPE_IDENTITY / @@IDENTITY
Returns the last identity value inserted into an identity column in the same
scope.
A scope is a module: a stored procedure, trigger, function, or batch.
Therefore, two statements are in the same scope if they are in the same
stored procedure, function, or batch.
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are
generated in any table in the current session.
However, SCOPE_IDENTITY returns values inserted only within the current
scope; @@IDENTITY is not limited to a specific scope.
>From http://msdn2.microsoft.com/en-us/library/ms190315.aspx
So if you want to use the selectKey statement, use the @@IDENTITY.
If you want you can make an entry in the wiki
http://opensource.atlassian.com/confluence/oss/display/IBATIS/Home
--
Cheers,
Gilles
<a href="http://www.amazon.com/gp/registry/6JCP7AORB0LE">Wish List</a>