Encountering an odd error, when calling a named query with scripting

Dalton_Adams · March 9, 2023, 2:31pm

Hello,
I recently made a script for checking two SQL tables for shared TID values for RFID tags, the script then evaluates which one appeared most recently and deletes the older one. To do this I made a named query for deleting the row that contains the older value and call it in my code using the runNamedQuery() function.
This is my query for deletion.

DELETE FROM reader0a1
WHERE tidNum = :tidVal;

tidVal is the parameter I pass in with my code.

namedQueryA2 = 'deleteOldZoneR0A1'
paramsA2 = {'tidVal':tid1}
system.db.runNamedQuery(namedQueryA2, paramsA2)

For some reason the passing in of the TID parameter value is not working properly. I previously tested the queries themselves and they worked properly. I don't know why this is occuring but in the error log it seems when I am passing the value into the query it is adding an additional single quote.

First block of error log:

Java Traceback:
Traceback (most recent call last):
  File "<input>", line 82, in <module>
WHERE tidNum = 'E280110020007B519B090983'' at line 2
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:351)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:325)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.invoke(GatewayInterface.java:954)
	at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.execute(DesignerNamedQueryManager.java:73)
	at com.inductiveautomation.ignition.designer.db.namedquery.DesignerNamedQueryManager.execute(DesignerNamedQueryManager.java:46)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities.runNamedQuery(ClientDBUtilities.java:117)
	at jdk.internal.reflect.GeneratedMethodAccessor3362.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE
WHERE tidNum = 'E280110020007B519B090983'' at line 2

I am confused on why this is occurring any help will be greatly appreciated.
Thanks for reading,

andrews · March 9, 2023, 2:58pm

Try using two equals in your query

WHERE tidNum == :tidVal;

I could be wrong but when in doubt:
One equal is used as an assignment operator.
Two equals are used as a comparison operator.

And you are comparing not assigning so I think using two should work.

Dalton_Adams · March 9, 2023, 3:04pm

Wow thank you; I really should have noticed that. That was indeed the issue. I greatly appreciate it.

andrews · March 9, 2023, 3:15pm

No worries, happens to me all the time.

Dalton_Adams · March 9, 2023, 3:21pm

Hmmm this is odd, it seemed to work once and then started throwing the same errors. I changed them back and they started working again.

09:15:07.364 [SwingWorker-pool-1-thread-1] ERROR com.inductiveautomation.ignition.client.util.gui.ErrorUtil - null
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== 'ea2'' at line 2

andrews · March 9, 2023, 3:36pm

That seems to be because certain values are returning NULL.

All you have to do is use a coalesce and you should be good to go.

coalesce - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)

It will allow you to return a default value when it is NULL.

Dalton_Adams · March 9, 2023, 3:42pm

Ah I see, thanks for the help!
Now I have to figure out why, it breaks whenever I put it in the gateway. For some reason it works in my script console but says it cannot compile whenever I throw it into my Gateway Events. Weird

Dalton_Adams · March 9, 2023, 3:51pm

I do not understand why this is occurring.

org.python.core.PySyntaxError: SyntaxError: mismatched input '' expecting DEDENT (, line 84)
at org.python.core.ParserFacade.fixParseError(ParserFacade.java:95)
at org.python.core.ParserFacade.parse(ParserFacade.java:205)
at org.python.core.Py.compile_flags(Py.java:2252)
at com.inductiveautomation.ignition.common.script.TagChangeScriptExecutor.compiledCode(TagChangeScriptExecutor.java:112)
at com.inductiveautomation.ignition.common.script.TagChangeScriptExecutor.(TagChangeScriptExecutor.java:53)
at com.inductiveautomation.ignition.common.script.TagChangeScriptManager.(TagChangeScriptManager.java:52)
at com.inductiveautomation.ignition.common.script.ScriptConfig.startup(ScriptConfig.java:137)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle.createScriptConfig(ProjectScriptLifecycle.java:291)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle.startupScriptConfig(ProjectScriptLifecycle.java:234)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle.restartScriptConfig(ProjectScriptLifecycle.java:230)
at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle.onResourcesModified(ProjectScriptLifecycle.java:713)
at com.inductiveautomation.ignition.gateway.project.ProjectLifecycle$LifecycleResourceListener.resourcesModified(ProjectLifecycle.java:176)
at com.inductiveautomation.ignition.common.project.AbstractProject.notifyResourceListeners(AbstractProject.java:351)
at com.inductiveautomation.ignition.common.project.AbstractProject.updateEffectiveState(AbstractProject.java:153)
at com.inductiveautomation.ignition.common.project.RuntimeProject.applyChange(RuntimeProject.java:274)
at com.inductiveautomation.ignition.gateway.project.ProjectLifecycleFactory$1.lambda$updateOrStartAffectedProjects$5(ProjectLifecycleFactory.java:185)
at java.base/java.util.ArrayList.forEach(Unknown Source)
at com.inductiveautomation.ignition.gateway.project.ProjectLifecycleFactory$1.updateOrStartAffectedProjects(ProjectLifecycleFactory.java:165)
at com.inductiveautomation.ignition.gateway.project.ProjectLifecycleFactory$1.projectUpdated(ProjectLifecycleFactory.java:143)
at com.inductiveautomation.ignition.gateway.project.BaseProjectManager.doProjectUpdatedNotification(BaseProjectManager.java:1058)
at com.inductiveautomation.ignition.gateway.project.BaseProjectManager.lambda$fireProjectUpdated$25(BaseProjectManager.java:1008)
at com.inductiveautomation.ignition.gateway.project.BaseProjectManager.lambda$submitToQueue$22(BaseProjectManager.java:986)
at com.inductiveautomation.ignition.common.util.ExecutionQueue$PollAndExecute.run(ExecutionQueue.java:239)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)

avaughn · March 9, 2023, 3:52pm

When using system.db.runNamedQuery from the gateway scope, you also need to pass in the name of the project that contains the named query.

https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery

Dalton_Adams · March 9, 2023, 4:14pm

Thanks for pointing that out, I went ahead and added that!

I figured out my other issue thanks for the help!