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 <[EMAIL PROTECTED]> 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:
> [EMAIL PROTECTED]
> *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:[EMAIL PROTECTED]
> *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)* <
> [EMAIL PROTECTED]> 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:[EMAIL PROTECTED]
> *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.
> ------------------------------
>
>
>
