runUpdateQuery Error

Ignition
, ,
1

Running Ignition 8.1.16 with Vision. Have 2 DB tables. One successfully runs this query from script but the other one errors for a reason I do not know.

works:

	if globalVars.logger:
	
		#get today's date
		date = system.date.format(system.date.now(),"yyyy_MM_dd")
		
		#select rows older than 3 months
		chemSelectStatement = "SELECT t_stamp, chem_code, chemMeter_val, chemPump_val, conc_str, meter_val, pump_val, sample_str FROM Chem WHERE t_stamp < DATEADD(MONTH, -"+str(globalVars.chemTrendAge)+", CURRENT_TIMESTAMP)"
		
		#select the data
		chemDataToDrop = system.db.runQuery(chemSelectStatement, "AppDataLog")

		#delete records
		deleteChem = "DELETE FROM Chem WHERE t_stamp < DATEADD(MONTH, -"+str(globalVars.chemTrendAge)+", CURRENT_TIMESTAMP)"
		system.db.runUpdateQuery(deleteChem,"AppDataLog")

errors:

	if globalVars.logger:
	
		#get today's date
		date = system.date.format(system.date.now(),"yyyy_MM_dd")
	
		#delete records
		deleteData = "DELETE FROM Data WHERE t_stamp < DATEADD(MONTH, -"+str(globalVars.dataTrendAge)+", CURRENT_TIMESTAMP)"
		system.db.runUpdateQuery(deleteData,"AppDataLog")

Error:

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

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

	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(DELETE FROM Data WHERE t_stamp < DATEADD(MONTH, -3, CURRENT_TIMESTAMP), AppDataLog, , 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.runUpdateQuery(AbstractDBUtilities.java:188)

	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._pyx59.f$0(<input>:5)

	at org.python.pycode._pyx59.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.runUpdateQuery(DELETE FROM Data WHERE t_stamp < DATEADD(MONTH, -3, CURRENT_TIMESTAMP), AppDataLog, , 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._runUpdateQuery(ClientDBUtilities.java:262)

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

	... 24 more

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

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

	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(DELETE FROM Data WHERE t_stamp < DATEADD(MONTH, -3, CURRENT_TIMESTAMP), AppDataLog, , false)

Data DB table format:

image
image185×108 4.36 KB

2

Cannot run this function when in read-only mode.

Something, somewhere is setting that client to read-only, almost certainly via

3

Thank you

4

Why is this not a Prep query?

5

Not sure. Please enlighten me. Somewhat of a DB novice

6

You are string formatting to create a date for DB usage. You are also passing a numeric value into a query with string concatenation. Pass dates into queries as objects using a ? placeholder, comparing directly to your t_stamp column. Use system.date.midnight() to get a date with zeros in the time parts. Compute the month delta in Ignition with system.date.addMonths(). Pass both endpoints as arguments to system.db.runPrepUpdate().

With limited exceptions, using string concatenation or formatting for values in SQL is simply wrong.

7

To illustrate runPrepUpdate(). It also makes it easier to read, IMO.

if globalVars.logger:
	
		#get today's date
		date = system.date.format(system.date.now(),"yyyy_MM_dd")
		#set cutoff date
		cutoffDate = system.date.addMonths(date, -globalVars.dataTrendAge)
	
		#delete records
		deleteData = "DELETE FROM Data WHERE t_stamp < ?"
		system.db.runPrepUpdate(deleteData, [cutoffDate], "AppDataLog")