cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - You can subscribe to a forum, label or individual post and receive email notifications when someone posts a new topic or reply. Learn more! X

Trailing whitespace in data table key

jensc
17-Peridot

Trailing whitespace in data table key

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

1 ACCEPTED SOLUTION

Accepted Solutions
slangley
23-Emerald II
(To:jensc)

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

View solution in original post

5 REPLIES 5

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

 

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

 

jensc
17-Peridot
(To: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
23-Emerald II
(To:jensc)

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
17-Peridot
(To:slangley)

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

Top Tags