Skip to main content
J
jensc17-Peridot
17-Peridot
March 22, 2023
Solved

Trailing whitespace in data table key

  • March 22, 2023
  • 2 replies
  • 1746 views

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

Best answer by slangley

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

2 replies

S
17-Peridot
Sathishkumar_C17-Peridot
17-Peridot
March 23, 2023

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)

Sathishkumar_C_0-1679548642909.png

 

J
jensc17-PeridotAuthor
17-Peridot
March 23, 2023

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:

jensc_0-1679555022657.pngjensc_1-1679555047939.pngjensc_2-1679555121376.png

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

 

J
jensc17-PeridotAuthor
17-Peridot
March 27, 2023

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

S
slangleyCommunity ManagerAnswer
Community Manager
March 30, 2023

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

J
jensc17-PeridotAuthor
17-Peridot
March 31, 2023

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

Terms & ConditionsAccessibility statement