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

**URL:** <https://forum.inductiveautomation.com/t/error-inserting-data-into-sql-table-conversion-from-unknown-to-unknown-unsupported/22960>\
**Category:** Ignition\
**Created:** [February 28, 2019, 8:58am UTC](https://forum.inductiveautomation.com/t/error-inserting-data-into-sql-table-conversion-from-unknown-to-unknown-unsupported/22960 "2019-02-28T08:58:14Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [February 28, 2019, 8:58am UTC](https://forum.inductiveautomation.com/t/error-inserting-data-into-sql-table-conversion-from-unknown-to-unknown-unsupported/22960/1 "2019-02-28T08:58:14Z")

</div>

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

```auto
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:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/d/dc7bd4b804121e8d13cee942daff6525e6515a4b.png)

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…

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

```

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/c/c371260f375e8d7ddc159f3ba9b21933651b9a20.png)

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [February 28, 2019, 9:11am UTC](https://forum.inductiveautomation.com/t/error-inserting-data-into-sql-table-conversion-from-unknown-to-unknown-unsupported/22960/2 "2019-02-28T09:11:02Z")

</div>

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

```auto
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')))

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 28, 2019, 1:40pm UTC](https://forum.inductiveautomation.com/t/error-inserting-data-into-sql-table-conversion-from-unknown-to-unknown-unsupported/22960/3 "2019-02-28T13:40:23Z")

</div>

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

```auto
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 ....

```

---

<div class="post-metadata">

**Author:** ![Sanderd17](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sanderd17/32/8735_2.png) [@Sanderd17](https://forum.inductiveautomation.com/u/Sanderd17)\
**Post date:** [February 28, 2019, 1:48pm UTC](https://forum.inductiveautomation.com/t/error-inserting-data-into-sql-table-conversion-from-unknown-to-unknown-unsupported/22960/4 "2019-02-28T13:48:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [February 28, 2019, 7:55pm UTC](https://forum.inductiveautomation.com/t/error-inserting-data-into-sql-table-conversion-from-unknown-to-unknown-unsupported/22960/5 "2019-02-28T19:55:46Z")

</div>

Awesome, thanks guys!

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [February 28, 2019, 10:36pm UTC](https://forum.inductiveautomation.com/t/error-inserting-data-into-sql-table-conversion-from-unknown-to-unknown-unsupported/22960/6 "2019-02-28T22:36:51Z")

</div>

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:

```auto
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.