Audit Event table insert with value changed event script

simon.beeching · April 30, 2019, 7:11am

I’m getting the following log

([default]CoreCert/Station 1/Part Code, valueChanged) Error executing tag event script: Traceback (most recent call last): File "<tagevent:valueChanged>", line 20, in valueChanged at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at 
com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258) at sun.reflect.GeneratedMethodAccessor134.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO AUDIT_EVENTS_TagChanges (EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,ACTION_VALUE,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?), [current_timestamp, 'BOB BUILDER', 'HOSTNAME', 'Part Code Changed', '[default]CoreCert/Station 1/Part Code', 'TEST', '0', 'TEST', '4'], CoreCert_DB, , false, false)

I’ve tried with single talking marks around the values and without, incidentally cutting and pasting the SQL in the error into MSSQL Management Studio and change the ‘INSERT INTO AUDIT_EVENTS_TagChanges’ into ‘INSERT INTO [dbo].[AUDIT_EVENTS_TagChanges]’ and the query runs fine.

Script looks like

system.db.runPrepUpdate("INSERT INTO AUDIT_EVENTS_TagChanges (EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,ACTION_VALUE,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?)", [EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,ACTION_VALUE,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT], "CoreCert_DB")

all the values are not null.

Any thoughts? I think I’ve hit this pretty much every way I can think of. Also ‘legacy database’ permissions are enabled on the project.

v7.9.10, MSSQL Server and Windows Server/Clients

pturmel · April 30, 2019, 12:41pm

We would need the rest of the logged error to see what MSSQL is complaining about. Please post it with triple back-quotes on lines above and below to trigger the forum’s pretty formatting (you can edit your OP, too).

simon.beeching · April 30, 2019, 10:37pm

This is the error block in total from the log.

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 20, in valueChanged at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258) at sun.reflect.GeneratedMethodAccessor134.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO AUDIT_EVENTS_TagChanges (EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,ACTION_VALUE,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?), [current_timestamp, BOB BUILDER, HOSTNAME, Part Code Changed, [default]CoreCert/Station 1/Part Code, TEST, 0, TEST, 4], CoreCert_DB, , false, false)

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

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

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

at sun.reflect.GeneratedMethodAccessor134.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:186)

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

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

at org.python.pycode._pyx15.valueChanged$1(:20)

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

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

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

at org.python.core.PyFunction.function___call__(PyFunction.java:376)

at org.python.core.PyFunction.__call__(PyFunction.java:371)

at org.python.core.PyFunction.__call__(PyFunction.java:361)

at org.python.core.PyFunction.__call__(PyFunction.java:356)

at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:649)

at com.inductiveautomation.ignition.common.sqltags.scripts.TagScriptManager$FunctionInvokerImpl.run(TagScriptManager.java:493)

at com.inductiveautomation.ignition.common.sqltags.scripts.AbstractTagScript.invoke(AbstractTagScript.java:33)

at com.inductiveautomation.ignition.common.sqltags.scripts.TagScriptManager$Task.invoke(TagScriptManager.java:442)

at com.inductiveautomation.ignition.common.sqltags.scripts.TagScriptManager$TagScriptDispatcher.run(TagScriptManager.java:405)

at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$ThrowableCatchingRunnable.run(BasicExecutionEngine.java:518)

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

at java.util.concurrent.FutureTask.run(Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(Unknown Source)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)

Caused by: org.python.core.PyException: Traceback (most recent call last): File "", line 20, in valueChanged at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258) at sun.reflect.GeneratedMethodAccessor134.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO AUDIT_EVENTS_TagChanges (EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,ACTION_VALUE,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?), [current_timestamp, BOB BUILDER, HOSTNAME, Part Code Changed, [default]CoreCert/Station 1/Part Code, TEST, 0, TEST, 4], CoreCert_DB, , false, false)

... 30 common frames omitted

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO AUDIT_EVENTS_TagChanges (EVENT_TIMESTAMP,ACTOR,ACTOR_HOST,ACTION,ACTION_TARGET,ACTION_VALUE,STATUS_CODE,ORIGINATING_SYSTEM,ORIGINATING_CONTEXT) VALUES (?,?,?,?,?,?,?,?,?), [current_timestamp, BOB BUILDER, HOSTNAME, Part Code Changed, [default]CoreCert/Station 1/Part Code, TEST, 0, TEST, 4], CoreCert_DB, , false, false)

... 29 common frames omitted

Caused by: java.sql.SQLException: Datasource "CoreCert_DB" does not exist in this Gateway.

at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnection(DatasourceManagerImpl.java:139)

at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:88)

at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.getConnection(GatewayDBUtilities.java:73)

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

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

... 27 common frames omitted

I have also tried NamedQueries as well with no luck (the test function works in the namedquery builder, but the query doesn’t insert anything when triggered from the tag change, it doesn’t even leave an error log in my case (probably a whole seperate issue). I’m going to attempt the following test to simplify things

Create a simple table named testTable (Id, inputstring, inputint, inputfloat)
Create a NamedQuery to call an insert on that table
Trigger that NamedQuery from a tag changed event

simon.beeching · April 30, 2019, 10:38pm

Interesting, I just looked at the color coded error and saw ‘CoreCert_DB’ doesn’t exist… Hmmm. OK I have more ideas now