ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nguyen, Tom" <Tom.Ngu...@rels.info>
Subject RE: Do not use <selectKey> with SCOPE_IDENTITY and MSSQL?
Date Tue, 18 Sep 2007 18:15:21 GMT
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 <mailto:tom.nguyen@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.
************************************************************************************

Mime
View raw message