How an Upsert Error Looks in the XML

[CathyC]

I was testing and thought this was interesting.

image.png (5.65 KiB) Viewed 328 times

image.png (5.65 KiB) Viewed 328 times

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?

[CathyC]

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

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

[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:

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?

[CathyC]

Heres the alternative I went for

[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?

[SteveCap]

Task 11481 has been created to discuss this change.