selectKey and identity question

["H.E. Sum" <ha...@yahoo.com>]

Hi, I have an insert statement which adds an object
into one table and a property of that object into
another table. I am using SQL Server with identity
inserts on both tables, however I want selectKey to
retrieve the key of the main object. Is there anyway
to accomplish this in the mapping (i.e specify where
the selectKey takes place), or do I need to execute
separate statements?

The map is similar to:

<insert id="InsertUser"	parameterClass="User">
      <selectKey property="Id" type="post"
resultClass="Int32">SELECT @@IDENTITY</selectKey>
      INSERT INTO fuser (
      lastname,
      firstname,
)
      VALUES (
      #LastName#,
      #FirstName#,
)
INSERT INTO group (userid, name) VALUES (#Id#,
#Group.Name#)
</insert>

This works in postgresql with sequences, because the
selectKey is a pre-select which gets the key before
the main insert, but with sql server, the selectKey is
a post-select, so #Id# doesn't get populated until the
property insert.

Thanks in advance.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: selectKey and identity question

[Ron Grabowski <ro...@yahoo.com>]

I don't think its possible to do it all in one statement. I'd break it
out into two.

--- "H.E. Sum" <ha...@yahoo.com> wrote:

> Hi, I have an insert statement which adds an object
> into one table and a property of that object into
> another table. I am using SQL Server with identity
> inserts on both tables, however I want selectKey to
> retrieve the key of the main object. Is there anyway
> to accomplish this in the mapping (i.e specify where
> the selectKey takes place), or do I need to execute
> separate statements?
> 
> The map is similar to:
> 
> <insert id="InsertUser"	parameterClass="User">
>       <selectKey property="Id" type="post"
> resultClass="Int32">SELECT @@IDENTITY</selectKey>
>       INSERT INTO fuser (
>       lastname,
>       firstname,
> )
>       VALUES (
>       #LastName#,
>       #FirstName#,
> )
> INSERT INTO group (userid, name) VALUES (#Id#,
> #Group.Name#)
> </insert>
> 
> This works in postgresql with sequences, because the
> selectKey is a pre-select which gets the key before
> the main insert, but with sql server, the selectKey is
> a post-select, so #Id# doesn't get populated until the
> property insert.
> 
> Thanks in advance.
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 
>