|
Home > Archive > BizTalk Server General > October 2004 > Updategram inserts when DB lookup fails
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 |
Updategram inserts when DB lookup fails
|
|
| Lars W. Andersen 2004-10-27, 7:46 am |
| Hi,
I could use some advice on an issue I have. I need to introduce some error
handling to an interface.
The scenario is:
I receive a message that I want to update or insert into an SQL table. For
this I use the updategram, and that works fine.
In my map I do a DB lookup to decide whether I should create an insert or an
update statement. I then use the value mapper to decide if I should create
the <before> part of the updategram, along with the result of my DB lookup.
My problem is:
This worked really well on my testbox, but as soon as I let it loose the
harsh realities showed me that if the DB lookup failed, (DB unavailable or
other proiblem) I end up inserting a new (duplicate) record into the table,
as the updategram/value mapper didnt create the <before> tag and I wasnt
able to state the "key" in the <before> elements.
So my question is now how I should design this. Should I move the DB lookup
out of the map and into a separate receive port in an orchestration? This
would enable me to use the read retry on the SQL receive port, and if that
fails the exception handling in an orchestration. That is the only thing I
can think of right now. Anybody have any other suggestions?
I might also write the DB lookup error into a new element. Promote it as a
property and when it exists move it through an orchestration that does some
error handling? But what should that error do.
regards
Lars
| |
| Matt Meleski 2004-10-27, 7:46 am |
| Lars,
Yes, using a database Error Return functoid in your map is definitely an
option,
but yes I think this is a poor design. I would be much more comfortable
using a receive port.
Also don't rule out using your own custom .Net component that uses ADO.NET
to retrieve the info from the database. If the retrieved data needs to be in
an XML format, then you can use the XML EXPLICIT option(giving you complete
control on how the retrieved XML will look) in your select
statement. This XML can be used to populate a message inside your
Ochestration. In the Sql Adapter you are stuck with XML AUTO. If your custom
ADO.NET code fails due to a Sql error, you can trap the error in the
Orchestration and retry.
Matt
"Lars W. Andersen" wrote:
> Hi,
>
> I could use some advice on an issue I have. I need to introduce some error
> handling to an interface.
>
> The scenario is:
> I receive a message that I want to update or insert into an SQL table. For
> this I use the updategram, and that works fine.
>
> In my map I do a DB lookup to decide whether I should create an insert or an
> update statement. I then use the value mapper to decide if I should create
> the <before> part of the updategram, along with the result of my DB lookup.
>
> My problem is:
> This worked really well on my testbox, but as soon as I let it loose the
> harsh realities showed me that if the DB lookup failed, (DB unavailable or
> other proiblem) I end up inserting a new (duplicate) record into the table,
> as the updategram/value mapper didnt create the <before> tag and I wasnt
> able to state the "key" in the <before> elements.
>
> So my question is now how I should design this. Should I move the DB lookup
> out of the map and into a separate receive port in an orchestration? This
> would enable me to use the read retry on the SQL receive port, and if that
> fails the exception handling in an orchestration. That is the only thing I
> can think of right now. Anybody have any other suggestions?
>
> I might also write the DB lookup error into a new element. Promote it as a
> property and when it exists move it through an orchestration that does some
> error handling? But what should that error do.
>
> regards
> Lars
>
>
>
>
| |
| Lars W. Andersen 2004-10-27, 5:49 pm |
| Hi Matt,
thanks for your feedback. I'll give the orchestration a shot and see how far
i get with the exception handling there, and maybe some funky workflow. Just
to get the excercise. If it fails I'll go the code way. Didnt even think of
that  Been doing BizTalk too long I guess ... haha.
regards
Lars
"Matt Meleski" <MattMeleski@discussions.microsoft.com> wrote in message
news:9B74A88C-1485-43C0-BAAF-E4FC33A86702@microsoft.com...
> Lars,
>
> Yes, using a database Error Return functoid in your map is definitely an
> option,
> but yes I think this is a poor design. I would be much more comfortable
> using a receive port.
> Also don't rule out using your own custom .Net component that uses ADO.NET
> to retrieve the info from the database. If the retrieved data needs to be
in
> an XML format, then you can use the XML EXPLICIT option(giving you
complete
> control on how the retrieved XML will look) in your select
> statement. This XML can be used to populate a message inside your
> Ochestration. In the Sql Adapter you are stuck with XML AUTO. If your
custom[vbcol=seagreen]
> ADO.NET code fails due to a Sql error, you can trap the error in the
> Orchestration and retry.
>
> Matt
>
> "Lars W. Andersen" wrote:
>
error[vbcol=seagreen]
For[vbcol=seagreen]
or an[vbcol=seagreen]
create[vbcol=seagreen]
lookup.[vbcol=seagreen]
or[vbcol=seagreen]
table,[vbcol=seagreen]
lookup[vbcol=seagreen]
This[vbcol=seagreen]
that[vbcol=seagreen]
thing I[vbcol=seagreen]
a[vbcol=seagreen]
some[vbcol=seagreen]
| |
| Matt Meleski 2004-10-28, 7:48 am |
| Lars,
Just to be complete, here a blog entry on Handling an Error in Orchestration
with the SQL Adapter . Sounds like this has to be fixed. Therefore using
a custom component using ADO.NET may be the only option at the moment, to
catch the database errors in the orchestration.
http://dallas.sark.com/SarkBlog/mho.../10/22/442.aspx
"Lars W. Andersen" wrote:
> Hi Matt,
>
> thanks for your feedback. I'll give the orchestration a shot and see how far
> i get with the exception handling there, and maybe some funky workflow. Just
> to get the excercise. If it fails I'll go the code way. Didnt even think of
> that Been doing BizTalk too long I guess ... haha.
>
> regards
> Lars
>
> "Matt Meleski" <MattMeleski@discussions.microsoft.com> wrote in message
> news:9B74A88C-1485-43C0-BAAF-E4FC33A86702@microsoft.com...
> in
> complete
> custom
> error
> For
> or an
> create
> lookup.
> or
> table,
> lookup
> This
> that
> thing I
> a
> some
>
>
>
| |
| Lars W. Andersen 2004-10-28, 5:49 pm |
| One more thing that needs to be fixed with orchestrations :-/
Thanks for the link Matt.
Lars
"Matt Meleski" <MattMeleski@discussions.microsoft.com> wrote in message
news:80D0397D-DF2B-468C-B641-DAD2DEFBD2A3@microsoft.com...
> Lars,
>
> Just to be complete, here a blog entry on Handling an Error in
Orchestration[vbcol=seagreen]
> with the SQL Adapter . Sounds like this has to be fixed. Therefore using
> a custom component using ADO.NET may be the only option at the moment, to
> catch the database errors in the orchestration.
>
> http://dallas.sark.com/SarkBlog/mho.../10/22/442.aspx
>
> "Lars W. Andersen" wrote:
>
far[vbcol=seagreen]
Just[vbcol=seagreen]
of[vbcol=seagreen]
an[vbcol=seagreen]
comfortable[vbcol=seagreen]
ADO.NET[vbcol=seagreen]
be[vbcol=seagreen]
some[vbcol=seagreen]
table.[vbcol=seagreen]
insert[vbcol=seagreen]
the[vbcol=seagreen]
unavailable[vbcol=seagreen]
wasnt[vbcol=seagreen]
DB[vbcol=seagreen]
if[vbcol=seagreen]
it as[vbcol=seagreen]
does[vbcol=seagreen]
|
|
|
|
|