Query returns no rows when using tag value as argument

Ignition
1

Hi All!

I am attempting to use a scalarPrepQuery with one argument in the where clause:

PN = str(system.tag.readBlocking(["[default]CurrentPartNumber"])[0].value)

query_lower = “”“SELECT TOP (1)
[LOWER_SPEC]
FROM [Test].[dbo].[GET_SPEC]
WHERE EQUIP_NUM = ‘XXX’ AND PART_NUMBER = ?
ORDER BY TIMESTAMP DESC”""

lowerSpec = system.db.runScalarPrepQuery(query_lower,[PN])

And it returns no results.

The weird thing is, if I instead type the returned tag value into the PN variable like this:

PN = ‘PartNumber’

And then run it, it returns the results I’m looking for.

Has anyone else seen this? I checked the type and both show as strings and if you print them out (the tag value and the manually typed in one) they both look identical

2

Try this

PN = system.tag.readBlocking(["[default]CurrentPartNumber"])[0].value

query_lower = '''SELECT TOP (1)
                 [LOWER_SPEC]
                 FROM [Test].[dbo].[GET_SPEC]
                 WHERE EQUIP_NUM = "XXX" AND PART_NUMBER = "?"
                 ORDER BY TIMESTAMP DESC
'''

lowerSpec = system.db.runScalarPrepQuery(query_lower,[PN])
3

I get an index 1 is out of range exception?

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: The index 1 is out of range.

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:359)

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:333)

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:286)

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:868)

at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:839)

at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepQuery(ClientDBUtilities.java:317)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runScalarPrepQuery(AbstractDBUtilities.java:308)

... 23 more
4

hmm…try this. If this doesn’t work, isolate the PN= line and add print PN to make sure the value you are getting is what you expect

PN = system.tag.readBlocking(["[default]CurrentPartNumber"])[0].value

query_lower = '''SELECT TOP (1)
                 [LOWER_SPEC]
                 FROM [Test].[dbo].[GET_SPEC]
                 WHERE EQUIP_NUM = "XXX" AND PART_NUMBER = ?
                 ORDER BY TIMESTAMP DESC
'''

lowerSpec = system.db.runScalarPrepQuery(query_lower,[PN])

PN = system.tag.readBlocking(["[default]CurrentPartNumber"])[0].value
print PN
5

I’d recommend print repr(PN) so you can see any white spaces or hidden characters.

4 Likes
6

WillMT10 gets the prize! I didn’t know about repr and print wasn’t showing anything.

Thank you!

I’m getting this:

“u’PartNumber\r\x00’”

Is there an ideal way to get rid this? Just use spring parsing to delete the first and last ones? Or is there a more elegant way to drop hidden characters?

7

Okay I did a google and came up with this:

PN = re.sub("[^a-z0-9]+","", PN, flags=re.IGNORECASE)

Figured I’d ask if this is how y’all do it or if there is an ignition library that is better. But either way seems to be working!!

Thank u for the help!
-Kelly

8

If the format is always the same (with a CR after the part number), you can use split():

s = u'PartNumber\r\x00'
print repr(s)

t = s.split('\r')
print t
print repr(t[0])

Output:

u'PartNumber\r\x00'
[u'PartNumber', u'\x00']
u'PartNumber'
>>>