I have a simple named query pulling data from a database based on a single string parameter. When I manually enter the parameter in the testing window for the query it returns row(s) as expected, but when I execute the named query from a script it returns 0 rows with the same input.
Tried client and gateway scripts, and some different string options in the scripts, but nothing has helped. Any advice would be much appreciated
Can you give the script that call the named query ?
Having the actual script (In preformatted form) will obviously make this much easier, but here are some general "gotcha's" to watch out for.
system.db.runNamedQuery() is one of those functions.Without the actual script it is difficult to give you any further guidence.
My money is on this and a missing Project Name param in front. This still trips me up.
Pssst!
# In some project library script...
@system.util.runInGateway
def someFunction(someArguments):
....
Then you can call someLibrary.someFunction(...) in the script console and it'll get diverted automatically to run in the gateway.
The script code that generates the console output is shown below. To give some background we are pulling the "packageNumber" value from a PLC to be used as the query parameter. This script is executing within a Client Event Tag Change script. We have tried making it a Gateway Tag Change script, and modifying the system.db.runNamedQuery call to add the project name for that scope, same result.
Strange thing is that if we change the named query to use an integer for the "packageNumber" and query against a different column in the DB, this script executes correctly. We can't actually do this for our application in production, we were just testing scenarios.
Appreciate the feedback, will try some of the suggestions next time I'm in front of this machine.
print("Reading Data")
#Defining PLC tags to read from
readPaths = [
"[edge]PLC/Controller:Global/ERP/i_SQL/PACKAGE_NUMBER"
]
# Specify the values
readValues = system.tag.readBlocking(readPaths)
packageNumber = str(readValues[0].value)
print(packageNumber)
print(type(packageNumber))
queryValues = {
"myPackageNumber" : packageNumber
}
print(queryValues)
#Run selectBundleInfo named query, returns PyDataset of info
queryResult = system.db.runNamedQuery("ERP/selectBundleInfo",queryValues)
print(queryResult)
#Reading data from queryResult dataset
columnHeaders = system.dataset.getColumnHeaders(queryResult.getUnderlyingDataset())
print(columnHeaders)
Is the package number actually stored as a number in the DB?
I actually don't know if a Client Event Tag Change Script executes in Gateway or Client scope, so I can't tell you for certain that you need the project name in the call. However, I would expect you to get an error (Something like: No project named "ERP/selectBundleInfo"), so that and the fact that you're not getting an error but an unexpected result clues me into the fact that the Named Query is at least executing.
This tells me that there is some descrepency between a python converted string and what ever a provided string in the testing panel.
If from the Script Console in the designer you run this code do you get the expected result:
queryResult = system.db.runNamedQuery("ERP/selectBundleInfo",{"myPackageNumber":"555555"})
for row in queryResult:
for col in queryResult.columnNames:
print row[col]
I don't think those single quotes are Ignition/us marking things as a string. I think they're actually in the DB, attached to the data (
)...
This did end up being the issue, once we changed our parameter to include the single quotes "'5555555'" instead of "5555555" the query worked... I was including those single quotes in the query tester without really thinking about it.
Feel silly not realizing that myself, really appreciate the help!
