|
Home > Archive > BizTalk Server General > March 2006 > Returning autogenerated id after inserting data (Updategram)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Returning autogenerated id after inserting data (Updategram)
|
|
| Sudhir Darbha 2006-03-21, 5:54 pm |
| Hi all,
I am using Updategram(Insert) to insert data into a table whose
primary key is auto-generated by the system. I would like to be able to read
this id from the response after updategram is done inserting. I read this
article on MSDN :
http://msdn.microsoft.com/library/d...768ff245df1.asp
this one talks about various internals of using SQL adapter. But it did talk
about using "updg:returnid" for returning the auto generated id, and
unfortunately the there was no further documentation on how to do this.
I am a total newbie to BizTalk, slowly trying to pick up! So is there anyone
out there who had to work with data returned from an updategram and use it
for some other processing inside the orchestration.
Any input is appreciated,
Thanks,
Sudhir.
| |
| Greg Forsythe 2006-03-21, 5:54 pm |
| For further documentation on updg:returnid you need to read the SQLXML3.0
documentation
Greg
"Sudhir Darbha" <SudhirDarbha@discussions.microsoft.com> wrote in message
news:6B32B93D-643F-49A1-9FBE-BEE961AA93A6@microsoft.com...
> Hi all,
> I am using Updategram(Insert) to insert data into a table whose
> primary key is auto-generated by the system. I would like to be able to
> read
> this id from the response after updategram is done inserting. I read this
> article on MSDN :
> http://msdn.microsoft.com/library/d...768ff245df1.asp
> this one talks about various internals of using SQL adapter. But it did
> talk
> about using "updg:returnid" for returning the auto generated id, and
> unfortunately the there was no further documentation on how to do this.
> I am a total newbie to BizTalk, slowly trying to pick up! So is there
> anyone
> out there who had to work with data returned from an updategram and use it
> for some other processing inside the orchestration.
>
> Any input is appreciated,
> Thanks,
> Sudhir.
| |
| Sudhir Darbha 2006-03-21, 5:54 pm |
| Greg,
Thanks for your reply. I did read the documentation on "updg:returnid" and
understood how it returns the data. But in a Biztalk orchestration, how can I
read this returnid from the response. It looks to me that the updategram is
always returning a static response containing a "Success" element in it. Is
there anyway you can replace the success with updg:returnid and if so how can
u link it with the request?
Thanks again,
sudhir.
"Greg Forsythe" wrote:
> For further documentation on updg:returnid you need to read the SQLXML3.0
> documentation
>
> Greg
>
> "Sudhir Darbha" <SudhirDarbha@discussions.microsoft.com> wrote in message
> news:6B32B93D-643F-49A1-9FBE-BEE961AA93A6@microsoft.com...
>
>
>
| |
| Greg Forsythe 2006-03-21, 5:54 pm |
| You need to modify the updategram schema.
You add the updg:at-identity="x" and updg:returnid="x" attributes as
appropriate on the message you send.
You add a <returnid> and child <x> tag to the result schema
You can make the <x> tag a distinguished field or use the xpath function to
retrieve the value of x.
Greg
"Sudhir Darbha" <SudhirDarbha@discussions.microsoft.com> wrote in message
news:79130602-B9D5-4264-8F75-AB0A056E18B3@microsoft.com...[vbcol=seagreen]
> Greg,
> Thanks for your reply. I did read the documentation on "updg:returnid" and
> understood how it returns the data. But in a Biztalk orchestration, how
> can I
> read this returnid from the response. It looks to me that the updategram
> is
> always returning a static response containing a "Success" element in it.
> Is
> there anyway you can replace the success with updg:returnid and if so how
> can
> u link it with the request?
>
> Thanks again,
> sudhir.
>
> "Greg Forsythe" wrote:
>
| |
| Eric Stott 2006-03-22, 2:48 am |
| The instructions on how to do this are at the following location:
http://msdn.microsoft.com/library/d...768ff245df1.asp
Eric Stott
http://stottcreations.com/blog
"Greg Forsythe" <greg.forsythe@gmail.com> wrote in message
news:u$xRoESTGHA.1728@TK2MSFTNGP11.phx.gbl...
> You need to modify the updategram schema.
> You add the updg:at-identity="x" and updg:returnid="x" attributes as
> appropriate on the message you send.
> You add a <returnid> and child <x> tag to the result schema
>
> You can make the <x> tag a distinguished field or use the xpath function
> to retrieve the value of x.
>
> Greg
>
> "Sudhir Darbha" <SudhirDarbha@discussions.microsoft.com> wrote in message
> news:79130602-B9D5-4264-8F75-AB0A056E18B3@microsoft.com...
>
>
| |
| Sudhir Darbha 2006-03-25, 11:37 am |
| I took the help of this article to create a "updg:at-identity" for
propogating parentid's to child tables. But this article leaves a note about
"returnid" but didn't explain how to use it inside a Biztalk schema editor
and mapper.
Here's a list of questions I would like to ask that might help to find an
answer:
1. Should I add the "updg:returnid" attribute to the response schema? My
response schema looks like this:
OutOrder <-- root
Success <-- child record
updg:returnid <-- does the return id go here?
2. In BizTalk Mapper, we have a source and destination schema right! Here
the destination schema is nothing but request schema for inserting data. The
MSDN article clearly explains how to map the parent id(OrderID) to
childtable's (OrderId) using the "string concatenate" functiod. But here, we
don't see any response schema. So how should I relate the newly generated
OrderId to the returnid? in other words I'm not sure how to do this
graphically.
Thanks,
Sudhir.
"Eric Stott" wrote:
> The instructions on how to do this are at the following location:
>
> http://msdn.microsoft.com/library/d...768ff245df1.asp
>
> Eric Stott
> http://stottcreations.com/blog
>
> "Greg Forsythe" <greg.forsythe@gmail.com> wrote in message
> news:u$xRoESTGHA.1728@TK2MSFTNGP11.phx.gbl...
>
>
>
| |
| Greg Forsythe 2006-03-25, 11:37 am |
| Unfortunately, I was wrong about getting the Identity returned by the SQL
Adapter when using an updategram.
This note in the whitepaper had me confused:
"Note: This example does not use the returnid attribute. You can use this
attribute to retrieve the auto-generated value from SQL Server if you need
it."
After a lot of testing, I discovered that the SQL adapter always returns a
hard coded string in response to an updategram.
the string is <Success/>
Option 1. Use a stored procedure.
You should be able to send an Xml document as an ntext parameter, use
OPENXML in the stored procedure and return @@IDENTITY.
Option 2. Write your own SQL Adapter
Option 3. One that I have used. Add an extra column to the table, make it a
unique key and populate it with a Biztalk derived GUID - possibly the
OrchestrationID. This can be used for correlation if you need to reference
this database object later in your orchestration
Greg
"Sudhir Darbha" <SudhirDarbha@discussions.microsoft.com> wrote in message
news:C4F592BE-6684-479B-8403-8B030997C4FE@microsoft.com...[vbcol=seagreen]
>I took the help of this article to create a "updg:at-identity" for
> propogating parentid's to child tables. But this article leaves a note
> about
> "returnid" but didn't explain how to use it inside a Biztalk schema editor
> and mapper.
> Here's a list of questions I would like to ask that might help to find an
> answer:
> 1. Should I add the "updg:returnid" attribute to the response schema? My
> response schema looks like this:
> OutOrder <-- root
> Success <-- child record
> updg:returnid <-- does the return id go here?
> 2. In BizTalk Mapper, we have a source and destination schema right! Here
> the destination schema is nothing but request schema for inserting data.
> The
> MSDN article clearly explains how to map the parent id(OrderID) to
> childtable's (OrderId) using the "string concatenate" functiod. But here,
> we
> don't see any response schema. So how should I relate the newly generated
> OrderId to the returnid? in other words I'm not sure how to do this
> graphically.
>
> Thanks,
> Sudhir.
>
> "Eric Stott" wrote:
>
| |
| Sudhir Darbha 2006-03-25, 11:37 am |
| Greg,
Thank you very much for your reply. Option 3 seems to be a fit for me
and I'm doing something similar to this. Option 1 (sproc) is kind of ruled
out for me since I have around 150 columns in different tables that need to
be inserted and it seems to me that Updategram required less maintenance
compared to sproc like whenever a column name changes. (correct me if i'm
wrong, i'm still a new BizTalker:-)
I have one question though about Option 3. In the mapper, I used a script
functoid to return a new Guid value (System.Guid.NewGuid().ToString()) and
assigned it to that extra column. Now in my response schema, I have a
"returnid" field element which I promoted as a "Distinguished Field". Now
inorder to assign the guid value to this returnid I used "MessageAssignment"
in my orchestration in which the expression looks like :
ResponseSchema.ReturnId = RequestSchema.RootElement.SomeElement.ExtraColID;
Do u think this is a preferred way of assigning the values to response
schema's fields? Just wanted to know the common practice here!
Thanks for the inputs!
../Sudhir
Option 3 (creating an extra column and creating a GUID and assigning the
value in BizTalk Mapper)
"Greg Forsythe" wrote:
> Unfortunately, I was wrong about getting the Identity returned by the SQL
> Adapter when using an updategram.
> This note in the whitepaper had me confused:
>
> "Note: This example does not use the returnid attribute. You can use this
> attribute to retrieve the auto-generated value from SQL Server if you need
> it."
>
> After a lot of testing, I discovered that the SQL adapter always returns a
> hard coded string in response to an updategram.
> the string is <Success/>
>
> Option 1. Use a stored procedure.
> You should be able to send an Xml document as an ntext parameter, use
> OPENXML in the stored procedure and return @@IDENTITY.
>
> Option 2. Write your own SQL Adapter
> Option 3. One that I have used. Add an extra column to the table, make it a
> unique key and populate it with a Biztalk derived GUID - possibly the
> OrchestrationID. This can be used for correlation if you need to reference
> this database object later in your orchestration
>
> Greg
>
> "Sudhir Darbha" <SudhirDarbha@discussions.microsoft.com> wrote in message
> news:C4F592BE-6684-479B-8403-8B030997C4FE@microsoft.com...
>
>
>
|
|
|
|
|