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

**URL:** <https://forum.inductiveautomation.com/t/encountering-an-odd-error-when-calling-a-named-query-with-scripting/70831>\
**Category:** Ignition\
**Tags:** ignition81, scripting, database\
**Created:** [March 9, 2023, 2:31pm UTC](https://forum.inductiveautomation.com/t/encountering-an-odd-error-when-calling-a-named-query-with-scripting/70831 "2023-03-09T14:31:54Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dalton\_Adams](https://avatars.discourse-cdn.com/v4/letter/d/7c8e57/32.png) [@Dalton\_Adams](https://forum.inductiveautomation.com/u/Dalton_Adams)\
**Post date:** [March 9, 2023, 2:31pm UTC](https://forum.inductiveautomation.com/t/encountering-an-odd-error-when-calling-a-named-query-with-scripting/70831/1 "2023-03-09T14:31:54Z")

</div>

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.

```auto
DELETE FROM reader0a1
WHERE tidNum = :tidVal;

```

tidVal is the parameter I pass in with my code.

```auto
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:

```auto
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,

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [March 9, 2023, 2:58pm UTC](https://forum.inductiveautomation.com/t/encountering-an-odd-error-when-calling-a-named-query-with-scripting/70831/2 "2023-03-09T14:58:51Z")

</div>

> [@Dalton\_Adams](#):
>
> `WHERE tidNum = :tidVal;`

Try using two equals in your query

```auto
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.

---

<div class="post-metadata">

**Author:** ![Dalton\_Adams](https://avatars.discourse-cdn.com/v4/letter/d/7c8e57/32.png) [@Dalton\_Adams](https://forum.inductiveautomation.com/u/Dalton_Adams)\
**Post date:** [March 9, 2023, 3:04pm UTC](https://forum.inductiveautomation.com/t/encountering-an-odd-error-when-calling-a-named-query-with-scripting/70831/3 "2023-03-09T15:04:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [March 9, 2023, 3:15pm UTC](https://forum.inductiveautomation.com/t/encountering-an-odd-error-when-calling-a-named-query-with-scripting/70831/4 "2023-03-09T15:15:40Z")

</div>

No worries, happens to me all the time.

---

<div class="post-metadata">

**Author:** ![Dalton\_Adams](https://avatars.discourse-cdn.com/v4/letter/d/7c8e57/32.png) [@Dalton\_Adams](https://forum.inductiveautomation.com/u/Dalton_Adams)\
**Post date:** [March 9, 2023, 3:21pm UTC](https://forum.inductiveautomation.com/t/encountering-an-odd-error-when-calling-a-named-query-with-scripting/70831/5 "2023-03-09T15:21:29Z")

</div>

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.

```auto
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

```

---

<div class="post-metadata">

**Author:** ![andrews](https://avatars.discourse-cdn.com/v4/letter/a/898d66/32.png) [@andrews](https://forum.inductiveautomation.com/u/andrews)\
**Post date:** [March 9, 2023, 3:36pm UTC](https://forum.inductiveautomation.com/t/encountering-an-odd-error-when-calling-a-named-query-with-scripting/70831/6 "2023-03-09T15:36:10Z")

</div>

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)](https://docs.inductiveautomation.com/display/DOC81/coalesce#:~:text=coalesce%20This%20function%20is%20used%20by%20Ignition%27s%20Expression,in%20order%2C%20and%20returns%20the%20first%20non-null%20argument.)

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

---

<div class="post-metadata">

**Author:** ![Dalton\_Adams](https://avatars.discourse-cdn.com/v4/letter/d/7c8e57/32.png) [@Dalton\_Adams](https://forum.inductiveautomation.com/u/Dalton_Adams)\
**Post date:** [March 9, 2023, 3:42pm UTC](https://forum.inductiveautomation.com/t/encountering-an-odd-error-when-calling-a-named-query-with-scripting/70831/7 "2023-03-09T15:42:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Dalton\_Adams](https://avatars.discourse-cdn.com/v4/letter/d/7c8e57/32.png) [@Dalton\_Adams](https://forum.inductiveautomation.com/u/Dalton_Adams)\
**Post date:** [March 9, 2023, 3:51pm UTC](https://forum.inductiveautomation.com/t/encountering-an-odd-error-when-calling-a-named-query-with-scripting/70831/8 "2023-03-09T15:51:46Z")

</div>

I do not understand why this is occurring.

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![avaughn](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/avaughn/32/38972_2.png) [@avaughn](https://forum.inductiveautomation.com/u/avaughn)\
**Post date:** [March 9, 2023, 3:52pm UTC](https://forum.inductiveautomation.com/t/encountering-an-odd-error-when-calling-a-named-query-with-scripting/70831/9 "2023-03-09T15:52:53Z")

</div>

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](https://docs.inductiveautomation.com/display/DOC81/system.db.runNamedQuery)

---

<div class="post-metadata">

**Author:** ![Dalton\_Adams](https://avatars.discourse-cdn.com/v4/letter/d/7c8e57/32.png) [@Dalton\_Adams](https://forum.inductiveautomation.com/u/Dalton_Adams)\
**Post date:** [March 9, 2023, 4:14pm UTC](https://forum.inductiveautomation.com/t/encountering-an-odd-error-when-calling-a-named-query-with-scripting/70831/10 "2023-03-09T16:14:55Z")

</div>

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

I figured out my other issue thanks for the help!