Error in excution of system.db.runUpdateQuery with variable!

Ignition
1

Hi Guys,
I'm trying to excute the script below with variable but I allways get error, is there any one can help me into how pass avariable to the command "system.db.runUpdateQuery"

query_01="INSERT INTO predictive_test_data (station_number, line_number, test_ID, test_result_value, test_date_and_time)"
query_02=" VALUES (?,?,?,?,?)"
row_data= (st_number,'plm_num',test_id ,test_value ,'datetime')
query=query_01 + query_02
print query
system.db.runUpdateQuery(query ,row_data)

Error

Java Traceback:
Traceback (most recent call last):
File "", line 14, in
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:183)

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.runUpdateQuery(INSERT INTO predictive_test_data (station_number, line_number, test_ID, test_result_value, test_date_and_time) VALUES (10,'s000001',21 ,1543 ,'2019.08.20 10:10:34'), , , false)

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

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

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:183)

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:188)

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

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

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

at org.python.pycode._pyx34.f$0(<buffer>:16)

at org.python.pycode._pyx34.call_function(<buffer>)

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

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

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

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

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

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

at org.python.util.InteractiveInterpreter.runsource(InteractiveInterpreter.java:116)

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

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

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.runUpdateQuery(INSERT INTO predictive_test_data (station_number, line_number, test_ID, test_result_value, test_date_and_time) VALUES (10,'s000001',21 ,1543 ,'2019.08.20 10:10:34'), , , false)

... 27 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:374)

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

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

at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runUpdateQuery(ClientDBUtilities.java:189)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:181)

... 25 more

Traceback (most recent call last):
File "", line 14, in
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:183)

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.runUpdateQuery(INSERT INTO predictive_test_data (station_number, line_number, test_ID, test_result_value, test_date_and_time) VALUES (10,'s000001',21 ,1543 ,'2019.08.20 10:10:34'), , , false)

2

Cannot run this function when in read-only mode.

For one thing, your designer has to be in Read/Write communications mode.

Another is how you're calling system.db.runUpdateQuery, it doesn't take row data like that. Check out the documentation for it.

If you want to insert variables into your query, you'll need to use string interpolation like such:

query = """
INSERT INTO predictive_test_data (station_number, line_number, test_ID, test_result_value, test_date_and_time)
VALUES {}, 'plm_num', {}, {}, 'datetime'
""".format(st_number, test_id, test_value)

(Also note that you can use multi-line strings in Python to make this much cleaner.

3

Use RunPrepUpdate when using ’ VALUES ?, ?’…

4

thank you for your Reply, I Change the command to runPrepUpdate then I gor new error :slight_smile:

Traceback (most recent call last):
File “”, line 15, in
TypeError: not all arguments converted during string formatting

5

now it’s working when I do the following Change:

System.db.runPrepUpdate(query,row_data).
:+1::+1: