system.db.runUpdateQuery stopped working?

Ignition
1

I've had a script for a few tags writing to an SQL database, yesterday they randomly stopped working and I'm not sure what the cause is.

I find this error in the diagnostics.

Error executing script.
Traceback (most recent call last):
File "tagevent:valueChanged", line 10, in valueChanged
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 jdk.internal.reflect.GeneratedMethodAccessor516.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 DataCollectionCounts VALUES ('UCPRESS', 'GOODPART', '2024-10-23 09:38:59.814'), Live_CEPDataEntry, , false)

This is the code in the script,

	from java.text import SimpleDateFormat
	qv = currentValue.value
	outputFormat=SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS")
	Current_datetime = system.date.now()
	Current_datetime_STR=outputFormat.format(Current_datetime) 

	if qv:
		system.db.runUpdateQuery("INSERT INTO DataCollectionCounts VALUES ('UCPRESS', 'GOODPART', '%s')" % (Current_datetime_STR),"Live_CEPDataEntry")
2

Show the full error stack trace. There will be a "Caused By:" part that will tell us exactly what went wrong.

Also highly recommend using the prep version of update query. Then you do not have to do any string formatting of dates, you can pass along system.date.now() directly as an arg to your query. Or use Named Queries and same thing. No string formatting necessary. See here - system.db.runPrepUpdate | Ignition User Manual

1 Like
3

And if you do need to format the date (which should really only be done at the point of display for human consumption) use the Ignition built-in function, system.date.format | Ignition User Manual, which avoids loading an external library.

3 Likes
4

In addition to this, you really shouldn't touch a DB in value change events.

This script should be in a Project Script Library, and called from a Gateway Tag Change event.

Script should really be just this:

if currentValue.value and not initialChange:
    query = "INSERT INTO DataCollectionCounts VALUES('UCPRESS','GOODPART',?)"
    system.db.runPrepUpdate(query,[system.date.now()],"Live_CEPDataEntry")

Though in a Gateway Tag Change Event, you would replace currentValue with newValue

Note my use of the initialChange flag, this will block the query from running when the tag is initialized, you could be getting false records without it.

1 Like
5

True, every time the value changes it has to from java.text import SimpleDateFormat again whereas in a project script library that would be cached. However it shouldn't be used anyways as @Transistor pointed out.

Given the other two values inserted are hardcoded strings, my money is on the date being the issue here.