Skip to main content
J
JO_993058514-Alexandrite
14-Alexandrite
September 17, 2024
Question

QueryDataTableEntries filter not working when using a variable value

  • September 17, 2024
  • 3 replies
  • 1716 views

 

	query = {
		"filters": {
			"type": "EQ",
			"fieldName": "site",
			"value": uid
		}
	};

logger.warn(uid);
	var operatorDataTableEntries = Things["EAS.Mun.DataTable.Operators"].QueryDataTableEntries({
		maxItems: undefined /* NUMBER */ ,
		query: query /* QUERY */ ,
		values: undefined /* INFOTABLE */ ,
		source: undefined /* STRING */ ,
		tags: undefined /* TAGS */
	});

 

If I run this query and use 2 instead of uid, it will return the expected results. When I change this to uid, it returns no results but throws no errors. I have verified that uid = 2. I have also tried casting uid to Number() and parseInt().

 

Why is this not working when using a variable? Is this a known bug in my TWX version? This is Thingworx version 9.0.4

3 replies

J
JO_993058514-AlexandriteAuthor
14-Alexandrite
September 17, 2024

After further debugging I found a key piece of information. The uid variable only returns 0 results if it is coming from an infotable like below. I have verified that both uid = 2 and uid2 = 2 and they are both number types. Yet, uid returns 0 results and uid2 returns the proper results. WHY?

JO_9930585_0-1726600687954.png

 

V
19-Tanzanite
Velkumar19-Tanzanite
19-Tanzanite
September 18, 2024

Hi @JO_9930585 

 

Could you please check whether service get_site_uid returns proper value.

 

You can check value in script log using below code

logger.info("Site InfoTable Value : " + JSON.stringify(siteUID..ToJSON()));

 

/VR

J
JO_993058514-AlexandriteAuthor
14-Alexandrite
September 18, 2024

As stated in the original post, uid and uid2 are exactly the same. They both show 2 and they are both number types. 

Rocko
Rocko19-Tanzanite
19-Tanzanite
September 18, 2024

Many articles are available about QueryDataTableEntries, valid for 9.0, see here:

https://www.ptc.com/en/support/search/#q=QueryDataTableEntries%20filter&f-ts_product_category=ThingWorx&f-ts_releases=9.0

It could also be you run into this one:

https://community.ptc.com/t5/ThingWorx-Developers/DataTable-QueryDataTableEntries-broken-in-Release-9-3/m-p/785657#M57357%3Fsource=search

 

Since 9.0 is out of support for over two years now I suggest you test on a newer, supported release first.

 

J
JO_993058514-AlexandriteAuthor
14-Alexandrite
September 18, 2024

Yes, I saw this article. I was thinking this was a bug in my TWX version. We will do a major upgrade in the future, but for now I was looking to see if anyone had a workaround.

P
PEHOWE17-Peridot
17-Peridot
November 14, 2024

@JO_9930585 .

Since we have not identified a solution to your problem, would you be interested in opening a case with Support. If you approve this action I can open the case on your behalf.

Regards,
Pehowe 

J
JO_993058514-AlexandriteAuthor
14-Alexandrite
November 21, 2024

That will not be necessary. We removed this feature altogether. The issue was never resolved.

C
Constantine18-Opal
18-Opal
November 22, 2024

Hello @JO_9930585! Sorry, I'm late to the party, just wanted to hypothesize about this unusual issue...

 

ThingWorx uses Mozilla Rhino engine to execute your server-side JavaScript. Rhino allows you to put any Java number like byte, char, int, long or double to the script's context. Although Rhino wraps those numbers into a single JavaScript Number type, behind the scenes it actually preserves the original Java object. ThingWorx uses the "full set" of Java types on the platform level, e.g. for storing data in Data Tables, Infotables and Properties. So, inside ThingWorx platform and inside Rhino script context you have "real" Java types like int or long, but to the JavaScript code they all appear as a single generic number type, that's why your "typeof uid" always returns "number", even though in reality it is something like "number wrapper over int". This abstraction works in 99% of cases just fine, but sometimes it breaks.

 

For example, I was able to reproduce your problem by creating a DataTable with the uid column defined as STRING (not a NUMBER!), and query it like you do:

 

let result = Things["Tt"].QueryDataTableEntries({
	query: {
		"filters": {
			"type": "EQ",
			"fieldName": "uid",
			"value": me.uid
		}
	}
});
// Also log typeof me.uid

 

I simplified your Infotable case and just used a Property instead. The result of this query depends on the type of me.uid -- if it's an INTEGER or LONG, the query works correctly, returning one row. But if I set it to NUMBER, the query returns zero results! The typeof me.uid always says "number", and the value is 2, but I would bet that internally Rhino uses Java Integer, Long and Double objects, respectively. By the way, I couldn't reproduce this issue with any numeric type for uid in the DataTable, only with a STRING or GUID.

 

So it looks like you encountered a rare corner case caused by the particular way Rhino "number" abstraction is leaking, and ThingWorx is not handling it correctly.

 

The best way to prevent it from happening is to ensure that the data types throughout the entire data flow are exactly the same and require as few implicit conversions as possible. If you can't ensure that, then to convert between different "numbers" you can "pass" them through Infotables, service return parameters, cast them to string and back to number, etc. -- all solutions have different level of ugliness. But probably you shouldn't even care about it, as it is exceedingly rare.

 

@Rocko @Velkumar You guys might find this interesting and even report this to R&D. I tested it on 9.3.1, don't have a more recent version at the moment.

 

P.S. This issue is not related to the fact that NUMBER is Double which can't represent integers exactly, as we are testing it with uid = 2, and Double can represent powers of 2 without errors.

 

/ Constantine

Terms & ConditionsCookie settingsAccessibility statement