runPrepUpdate Insert Failing

Ignition
1

This insert query keeps failing, can anyone see why? I’m executing this from an SFC.

[code]
res = chart.resource
str = chart.startTime
staID = chart.staID
jobNum = chart.jobNum

system.db.runPrepUpdate("INSERT INTO help_events (res_id, event_str, sta_id, job_num) VALUES (?,?,?,?)",
[res, str, staID, jobNum], 'Wentzville_MO')[/code]

[code] 1:40:22 PM com.inductiveautomation.sfc.ChartInstance Chart ‘Wentzville/HelpSeq’ aborted.

Traceback (most recent call last):
File “”, line 15, in onStart
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:362)

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

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.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 help_events (res_id, event_str, sta_id, job_num) VALUES (?,?,?,?), [2, Fri Feb 05 13:40:22 CST 2016, 0, 0], Wentzville_MO, , 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:260)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.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:428)
at org.python.core.PyObject.call(PyObject.java:422)
at org.python.core.PyObject.call(PyObject.java:426)
at org.python.pycode.pyx3682.onStart$1(:15)
at org.python.pycode.pyx3682.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:637)
at com.inductiveautomation.sfc.elements.steps.StepScriptRunner.invoke(StepScriptRunner.java:44)
at com.inductiveautomation.sfc.elements.steps.StepScriptRunner.invoke(StepScriptRunner.java:32)
at com.inductiveautomation.sfc.elements.steps.action.ActionStep.activateStep(ActionStep.java:53)
at com.inductiveautomation.sfc.elements.StepContainer.activateStep(StepContainer.java:46)
at com.inductiveautomation.sfc.elements.AbstractBasicStepElement.onActivateRequested(AbstractBasicStepElement.java:92)
at com.inductiveautomation.sfc.fsm.element.ElementInactive.action(ElementInactive.java:33)
at com.inductiveautomation.sfc.fsm.element.ElementInactive.action(ElementInactive.java:8)
at com.inductiveautomation.sfc.fsm.StateContext.lambda$handleEvent$14(StateContext.java:52)
at com.inductiveautomation.sfc.fsm.StateContext$$Lambda$721/1486015562.run(Unknown Source)
at com.inductiveautomation.sfc.api.ExecutionQueue$PollAndExecute.run(ExecutionQueue.java:111)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.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: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO help_events (res_id, event_str, sta_id, job_num) VALUES (?,?,?,?), [2, Fri Feb 05 13:40:22 CST 2016, 0, 0], Wentzville_MO, , false, false)
… 34 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:946)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:958)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObjectNoType(SQLServerPreparedStatement.java:910)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:919)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.setObject(SRConnectionWrapper.java:971)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepUpdate(SRConnectionWrapper.java:160)
at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities._runPrepStmt(GatewayDBUtilities.java:108)
at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:258)[/code]

2

What data types are being used by the 4 columns in the database table?

Perhaps a Python type you are using doesn’t correspond to a column type in the database table.

Best,

3

In fact your “event_str” column seems like it might be a string data type like a VARCHAR. But the value you are trying to insert is a Java Date, not a string. That won’t work. You will need to convert the date object to a string first, before trying to insert it.