Hello,
Hopefully someone can shed some light on this oversight of mine. Currently, I just can't seem to see it.
After I run the named query (which is a scalar query), I want to compare the value against a PLC value. However, if nothing is returned for the select named query, I set a fallback value of 1. Testing this out when nothing is returned from the named query, the tag [default]testIntegerWLR is correctly set to 1. However, the second if statement where queryDatasetSelect1 == 1 is not true and I always execute into the elif portion of the if statement where queryDatasetSelect1 != 1. Can someone please help me see the light here? How is the integer memory tag set to the fallback value but I can't read the fallback value correctly in an if statement. TIA.
if (basicState == 4):
#Create Dictionary For Named Query Parameters, Call Select Detailed State Named Query
parametersSelect1 = {"sqlDetailStateTable":sqlDetailStateTable, "detailStateID":detailStateID}
queryDatasetSelect1 = system.db.runNamedQuery("Project_Name", "Select Detailed State", parametersSelect1)
system.tag.writeBlocking(['[default]MemoryTags/testIntegerWLR'], queryDatasetSelect1)
#test123 = system.tag.read('[default]MemoryTags/testIntegerWLR').value
if queryDatasetSelect1 == 1:
system.tag.writeBlocking(['[default]MemoryTags/testStringWLR'], "wtf")
#Create Dictionary For Named Query Parameters, Call Insert Detailed State Named Query
parametersInsert1 = {"sqlDetailStateTable":sqlDetailStateTable, "detailStateID":detailStateID, "detailStateDesc":detailStateDesc}
queryDatasetInsert1 = system.db.runNamedQuery("Project_Name", "Insert Detailed State", parametersInsert1)```
Try changing the check to be if queryDatasetSelect1 == '1':, it may be returning the value as a string instead of an integer, and then the write to the tag is coercing it to an integer.
Another way to check this would be to add a logger or a print statement to check the type of the returned result (print type(queryDatasetSelect1)) and confirm it is the type you expect.
3 Likes
Would also saying returning a NULL value is allowed, that does not get a fallback, iirc you only get the fallback if no rows are selected at all. But row/column value of NULL is a result.
It's not returning a string. Good idea on the logger.. looks like its returning a unicode data type? Logger string: <type 'unicode'>
How would null get coerced into a 1 though for the integer memory tag I write to? I am 100% getting a fallback value. If I change the value for the fallback, I see the correct value written to my memory integer.
Fair point. I just brought it up as a common gotcha, many people mistake that a null will get the fallback value but I think you're right, that's not the issue here. Does seem a datatype issue as @ryan.white pointed out since you're getting unicode - that's the problem.
Ya, its weird its returning a unicode datatype though. It says nothing about this in the manual for returned values for scalar queries. Just that you'd get an object returned that matches the data type of the return value. Any idea how to use a unicode datatype in logic for the scripting? Writing the unicode returned value to a tag, and then using that tag in scripting logic seems inefficient and like I am doing it wrong.
What is the datatype of the column you are selecting from in the scalar query? Is it not a varchar or text type? What's the query like, any casting being done in there?
The data type is a varchar(85). Essentially, I am doing a select query to get a string for a fault based on a fault number. Then I am comparing the fault string in the database to the fault string in the PLC and if they aren't the same, I overwrite the fault string in the database with the PLC fault string. Not sure what casting is though.
The select query is simple:
SELECT Description FROM {sqlDetailStateTable} WHERE DetailState = :detailStateID
Furthermore, if I do a select query for the fault ID number, and it doesn't exist, I do an insert statement to insert the fault to the table. This is why I need the fallback value, to know if the fault id and corresponding description exists in the table or not.
There you go. The datatype is a VARCHAR so Ignition does not do any implicit casting of a '1' to the integer 1 for you because it does not know when that would be appropriate or not, it just passes through the VARCHAR from the db to you. I would recommend changing the datatype to be an integer if it is always going to be an integer at the database level.
If it is not always going to be a integer but can be something like someText1, then change the conditional in your script to be if queryDatasetSelect1 == '1' instead.
I am thinking that the fallback value matches the expected type from the db as well, seem sthe only sensible thing. If you're normally getting a VARCHAR from the named query column and then ignition gave you an integer as a fallback that could really mess up your control flow or cause errors down the line.
It may make more sense for you to take the fallback value off. Then you can do something like
queryDatasetSelect1 = system.db.runNamedQuery("Project_Name", "Select Detailed State", parametersSelect1)
if queryDAtasetSelect1:
# do something
else:
# you did not get a result
1 Like
What does the fallback value have to do with the datatype of the select query though? Does the fallback datatype have to be the same as the column datatype?
I assume because it would completely mess up control flow though I don't know the backend code perhaps @PGriffith could chime in about what determines the type of the fallback value of a scalar named query. I imagine it's because if you are expecting a varchar from the DB normally then if you had this
result = system.db.runNamedQuery('somequery')
subString = result.index('i')
Now the fallback would then cause that next line to immediately throw an error because the rest of your script would be working with the assumption of the datatype from the db, and I imagine Ignition doesn't want to change that on the fly - if it gave you a integer then your next line would throw an error now but if it gave you a fallback of the same type, at least that method exists.
Alternatively, if you were expecting an integer normally and it gave you a varchar as the fallback
result = system.db.runNamedQuery('somequery')
addition = result + 1
this too now your fallback is giving you an error because of a type mismatch between what your script is expecting and what you are getting.
Did some testing and it always seems to be unicode so ignore that . It's just always unicode.
1 Like
Bummer, ok I might just have to change course and run a scalar query instead of a named query. Thanks for your help in testing this.
I don't know that running a scalar query will give you any different behavior here, your result will still be unicode since your database column is varchar(85) and you won't have the ability to make a fallback value (though again don't think you really necessarily need on here).
Personally I would still remmond what I said before with
queryDatasetSelect1 = system.db.runNamedQuery("Project_Name", "Select Detailed State", parametersSelect1)
if queryDAtasetSelect1:
# you got SOME result (assuming you don't have a fallback set, its straight from the db)
else:
# you did not get a result - either no rows, or a null value.
I feel this is the cleanest way to do control flow on a query.
If you want to check the difference between a row of a NULL and no rows returned, turn it from scalar to a normal query, then you can do something like
result = system.db.runNamedQuery('test',{'idx':29})
if result.rowCount:
# you have a row, now you can inspect the value if you need and go from here
print 'got result %s'%(result)
else:
# you did not get any rows back
print 'nothing returned'
Unicode is string. Sort of. It's what jython calls unicode encoded strings. That's the 'u' you might see when printing the representation of some strings (try print repr(u"foo")). It just means they're encoded using unicode.
You can treat it just like a string.
Some reading material that might make things clearer:
1 Like
What a great article. Thanks for sharing.
I ended up taking your lead on this and followed your example. It seems to be working nicely. Thank you!
1 Like
