Error INSERTing data into SQL table: Conversion from UNKNOWN to UNKNOWN unsupported

nminchin · February 28, 2019, 8:58am

I’m getting the below exception error when my script is attempting to execute the line:

system.db.runPrepUpdate(INSERT INTO datSeqLogData (seq_logs_id, tag_id, val_int, logged_datetime) VALUES (?, ?, ?, ?), [5285, 1190, 9999, Thu Feb 28 19:15:11 ACDT 2019]

I’ve copied and pasted this into SQL management studio and this works (replaced the date with GETDATE())

This is my table definition:

I’m not sure where it’s thinking there is an UNKNOWN?

Trace below, where the 4 lines at the top just show the variable types that I’m sending into the ? parameters in the query.
It’s odd that there are two references to the script library I called as I only called it once and there is no recursive calling within it (below). Also, this whole call is actually placed inside a try/except expression, but it is still producing an exception that halts the code? If I remove the Exception, e: part, it doesn’t halt the code, but I don’t get to see what the error was…

File "<module:shared.sequences.logging>", line 184, in logSeqData
File "<module:shared.sequences.logging>", line 184, in logSeqData

seq_logs_id = <type 'int'>  =  5285
tag_id = <type 'int'>  =  1190
val_int  = <type 'long'>  =  9999
logged_datetime = <type 'java.util.Date'>  =  Thu Feb 28 19:15:11 ACDT 2019
Java Traceback:

	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.GeneratedMethodAccessor609.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:422)
	at org.python.core.PyObject.__call__(PyObject.java:426)
	at org.python.pycode._pyx235.logSeqData$1(<module:shared.sequences.logging>:199)
	at org.python.pycode._pyx235.call_function(<module:shared.sequences.logging>)
	at org.python.core.PyTableCode.call(PyTableCode.java:165)
	at org.python.core.PyBaseCode.call(PyBaseCode.java:134)
	at org.python.core.PyFunction.__call__(PyFunction.java:317)
	at org.python.pycode._pyx234.f$0(<buffer>:2)
	at org.python.pycode._pyx234.call_function(<buffer>)
	at org.python.core.PyTableCode.call(PyTableCode.java:165)
	at org.python.core.PyCode.call(PyCode.java:18)
	at org.python.core.Py.runCode(Py.java:1275)
	at org.python.core.Py.exec(Py.java:1319)
	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:215)
	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:89)
	at org.python.util.InteractiveInterpreter.runsource(InteractiveInterpreter.java:70)
	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:476)
	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$InterpreterWorker.doInBackground(JythonConsole.java:464)
	at javax.swing.SwingWorker$1.call(Unknown Source)
	at java.util.concurrent.FutureTask.run(Unknown Source)
	at javax.swing.SwingWorker.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 datSeqLogData (seq_logs_id, tag_id, val_int, logged_datetime) VALUES (?, ?, ?, ?), [5285, 1190, 9999, Thu Feb 28 19:15:11 ACDT 2019], WineDelivery, , false, false)
	... 31 more
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: SQL error for "INSERT INTO datSeqLogData (seq_logs_id, tag_id, val_int, logged_datetime) VALUES (?, ?, ?, ?)": The conversion from UNKNOWN to UNKNOWN is unsupported.
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:268)
	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:215)
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:256)
	... 29 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:1055)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunPrepStmt.runUpdateQuery(RunPrepStmt.java:66)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractUpdateQuery.run(AbstractUpdateQuery.java:37)
	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:77)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:404)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
	at org.eclipse.jetty.server.Server.handle(Server.java:518)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
	at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
	at java.lang.Thread.run(null)
Traceback (most recent call last):
  File "<buffer>", line 2, in <module>
  File "<module:shared.sequences.logging>", line 184, in logSeqData
  File "<module:shared.sequences.logging>", line 184, in logSeqData
	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.GeneratedMethodAccessor609.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 datSeqLogData (seq_logs_id, tag_id, val_int, logged_datetime) VALUES (?, ?, ?, ?), [5285, 1190, 9999, Thu Feb 28 19:15:11 ACDT 2019], WineDelivery, , false, false)

nminchin · February 28, 2019, 9:11am

If I replace the runPrepUpdate line with the runUpdateQuery function, it works… but looks awful as I need to handle different data types (formatted with multiple lines for forum display):

system.db.runUpdateQuery(
"INSERT INTO datSeqLogData (seq_logs_id, tag_id, %s, logged_datetime)
VALUES (%s, %s, %s, {TS '%s'})" %
(tag_val_type, 
str(seq_logs_id), 
str(tag_id),
"'" + str(tag_val) + "'" if tag_val_type == 'val_string' else "{TS '" + system.date.format(tag_val, 'yyyy-MM-dd HH:mm:ss') + "'}" if tag_val_type == 'val_datetime' else str(tag_val),
system.date.format(now, 'yyyy-MM-dd HH:mm:ss')))

pturmel · February 28, 2019, 1:40pm

Jython’s except Exception, e: syntax won’t catch a java exception, only a jython exception. Use something like this:

import java.lang.Exception
....  other code ....

try:
	... some code ...
except Exception, e:
	.... handle a jython exception ....
except java.lang.Exception, e:
	.... handle a java exception ....

Sanderd17 · February 28, 2019, 1:48pm

The problem is in the JDBC driver. The same error also happened when inserting values into BigInt columns into MS SQL db’s. Upgrading the JDBC driver to a later version solved the problem.

You also can still use the runPrepUpdate with the old JDBC driver, but you just have to translate the datetime into a string for yourself (something that’s understood by SQL). Or if you can alter the schema, you can use a datetime, which doesn’t give problems with the JDBC.

The other variables don’t need to be escaped as string.

PS. the except java.lang.Exception ... does work, but if you want to catch everything, an except without extra arguments also does the trick. The problem is that java.lang.Exception doesn’t inherit from Exception in Jython.

nminchin · February 28, 2019, 7:55pm

Awesome, thanks guys!

nminchin · February 28, 2019, 10:36pm

Just for others who come across the same issue, when I changed my datetimes to datetime instead of datetime2(7), this introduced issues for some of my displays, as these datetimes are actually being implicitly converted to strings when I write them to a dataset. I found that datetime and datetime2(7) data types in SQL are converted to strings differently in Ignition:

Using datetime, the value converts to a string in this format:
Mar 1 2019 7:36AM

Using datetime2(7), the value converts to a string in this format:
2019-03-01 07:36:25.0000000

I’m using this expression to display and format the date, which doesn’t work for the datetime string format:
dateFormat(toDate({SeqLogDataRow.tag2_val}), 'yyyy-MM-dd HH:mm')

The fix was to first format the datetime to a proper date format before writing it into the dataset.
Code snippet:

val_type = row['val_type']
val = row['val']
# if the val type is a datetime, format this to avoid issues when the SQL type 'datetime' is used (instead of datetime2(7))
if val_type == 'datetime':
	val = system.date.format(system.date.parse(val), 'yyyy-MM-dd HH:mm:ss')

For a bit of background, the dataset column I’m writing the datetimes into can actually contain any data type. Because Ignition datasets can only contain one data type however, the values I write in all become strings as a common denominator that can accommodate values from any data type.