runUpdateQuery Error with Extra Parameters

Ignition
1

I have an update query that is scheduled to run every day at 5am. It's purpose is to prune data from an SQL Express Database. It's done daily so that the load for the query is more reasonable. It seems like it's running, but not deleting the rows. Eventually, it gets to the point where the query times out, and that time out error pops up in the log. Nothing else in the logs. If I run the same script from a button on the screen it runs without an error popup. When I run the same DELETE query in SSMS it works as expected.

#get the most recent date in the table
date = system.db.runQuery("SELECT TOP 1 t_stamp FROM Data ORDER BY t_stamp DESC")[0][0]

#calculate the pruning date for Data table and Chem table
dataPruneDate = system.date.format(system.date.addMonths(date, -3), "yyyy-MM-dd")

#create query stings for Data table and Chem table
dataQueryString = str("DELETE Data WHERE t_stamp < '" + dataPruneDate + "'")

#run query for Data table and Chem table
system.db.runUpdateQuery(dataQueryString)

The log error shows some extra parameters for runQuery()

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 15, in onScheduledEvent 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 Data WHERE t_stamp < '2022-10-11', AppDataLog, , false)
2

Should your query be DELETE FROM Data WHERE t_stamp...? (I've included FROM)

Beyond that, would this work? It would delete anything older than 3 months (rather than 3 months older than your most recent record), but it simplifies things quite a lot.

system.db.runUpdateQuery(
    'DELETE FROM Data WHERE t_stamp < DATEADD(MONTH, -3, CURRENT_TIMESTAMP)'
)

If not, something close to this would probably work.

#get the most recent date in the table
date = system.db.runQuery("SELECT TOP 1 t_stamp FROM Data ORDER BY t_stamp DESC")[0][0]

#calculate the pruning date for Data table and Chem table
dataPruneDate = system.date.addMonths(date, -3)

#create query stings for Data table and Chem table
dataQueryString = "DELETE FROM Data WHERE t_stamp < ?"

#run query for Data table and Chem table
system.db.runPrepUpdate(dataQueryString,[dataPruneDate])
3

The FROM word was not required for it to run in SSMS. I'll see if it runs in SSMS with the FROM keyword

4

It works in SSMS and seems to run without error from a button as well, but it's hard to know if it's actually working. Based on the timestamps, though it does look like it got everything in the last 3 months.

5

Why is it hard to tell if it's working or not? system.db.runUpdateQuery returns the number of rows affected by the query, you should consider printing this out somewhere to verify it's working.