runPrepUpdate vs runUpdateQuery

Joseph_Loh · June 19, 2023, 9:04am

I'm writing an insert into query to add a row of data into a table with 5 columns. From reading the doc, I assume I don't need to use prep update as

my data is not being typed out by user and doesn't contain additional ''
my data does not contain binary or blob values

if I runUpdateQuery, it should look as such right?

InsertQuery=system.db.runUpdateQuery("INSERT INTO FORCE_INFO (EQPID,COMPTAG,FORCEVAL,USERID,DTTM) VALUES ('%s', '%s','%s', '%s', '%s')" %("5BH1D","4PE42","0","JMLNEW004","19/06/23"))

and if I runPrepUpdate, it should look as such right?

InsertPrep=system.db.runPrepUpdate("INSERT INTO FORCE_INFO (EQPID,COMPTAG,FORCEVAL,USERID,DTTM) VALUES (?,?,?,?,?)",["5BH1D","4PE42","0","JMLNEW004","19/06/23"])

dkhayes117 · June 19, 2023, 12:22pm

I would make this a named query.
Named Queries - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)
system.db.runNamedQuery - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)

PGriffith · June 19, 2023, 2:42pm

You should basically always use runPrepUpdate. It's a straight upgrade over assembling the query manually for a variety of reasons. The two most important:

The JDBC driver is in charge of sending your parameters over the wire in a 'native' format, without any lossy conversions to strings. Sending strings works okay for numbers and literal strings, but quickly hits limitations: what about date(time) columns, binary data, JSON, or other exotic types?
A prepared statement (from any of the prep* functions) can be parsed and cached and better optimized on the receiving server. If two identical prep updates come in, the second will likely avoid the SQL parsing work. Manually sent string queries will have to be parsed from scratch each time.

Named queries get all the same benefits as prep updates but are not as flexible, by nature. Both solutions have their place. One thing to never do is enable legacy DB access for Vision clients.

Joseph_Loh · June 21, 2023, 5:27am

I ran the runPrepUpdate line from above, why do I get this error

Traceback (most recent call last):
  File "<input>", line 1, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO MDS_FORCE_INFO (EQPID,COMPTAG,FORCEVAL,USERID,DTTM) VALUES (?,?,?,?,?), [5BH1D, 4PE42, 0, JMLNEW004, 19/06/23], , , false, false)


	at org.python.core.Py.JavaError(Py.java:547)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)

	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:541)

	at org.python.core.PyObject.__call__(PyObject.java:477)

	at org.python.core.PyObject.__call__(PyObject.java:481)

	at org.python.pycode._pyx13.f$0(<input>:1)

	at org.python.pycode._pyx13.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1687)

	at org.python.core.Py.exec(Py.java:1731)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:611)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:599)

	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)

	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)

	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)

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO MDS_FORCE_INFO (EQPID,COMPTAG,FORCEVAL,USERID,DTTM) VALUES (?,?,?,?,?), [5BH1D, 4PE42, 0, JMLNEW004, 19/06/23], , , false, false)

	... 26 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Cannot run this function when in read-only mode.

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:384)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:283)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:278)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:288)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:261)

	... 24 more

Traceback (most recent call last):
  File "<input>", line 1, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO MDS_FORCE_INFO (EQPID,COMPTAG,FORCEVAL,USERID,DTTM) VALUES (?,?,?,?,?), [5BH1D, 4PE42, 0, JMLNEW004, 19/06/23], , , false, false)

PGriffith · June 21, 2023, 5:55am

Change your designer comm mode.

Joseph_Loh · June 21, 2023, 6:12am

That actually worked, thanks.

pascal.fragnoud · June 21, 2023, 6:20am

Tip for your next stack trace: Find the lines that start by Caused by, they'll tell you what's wrong.