How an Upsert Error Looks in the XML

This forum allows users to post and respond to "How Do I Do ....." questions. The information contained in this forum has not been validated by K-Rise Systems and, as such, K-Rise Systems cannot guarantee the accuracy of the information.
Post Reply
CathyC
Posts: 472
Joined: November 16th, 2021, 11:15 am
Contact:

How an Upsert Error Looks in the XML

Unread post by CathyC »

I was testing and thought this was interesting.
image.png
image.png (5.65 KiB) Viewed 1503 times
image.png
image.png (5.65 KiB) Viewed 1503 times
image.png
Notice that when the upsert is successful, it adds another Result to the Output.
When it errors, it adds another exception node with one Result. Is this because each exception can have multiple Results and to keep them separate from a previous exception then "Exception" is the repeating node?
word count: 73

Tags:
CathyC
Posts: 472
Joined: November 16th, 2021, 11:15 am
Contact:

Re: How an Upsert Error Looks in the XML

Unread post by CathyC »

Also interesting is that there are 4 Info nodes. so whether it is successful or in error, it creates an info node.
image.png

So here is the output i'm trying to create from this output:
ParentId AB-10158083 and ChildId AB-10158082 and RelationType BillingLocation Inserted/Updated successfully in AddressBookHierarchy table.
OR
Failed to Insert/Update ParentId AB-10158083 and ChildId AB-10158082 and RelationType BillingLocation to AddressBookHierarchy table: Incorrect syntax near '='.

But looking at what is returned in the exception, it would be hard to match up the exception to know which 2 failed and which 2 succeeded.

The ParentId and ChildId are parameters in the upsert. they are returned in the Info Nodes and the in the Output nodes. So if they arent in the output, I know the ones from the Info nodes that are not also found in the Outputs are in the exceptions.

Should this be easier somehow with EASYProcess? Maybe an index returned?

Attached is the full xml of the service
Attachments
UpsertAddressBookHierarchy Test XML.txt
(45.05 KiB) Downloaded 165 times
UpsertAddressBookHierarchy Test XML.txt
(45.05 KiB) Downloaded 165 times
word count: 167
CathyC
Posts: 472
Joined: November 16th, 2021, 11:15 am
Contact:

Re: How an Upsert Error Looks in the XML

Unread post by CathyC »

Thats the logic I ended up using. but the problem I ran into was that if there are multiple failures, there is not a good way for me to tell which failure belongs to which attempted Info node.

Maybe out of 4 attempts, 1 and 3 failed. I would need logic to say:

set count = 0
for each of the info nodes, check the output node. does it match? if yes, call it a success. If no, increment the count. look for that indexed exception node.

This is kind hard to do though and it would be easier if the info result had an id or something that matched a value in the exception result.

EDIT: so I found the way to do this. The whole query is returned in the Exception Result. its just recognized XML to EASYProcess. My solution is a workaround:
image.png
Feature Suggestion: In the Exception Node of an Upsert and possibly other db workshop services, can the "ServiceXml" node be recognized XML to EASYProcess for matching purposes?
word count: 171
CathyC
Posts: 472
Joined: November 16th, 2021, 11:15 am
Contact:

Re: How an Upsert Error Looks in the XML

Unread post by CathyC »

Heres the alternative I went for
image.png
word count: 6
CathyC
Posts: 472
Joined: November 16th, 2021, 11:15 am
Contact:

Re: How an Upsert Error Looks in the XML

Unread post by CathyC »

Update:

The XML looks different if the Upsert is Inserting vs Updating, so my logic actually didnt work. In the case of upserting, there are no parameters.
Heres the entire Result for the Upsert:


<Result SelectOutput="False">
<NoOfRecordsUpdated>1</NoOfRecordsUpdated>
<Query>update EP_Platform_DV.Customer_EASYCommerceMVC_Data.AddressBookHierarchy RelationType = ShippingLocation,Imported = True,UpdatedOn = 2023-06-17 03:13:33.062 WHERE ParentAddressId = AB-10175166 AND ChildAddressId = AB-10175165</Query>
<ParameterizedQuery>update EP_Platform_DV.Customer_EASYCommerceMVC_Data.AddressBookHierarchy set RelationType = @Name0,Imported = @Name1,UpdatedOn = @Name2 WHERE ParentAddressId = @Name3 AND ChildAddressId = @Name4</ParameterizedQuery>
</Result>

Suggestion: if the upsert is updating vs inserting, can it have the same output structure with the parameters?
word count: 112
SteveCap
Posts: 329
Joined: August 26th, 2021, 9:18 am
Contact:

Re: How an Upsert Error Looks in the XML

Unread post by SteveCap »

Task 11481 has been created to discuss this change.
word count: 9
Post Reply