Error with system.db.runUpdateQuery

[Ly17]

Hi, I'm trying to insert data into sql db on tag value changed and I'm getting this error.

("TAGPATH", 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:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runUpdateQuery(AbstractDBUtilities.java:188) at jdk.internal.reflect.GeneratedMethodAccessor205.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 group_table (ShiftStart, ShiftEnd, ShiftNo, Takt, ScheduledUnit, Target, Completed, DraggingUnit, WorkingDate, t_stamp)VALUES ('Fri Aug 18 06:00:00 CDT 2023','Fri Aug 18 17:00:00 CDT 2023','1','129','220','220','None','0','Fri Aug 18 09:17:03 CDT 2023','Fri Aug 18 09:17:03 CDT 2023'), "DATABASE", , false)

What does this error mean?

[PGriffith]

Are you sure that's the entire error you're getting?

[Ly17]

Here is the rest of it.

com.inductiveautomation.ignition.common.script.JythonExecException: Traceback (most recent call last): File "", line 22, 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.GeneratedMethodAccessor205.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 group_table (ShiftStart, ShiftEnd, ShiftNo, Takt, ScheduledUnit, Target, Completed, DraggingUnit, WorkingDate, t_stamp)VALUES ('Fri Aug 18 06:00:00 CDT 2023','Fri Aug 18 17:00:00 CDT 2023','1','129','200','200','0','0','Fri Aug 18 10:05:04 CDT 2023','Fri Aug 18 10:05:05 CDT 2023'), VersaCall_LKC, , 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 jdk.internal.reflect.GeneratedMethodAccessor205.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:552)

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

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

at org.python.pycode._pyx31.valueChanged$1(:22)

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

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

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

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

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

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

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

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

at com.inductiveautomation.ignition.gateway.tags.scripting.TagScriptManagerImpl$FunctionInvokerImpl.run(TagScriptManagerImpl.java:533)

at com.inductiveautomation.ignition.gateway.tags.scripting.events.AbstractTagScript.invoke(AbstractTagScript.java:34)

at com.inductiveautomation.ignition.gateway.tags.scripting.TagScriptManagerImpl$Task.invoke(TagScriptManagerImpl.java:482)

at com.inductiveautomation.ignition.gateway.tags.scripting.TagScriptManagerImpl$TagScriptDispatcher.run(TagScriptManagerImpl.java:445)

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

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

at java.base/java.util.concurrent.FutureTask.run(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.runUpdateQuery(INSERT INTO group_table (ShiftStart, ShiftEnd, ShiftNo, Takt, ScheduledUnit, Target, Completed, DraggingUnit, WorkingDate, t_stamp)VALUES ('Fri Aug 18 06:00:00 CDT 2023','Fri Aug 18 17:00:00 CDT 2023','1','129','200','200','0','0','Fri Aug 18 10:05:04 CDT 2023','Fri Aug 18 10:05:05 CDT 2023'), VersaCall_LKC, , false)

... 30 common frames omitted

Caused by: java.lang.Exception: Error executing system.db.runUpdateQuery(INSERT INTO group_table (ShiftStart, ShiftEnd, ShiftNo, Takt, ScheduledUnit, Target, Completed, DraggingUnit, WorkingDate, t_stamp)VALUES ('Fri Aug 18 06:00:00 CDT 2023','Fri Aug 18 17:00:00 CDT 2023','1','129','200','200','0','0','Fri Aug 18 10:05:04 CDT 2023','Fri Aug 18 10:05:05 CDT 2023'), VersaCall_LKC, , false)

... 29 common frames omitted

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.

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.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:898)

at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:793)

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.SQLServerStatement.executeUpdate(SQLServerStatement.java:733)

at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:341)

at org.apache.commons.dbcp2.DelegatingStatement.executeUpdate(DelegatingStatement.java:341)

at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeUpdate(DelegatingStatement.java:80)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeUpdate(SRConnectionWrapper.java:867)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runUpdateQuery(SRConnectionWrapper.java:219)

at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._runUpdateQuery(GatewayDBUtilities.java:251)

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

... 27 common frames omitted

[PGriffith]

@Ly17

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.

You're calling runUpdateQuery, not runPrepUpdate. I would strongly suggest you move to runPrepUpdate - in addition to giving you type-safe insertion (if you need to insert a datetime, you just use a java Date object as a parameter), it's also better for performance, in both Ignition and on the DB.

[Transistor]

Tip: Use code formatting to have your error logs show up in a code window. It will apply a scrollbar when the number of lines exceeds a certain value and eliminates a pile of scrolling to get past the error log.

Wiki - how to post code on this forum

This thread is intended as a short help file for all those struggling to post formatted code on the forum. Posting formatted code on the forum (and most others) is as simple as selecting the code and pressing the </> button. It works the same as applying Bold or Italic styles to your document. It preserves indentation and applies syntax highlighting. [How to post code]

[Ly17]

Thanks. I just tested this and that was the issue.