Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! X
Hello everyone,
I noticed some "strange" behavior while trying to add entries into a data table.
I have a large JSON that I am converting into an infotable to do the entry.
However it seems like the key value that is in my datashape contains some duplicates.
The JSON looks something like this:
{array:[{key: 'A'}, {key: 'A '}]}
Now, as you might be able to see, the second object in the array has a key value with a trailing blank space.
To me, these two are not equal, however it seems like when using the AddDataTableEntries service, it sees this as a duplicate.
And also when trying to add rows manually using the data tables "mashup", I can also not add both 'A ' and 'A'.
I'm not sure if this is caused by something on the platform side seeing this as equal, or if it is something on the DB side, so I wanted to ask here to see if someone has had similar issues.
It seems like it doesn't just skip the duplicate either, instead it just stops the addition of entries all together.
For now I will either add another key or make sure the JSON doesn't contain any duplicates.
Thanks,
Jens
Solved! Go to Solution.
Hi @jensc.
Testing this on a SQL Server instance of ThingWorx, we observed similar issues, and subsequently found that this is designed behavior in SQL Server. Changing the default behavior may have unintended results, so it's not recommended to resolve it in that manner.
You may need to implement some logic to prevent special characters like spaces being added. It would also be a good idea to add some error handling so the script doesn't abort in the middle.
Regards.
--Sharon
tried with following, and it's working fine.
Hello,
That's strange!
I am on:
When using the AddDataTableEntries() manually where "ObjektID" is the key:
As you can see, it does insert the first row but fails when trying to insert the second.
This data comes from an upstream system and they say this values should be unique (which it is I guess), but some users had accidentally added a space after the value and then recreated without it without removing the "bad" value.
I wonder if this is a difference between databases?
I'll see if I can test it directly in the database.
Thanks,
Jens
Hello,
I still haven't figured out why this happens.
It would be great if someone else with a 9.3.5 platform could try this out to see if they face the same issue
Thanks,
Jens
Hi @jensc.
Testing this on a SQL Server instance of ThingWorx, we observed similar issues, and subsequently found that this is designed behavior in SQL Server. Changing the default behavior may have unintended results, so it's not recommended to resolve it in that manner.
You may need to implement some logic to prevent special characters like spaces being added. It would also be a good idea to add some error handling so the script doesn't abort in the middle.
Regards.
--Sharon
Hello,
I thought this might be the case and it is good to have confirmation.
I added some controls for removing any duplicates (which in my case could possibly cause other issues, but that's okay for now).
Thanks,
Jens