system.db.runQuery() Not Working from Gateway Script

aaron1 · July 25, 2021, 10:09pm

Hi all,

I am having an issue with running the system.db.runQuery() function from Gateway script but the same script runs fine in Script Console…the main bulk of script is saved in Legacy Scripts and called from a Gateway Timer Script.

This bit of script used to work on an old server running Ignition 7.9.8 with Java 8.0.660.18 and MySQL server v5.6.

The server has recently upgraded to Windows Server 2019 and MySQL version is now 8.0.25 but the Ignition and Java version stayed the same. The MySQL connector also changed to v 8.0.25.

Not sure if there’s some version combability here but if it works n Script Console then it should work from a Gateway Script?

Below is the error message I am getting but I can’t tell where exactly is going wrong.

Traceback (most recent call last):
** File “<TimerScript:project/WriteHourlyLogs @10,000ms >”, line 57, in **
** at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)**

** at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:333)**

** at sun.reflect.GeneratedMethodAccessor202.invoke(Unknown Source)**

** at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)**

** at java.lang.reflect.Method.invoke(Unknown Source)**

java.lang.Exception: java.lang.Exception: Error executing system.db.runQuery(SELECT “”, “”, Time, Location, Speed, Length FROM table1 WHERE Time BETWEEN “2021-07-26 08:00:00” AND “2021-07-26 08:59:59”, DatabaseName, )

7.9.8 (b2018060714)
Oracle Corporation 1.8.0_66

pturmel · July 25, 2021, 10:18pm

There should be one or more “caused by” elements in the full stack trace. Please post them. (You might need to get them from the wrapper log.)

Also, please mark your posted backtrace as pre-formatted text so we can read it. /:

aaron1 · July 25, 2021, 11:05pm

Hi @pturmel ,

Thanks a lot for your reply.

Below is what I got from the wrapper log…

INFO | jvm 1 | 2021/07/26 10:14:57 |
INFO | jvm 1 | 2021/07/26 10:14:57 | java.lang.Exception: java.lang.Exception: Error executing system.db.runQuery(SELECT “”, “”, Time, Location, Speed, Length FROM Table1 WHERE Time BETWEEN “2021-07-26 07:00:00” AND “2021-07-26 07:59:59”, Database1, )
INFO | jvm 1 | 2021/07/26 10:14:57 |
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.core.Py.JavaError(Py.java:495)
INFO | jvm 1 | 2021/07/26 10:14:57 | at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)
INFO | jvm 1 | 2021/07/26 10:14:57 | at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:333)
INFO | jvm 1 | 2021/07/26 10:14:57 | at sun.reflect.GeneratedMethodAccessor202.invoke(Unknown Source)
INFO | jvm 1 | 2021/07/26 10:14:57 | at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
INFO | jvm 1 | 2021/07/26 10:14:57 | at java.lang.reflect.Method.invoke(Unknown Source)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.core.PyReflectedFunction.call(PyReflectedFunction.java:186)
INFO | jvm 1 | 2021/07/26 10:14:57 | at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.call(ScriptManager.java:431)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.core.PyObject.call(PyObject.java:404)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.core.PyObject.call(PyObject.java:408)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.pycode._pyx1057.exportReportAsCSV$1(module:shared.SampleProject.export:162)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.pycode._pyx1057.call_function(module:shared.SampleProject.export)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.core.PyTableCode.call(PyTableCode.java:165)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.core.PyBaseCode.call(PyBaseCode.java:134)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.core.PyFunction.call(PyFunction.java:317)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.pycode._pyx1056.f$0(<TimerScript:SampleProject/WriteHourlyLogs @10,000ms >:37)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.pycode._pyx1056.call_function(<TimerScript:SampleProject/WriteHourlyLogs @10,000ms >)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.core.PyTableCode.call(PyTableCode.java:165)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.core.PyCode.call(PyCode.java:18)
INFO | jvm 1 | 2021/07/26 10:14:57 | at org.python.core.Py.runCode(Py.java:1275)
INFO | jvm 1 | 2021/07/26 10:14:57 | at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:636)
INFO | jvm 1 | 2021/07/26 10:14:57 | at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:603)
INFO | jvm 1 | 2021/07/26 10:14:57 | at com.inductiveautomation.ignition.common.script.TimerScriptTask.run(TimerScriptTask.java:88)
INFO | jvm 1 | 2021/07/26 10:14:57 | at java.util.TimerThread.mainLoop(Unknown Source)
INFO | jvm 1 | 2021/07/26 10:14:57 | at java.util.TimerThread.run(Unknown Source)
INFO | jvm 1 | 2021/07/26 10:14:57 | Caused by: org.python.core.PyException: Traceback (most recent call last):
INFO | jvm 1 | 2021/07/26 10:14:57 | File “<TimerScript:SampleProject/WriteHourlyLogs @10,000ms >”, line 41, in
INFO | jvm 1 | 2021/07/26 10:14:57 | File “module:shared.SampleProject.export”, line 11, in exportReportAsCSV
INFO | jvm 1 | 2021/07/26 10:14:57 | at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

INFO | jvm 1 | 2021/07/26 10:14:57 | at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runQuery(AbstractDBUtilities.java:333)

INFO | jvm 1 | 2021/07/26 10:14:57 | at sun.reflect.GeneratedMethodAccessor202.invoke(Unknown Source)

INFO | jvm 1 | 2021/07/26 10:14:57 | at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

INFO | jvm 1 | 2021/07/26 10:14:57 | at java.lang.reflect.Method.invoke(Unknown Source)

INFO | jvm 1 | 2021/07/26 10:14:57 |
INFO | jvm 1 | 2021/07/26 10:14:57 | java.lang.Exception: java.lang.Exception: Error executing system.db.runQuery(SELECT “”, “”, Time, Location, Speed, Length FROM Table1 WHERE Time BETWEEN “2021-07-26 07:00:00” AND “2021-07-26 07:59:59”, Database1, )
INFO | jvm 1 | 2021/07/26 10:14:57 |
INFO | jvm 1 | 2021/07/26 10:14:57 | … 25 common frames omitted
INFO | jvm 1 | 2021/07/26 10:14:57 | Caused by: java.lang.Exception: Error executing system.db.runQuery(SELECT “”, “”, Time, Location, Speed, Length FROM Table1 WHERE Time BETWEEN “2021-07-26 07:00:00” AND “2021-07-26 07:59:59”, Database1, )
INFO | jvm 1 | 2021/07/26 10:14:57 | … 24 common frames omitted
INFO | jvm 1 | 2021/07/26 10:14:57 | Caused by: java.lang.NullPointerException: null

pturmel · July 26, 2021, 12:53am

NullPointerException. Something you are passing to your query isn’t what you think it is.

aaron1 · July 26, 2021, 4:06am

Hi Phil,

Thanks a lot for your help, I found the problem…

For some reason it doesn’t like the two quotation marks just after the SELECT in the query string when executed from the Gateway Script, which was a bit confusing as the same query worked on the old server and in Scripting Console on the new server, also in the SQL client as well.

I’ve got way around it now but will be interested to know as to why that is if you have any theories on this…

victordcq · July 26, 2021, 9:30am

Im pretty sure it should still work.
I doubt the error was related to the empty strings tho, this is a nullpointer error not a columnname or something error.
Maybe you are using the wrong " in the query? Altho that would also show up as a different error…

Could you show the code before and after you removed these " ?

And please press this where you paste the code

But selecting empty strings seems useless tho, so better remove them anyways

lrose · July 26, 2021, 12:52pm

Completely agree, why return nothing from a Select statement?

As a general rule SQL only supports Single Quotes, but that can vary from database to database. I would advise that you stick to using single quotes in query's.

pturmel · July 26, 2021, 1:24pm

The SQL standard is single quotes for values, double quotes for identifiers (column names, table names, schema names). Various brands have alternate quoting behavior for identifiers. All use single quotes for values.