SQL Query Error

I am getting an “error converting varchar to bigint” on my query on tag history. It is happening in the where clause. t_stamp is a date, intvalue is an bigint, dataintegrity is an int.

Sample of row data and error below.

tagid intvalue floatvalue stringvalue datevalue dataintegrity t_stamp
28 920 NULL NULL NULL 192 1405616502411

ERROR [QueryBrowser$6$3-AWT-EventQueue-2] Error running query:
SELECT t_stamp, intvalue, dataintegrity
FROM dbo.sqlt_data_1_2014_07
Where t_stamp >= ‘{Root Container.Date Range.startDate}’ And t_stamp <= '{Root Container.Date Range.endDate}'
Error converting data type varchar to bigint.
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Error converting data type varchar to bigint.
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:313)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:244)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:712)
at com.inductiveautomation.ignition.designer.querybrowser.QueryBrowser$6.run(QueryBrowser.java:481)
at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type varchar to bigint.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4700)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1683)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:956)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:169)
at com.inductiveautomation.ignition.gateway.datasource.DelegatingResultSet.next(DelegatingResultSet.java:419)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunQuery.run(RunQuery.java:143)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:76)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:376)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(null)

what does the DB structure look like?

Looks like you are probably using a date string in your query, but the Ignition History tables store the date/time numerically rather than in a DateTime field.

The query had to be modified to put the date in the correct format:

SELECT intvalue, dateadd(s,t_stamp/1000,‘1970-01-01 00:00:00’) as t_stamp from MyDB.dbo.sqlt_data_1_2014_07
where cast(dateadd(s,t_stamp/1000,‘1970-01-01 00:00:00’) as datetime) >= ‘{Root Container.Date Range.startDate}’
and cast(dateadd(s,t_stamp/1000,‘1970-01-01 00:00:00’) as datetime) <= ‘{Root Container.Date Range.endDate}’
and tagid = 29

1 Like

Examples:

Missed this back in 2014… If you want your queries to run efficiently, convert your parameters to the data type needed in the DB, don’t make the DB convert every t_stamp value to a datetime. Simply use toMillis() on the start and end date in custom (Long) properties, and pass those to the WHERE clause.