Bulk insert of tag valueChange events

Ignition
, ,
1

We have have about 25 000 tags that we want log into an MSSQL database using tag eventChange scripts. One requirement is that every value is to be saved into a custom structure with a unique key for each machine and machine cycle. So we can not use tag historian since you cant make those kinds of relationships.

Since there are a lot of tags that fire querys at a very high rate we wanted to create some kind of buffer so that we could bulk insert lets say 100 rows at a time instead of doing them one by one.

We tried using system.util.getGlobals were we created a global list which held all the args for an insert query so that we could once every second bulk insert whatever was there (gatewayEvent script, 1000ms fixed delay) We limited it to 200 rows pers second as we fond a limitation of 2100 args per query.

It worked perfectly in small scale but as soon as we implemented it on all of the tags the script started to fire “empty rows” and no values were inserted.

Does anyone know why this could happen or have done something similar?

the gatewayEvent looked like this:

buffer = system.util.getGlobals()['history_buffer']
row_count=len(buffer)
if row_count>200:
       max_rows=200
else:
max_rows=row_count

tag_rows=buffer[:max_rows]

SMASH_logging.logging.log_group_data(tag_rows)

del buffer[:max_rows]

Script:

def log_group_data(tag_rows):
dbCon='SMASH'
values_sql = ",".join("(?,?,?,?,?,?,?,?)" for _ in tag_rows)

query = """
INSERT INTO tbl_history
(cycle_key, int_value, real_value, bool_value, timestamp_value, tag_id, string_value, timestamp)
VALUES {}
""".format(values_sql)

# Flatten parameters
args = [item for row in tag_rows for item in row]

system.db.runPrepUpdate(query, args, dbCon)

Error code like this:


 
com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 10, in File "", line 94, in log_group_data INSERT INTO tbl_history (cycle_key, int_value, real_value, bool_value, timestamp_value, tag_id, string_value, timestamp) VALUES , 
, SMASH, , false, false) 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 jdk.internal.reflect.GeneratedMethodAccessor51.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 tbl_history (cycle_key, int_value, real_value, bool_value, timestamp_value, tag_id, string_value, timestamp) VALUES , 
, SMASH, , false, false)

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

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 jdk.internal.reflect.GeneratedMethodAccessor51.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:553)

at org.python.core.PyObject.call(PyObject.java:494)

at org.python.core.PyObject.call(PyObject.java:498)

at org.python.pycode._pyx2431085.log_group_data$4(:94)

at org.python.pycode._pyx2431085.call_function()

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

at org.python.core.PyBaseCode.call(PyBaseCode.java:134)

at org.python.core.PyFunction.call(PyFunction.java:416)

at org.python.pycode._pyx2431084.f$0(:12)

at org.python.pycode._pyx2431084.call_function()

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

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:804)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:859)

at com.inductiveautomation.ignition.common.script.ScriptManager.runCode(ScriptManager.java:752)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runCode(ProjectScriptLifecycle.java:840)

at com.inductiveautomation.ignition.common.script.TimerScriptTask.run(TimerScriptTask.java:92)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

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

at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.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: org.python.core.PyException: java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate( INSERT INTO tbl_history (cycle_key, int_value, real_value, bool_value, timestamp_value, tag_id, string_value, timestamp) VALUES , 
, SMASH, , false, false)

... 31 common frames omitted

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate( INSERT INTO tbl_history (cycle_key, int_value, real_value, bool_value, timestamp_value, tag_id, string_value, timestamp) VALUES , 
, SMASH, , false, false)

... 30 common frames omitted

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'VALUES'.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1662)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:615)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:537)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3488)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:262)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:237)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:483)

at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)

at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeUpdate(SRConnectionWrapper.java:981)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepUpdate(SRConnectionWrapper.java:181)

at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._runPrepStmt(GatewayDBUtilities.java:203)

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

I'm assuming the indentation error you have in the script after the else: is a copy paste error as you are obviously running the script.

There error says that it is a syntax error. I don't see anything imediately in the script that I would think would result in a syntax error.

I would suggest logging the actual query prior to it being run, to try and determine what the syntax error is.