Unknown SQL type: -151 When SQL Query returns a datetime

Ignition
1

I’m trying to get some of our v7.9.12 vision screens to run on v8.0.7. I’m getting “Unknown SQL type: -151” exceptions whenever a SQL query is to return a “datetime”.

Has anyone ran into this?

Background: This Gateway has been running for a while with Perspective only. No SQL queries that return a “datetime”. We recently purchased the Vision Module for it so we can transition from version 7 to version 8 without risk of downtime.

Here is the Console output:

08:56:50.359 [AWT-EventQueue-0] ERROR com.inductiveautomation.factorypmi.application.binding.AbstractPropertyAdapter - null
java.lang.Exception: Error running query:
SQLQuery(query=SELECT [Period]
,[PeriodEnd]
,[PeriodStart]
,[Year]
FROM [PeriodCalendar], database=ProductionData)@0ms
On: SQL Unknown Type.Root Container.Table.data
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:309)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Unknown SQL type: -151
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.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:843)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:814)
at com.inductiveautomation.factorypmi.application.gateway.SQLQuery.execute(SQLQuery.java:50)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.execute(QueryManager.java:657)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:306)
… 1 common frames omitted
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayResponse$GatewayThrowable: Unknown SQL type: -151
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunQuery.run(RunQuery.java:118)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:68)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:411)
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:86)
at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:844)
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:544)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:536)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:235)
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1581)
at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:233)
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1307)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:188)
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:482)
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1549)
at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:186)
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1204)
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:322)
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:59)
at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:146)
at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:127)
at org.eclipse.jetty.server.Server.handle(Server.java:494)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:374)
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:268)
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:311)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.runTask(EatWhatYouKill.java:336)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.doProduce(EatWhatYouKill.java:313)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.tryProduce(EatWhatYouKill.java:171)
at org.eclipse.jetty.util.thread.strategy.EatWhatYouKill.run(EatWhatYouKill.java:129)
at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:367)
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:782)
at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:918)
at java.lang.Thread.run(null)

2

Wild shot in the dark, but as someone who moved a 7.9 project to 8.0, is the database your querying set correctly in the tag? I see you are using database=ProductionData, is that what the new database you have configured is called on your gateway? I ask because it looks like your query is returning a null value, is that what’s happening now?

3

I don’t believe that is the issue, because if I cast the datetime as a varchar in the same Query it returns values.

Query that works:
‘’‘SELECT [Period]
,cast([PeriodEnd] as varchar) as ‘PeriodEnd’
,cast([PeriodStart] as varchar) as ‘PeriodStart’
,[Year]
FROM [PeriodCalendar]’’’

Query that doesn’t work:
‘’‘SELECT [Period]
,[PeriodEnd]
,[PeriodStart]
,[Year]
FROM [PeriodCalendar]’’’

4

Is the actual datatype datetime or datetime2?

5

SQL Server 2005, datetime

6

Some new info: This only seems to be a problem if I’m querying our SQL Server 2005 database. SQL Server 2014 and MySQL seem to work fine. However most of the data I need is on the 2005 server.

Did anything change in the database driver between 7.9.12 and 8.0.7? Making it incompatible with SQL Server 2005.

7

If you did an upgrade it should be the same JDBC driver, but maybe if you did a fresh install of 8 it could be a newer one.

8

It looks like I have JDBC 4.2 compliant driver on the 8.0.7 Gateway.
And JDBC 4.0 compliant driver on the 7.9.12 Gateway.

Anyone know if 4.2 compliant driver works with SQL Server 2005? If not how do I fix it.

9

What’s the driver version though?

It’s probably not good that Microsoft doesn’t even list SQL Server 2005 in its compatibility matrix: https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server-support-matrix?view=sql-server-ver15

10

How do I find the driver version?

11

Huh, that’s annoying. I thought it was listed somewhere in the gateway.

You can look at the filename of the driver in rhe $IGNITION/user-lib/jdbc folder I guess. The SQL Server JDBC JAR that came with a fresh install of Ignition 8.0.7 is version 7.2.1.

12

Try uninstalling the more recent JDBC driver and install the old version you have on 7.9, which is probably version 4.1.

An upgrade would have left the old driver in place.

1 Like
13

That did the trick!

Thank you

14

Great, count yourself a little lucky here. You need to seriously consider getting that SQL Server 2005 instance upgraded because 1) you can’t connect to newer versions with this old driver and 2) this old driver may stop working in some future Java version or other Ignition update if a newer version of JDBC becomes a requirement. This technical debt could be a serious liability.

1 Like
15

I have just found myself in a situation where I have a 2005 SQL Server Express install that exists as a stand alone system. I was hoping to use a series of transaction groups to replicate the data into our main production system (2019), but I ran into this same incompatibility.