Trailing whitespace in data table key

[jensc]

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

[Sathishkumar_C]

tried with following, and it's working fine.

• ThingWorx 9.3.7-b1432
• PostgreSQL 14.3
• AddDataTableEntry() - "A" records (Manual entry)
• AddDataTableEntries() - "B" records (Manual entry)

[jensc]

Hello,

That's strange!

I am on:

• ThingWorx 9.3.5-b1258
• Some MSSQL DB (don't know the version)

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

[jensc]

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

[slangley · Accepted 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

[jensc]

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