I have a script that is causing me some issues. I need it to run 2 sql queries depending on the value of the tag it is connected to.
First issue is running the query. When i comment in the syste.db.runNamedQuery, it fails, and it doesnt write to the other tags. It works when the query isnt used. When running the parameter and system.db.runNamedQuery from the script console it works fine.
Another this is using the currentValue in the definition. I cant get that to work either, and have to read in the value with readBlocking, but it should be the same value as its the same tag.
def valueChanged(tag, tagPath, previousValue, currentValue, initialChange, missedEvents):
result = system.tag.readBlocking("[default]ongoingStop[0].value")
result = currentValue
ongoing = result[0].value
#result = system.tag.readBlocking("[default]Counter 1")
result = currentValue
counterVal = result[0].value
stop_time = system.date.now()
if counterVal < 100 and not initialChange and not ongoing:
# Create a python dictionary of parameters to pass
parameters = {"startTime": stop_time, "reason": 1, "stopType": 1, "line": 1}
# Run Named Query
#system.db.runNamedQuery("Create Stop", parameters)
system.tag.writeBlocking("[default]ongoingStop", [True])
system.tag.writeBlocking("[default]TestInt", counterVal)
elif counterVal > 100 and not initialChange and ongoing:
# Create a python dictionary of parameters to pass
parameters = {"stopTime": stop_time, "line": 1}
# Run Named Query
#namedQuery = "Stop Stop"
#system.db.runNamedQuery(namedQuery, parameters)
system.tag.writeBlocking("[default]ongoingStop", [False])
system.tag.writeBlocking("[default]TestInt", counterVal)
Your code has a lot of experimentation left in it that needs to be cleaned out.
You have set the value of result three times.
The lines below don't look right. currentValue isn't an array as far as I can see.
result = currentValue
ongoing = result[0].value
What is the SQL datatype for startTime. I'd expect it to be DATETIME but you're passing in "test" in your Script Console screen grab.
system.tag.writeBlocking is expecting a list for the tagpaths and a list for the parameters. (It will work with only one item for backwards compatibility but don't rely on it not to change.)
You should combine all your system.tag.writeBlocking lines into one (in each if branch).
Clean up the code and redefine the problem and you'll get help.
It might be a good idea to post the formatted code of the named queries also.
Also, make sure you're reading from the right tag at the beginning. Is ongoingStop an array or a single tag? If it's a single tag, you could simplify this to:
Named query functions have a different required set of arguments when used in gateway scope (like tag events) versus Vision Client scope (which is what you have in the designer script console). Take a closer look at the documentation.
I also think it is worth mentioning, as it is all over the forum, you shouldn't do long running processes in value change scripts like run named queries etc. as it can cause missed events to happen.
Can you share the error information from the log or the error pop-up that happens in the designer? There are a few things that could be happening between SQL type conflictions or constraints requiring stopTime column to have data on row insertion. Transistor already alluded to this.
Did you try running the same SQL query with the parameters directly in a SQL GUI or CLI to make sure that the query matches the table structure?
I see a few issues... Are you sure your registered column is called registrered in the database? If so, you should fix it, if possible.
Otherwise, here are some more improvements to your code:
def valueChanged(tag, tagPath, previousValue, currentValue, initialChange, missedEvents):
# read tags all at once
tags = system.tag.readBlocking(["[default]ongoingStop", "[default]Counter 1"])
ongoing = tags[0].value
counterVal = tags[1].value
stop_time = system.date.now()
if counterVal < 100 and not initialChange and not ongoing:
# Create a python dictionary of parameters to pass
parameters = {"startTime": stop_time, "reason": 1, "stopType": 1, "line": 1}
# Run Named Query
system.db.runNamedQuery("Create Stop", parameters)
# if you use array syntax for values, also use array syntax for tagPaths
system.tag.writeBlocking(["[default]ongoingStop"], [True])
elif counterVal > 100 and not initialChange and ongoing:
# Create a python dictionary of parameters to pass
parameters = {"stopTime": stop_time, "line": 1}
# Run Named Query
namedQuery = "Stop Stop"
system.db.runNamedQuery(namedQuery, parameters)
# same thing here... tagPaths and tagValues are both lists
system.tag.writeBlocking(["[default]ongoingStop"], [False])
As I mention in the comments, if you use the array syntax for system.tag.writeBlocking, use it consistently. I feel that what you're doing would result in an exception being thrown (although I'm not sure if you're even getting to that point if the query isn't executing).
What I mean is that either do system.tag.writeBlocking([""], [""]) or system.tag.writeBlocking("", ""), but not what you had: system.tag.writeBlocking("", [""]).
Also, as @dillon.therrien mentioned above, make sure the query works when you run it from the script console.
The OP hasn't followed my instructions yet. The problem is the difference between gateway scope and Vision Client scope (designer).
Other notes:
Use a single readBlocking(). Like this, perhaps:
ongoing, counterVal = [qv.value for qv in system.tag.readBlocking(["[default]ongoingStop", "[default]Counter 1"])]
writeBlocking() is not safe in tag events unless the tag is a memory tag. Use writeAsync() if you cannot use a gateway tag change event (in the project, not on the tag).
runNamedQuery() is not safe in tag events. Use runSFNamedQuery() or move your entire event to a gateway tag change event.
