How to resolve the error "java.sql.SQLException: Unable to convert between net.sourceforge.jtds.jdbc.DateTime and BINARY" received when retrieving data from Microsoft SQL Server using a JTDS driver?

How to resolve the error "java.sql.SQLException: Unable to convert between net.sourceforge.jtds.jdbc.DateTime and BINARY" received when retrieving data from Microsoft SQL Server using a JTDS driver?

book

Article ID: KB0071359

calendar_today

Updated On:

Products

TIBCO Data Virtualization

Description

After setting up a successful connection to an Microsoft SQL server database in TIBCO Data Virtualization(TDV) Studio using a JTDS driver, retrieving data from the introspected tables, may fail with the below error:
-------------------------------
Unable to retrieve result from data source "/shared/AB/A_/PowerBI_logs/table_1" during query execution.  Failed query: "SELECT [table1].[dbo].[History].[HistoryID],[table1].[dbo].[History].[ID],[table1].[dbo].[History].[Type],[table1].[dbo].[History].[StartTime] FROM [table1].[dbo].[History]".  [data-3923000]
Cause: java.sql.SQLException: Unable to convert between net.sourceforge.jtds.jdbc.DateTime and BINARY.
-------------------------------

Complete stack trace from the cs_server.log file:
-------------------------------
ERROR [jetty thread pool-625] 2023-05-23 10:09:15.824 +0200 Util - null
com.compositesw.data.DataRuntimeException: Unable to retrieve result from data source "/shared/AB/A_/PowerBI_logs/table_1" during query execution.  Failed query: "SELECT [table1].[dbo].[History].[HistoryID],[table1].[dbo].[History].[ID],[table1].[dbo].[History].[Type],[table1].[dbo].[History].[StartTime] FROM [table1].[dbo].[History]".  [data-3923000]
    at com.compositesw.cdms.webapi.service.PrefetchCursor$LoadPipeThread.run(PrefetchCursor.java:292) ~[cswebapi-server.jar:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
    at java.lang.Thread.run(Thread.java:834) [?:?]
Caused by: com.compositesw.data.DataRuntimeException: Unable to retrieve result from data source "/shared/BI_Beheer/A_Introspectie/PowerBI_logs/rivm-pwrbi-w02p_Copy_1" during query execution.  Failed query: "SELECT [table1].[dbo].[History].[HistoryID],[table1].[dbo].[History].[ID],[table1].[dbo].[History].[Type],[table1].[dbo].[History].[StartTime] FROM [table1].[dbo].[History]".  [data-3923000]
    at com.compositesw.server.qe.QueryResult.next(SourceFile:447) ~[csqe.jar:?]
    at com.compositesw.cdms.webapi.service.PrefetchCursor$LoadPipeThread.run(PrefetchCursor.java:272) ~[cswebapi-server.jar:?]
    ... 3 more
Caused by: com.compositesw.cdms.datasource.IafRuntimeException: Unable to retrieve result from data source "/shared/AB/A_/PowerBI_logs/table_1" during query execution.  Failed query: "SELECT [table1].[dbo].[History].[HistoryID],[table1].[dbo].[History].[ID],[table1].[dbo].[History].[Type],[table1].[dbo].[History].[StartTime] FROM [table1].[dbo].[History]".  [data-3923000]
    at com.compositesw.cdms.ds.jdbc.JdbcConnectionTableCursor.next(JdbcConnectionTableCursor.java:249) ~[csmodule_datasource.jar:?]
    at com.compositesw.data.cursor.MeterCursor.next(MeterCursor.java:86) ~[cscommon.jar:?]
    at com.compositesw.server.qe.physical.operator.SqlScan.next(SourceFile:785) ~[csqe.jar:?]
    at com.compositesw.server.qe.topology.SelectPTree.nextInternal(SourceFile:438) ~[csqe.jar:?]
    at com.compositesw.server.qe.topology.SelectPTree.next(SourceFile:417) ~[csqe.jar:?]
    at com.compositesw.server.qe.QueryResult.next(SourceFile:409) ~[csqe.jar:?]
    at com.compositesw.cdms.webapi.service.PrefetchCursor$LoadPipeThread.run(PrefetchCursor.java:272) ~[cswebapi-server.jar:?]
    ... 3 more
Caused by: java.sql.SQLException: Unable to convert between net.sourceforge.jtds.jdbc.DateTime and BINARY.
    at net.sourceforge.jtds.jdbc.Support.convert(Support.java:747) ~[?:?]
    at net.sourceforge.jtds.jdbc.JtdsResultSet.getBytes(JtdsResultSet.java:751) ~[?:?]
    at com.compositesw.cdms.ds.jdbc.shunt.SqlServerRawDateTimeShunt.jdbc2Value(SqlServerRawDateTimeShunt.java:67) ~[csmodule_datasource.jar:?]
    at com.compositesw.cdms.ds.jdbc.JdbcConnectionTableCursor.next(JdbcConnectionTableCursor.java:224) ~[csmodule_datasource.jar:?]
    at com.compositesw.data.cursor.MeterCursor.next(MeterCursor.java:86) ~[cscommon.jar:?]
    at com.compositesw.server.qe.physical.operator.SqlScan.next(SourceFile:785) ~[csqe.jar:?]
    at com.compositesw.server.qe.topology.SelectPTree.nextInternal(SourceFile:438) ~[csqe.jar:?]
    at com.compositesw.server.qe.topology.SelectPTree.next(SourceFile:417) ~[csqe.jar:?]
    at com.compositesw.server.qe.QueryResult.next(SourceFile:409) ~[csqe.jar:?]
    at com.compositesw.cdms.webapi.service.PrefetchCursor$LoadPipeThread.run(PrefetchCursor.java:272) ~[cswebapi-server.jar:?]
    ... 3 more
-------------------------------
 

Issue/Introduction

How to resolve the error "java.sql.SQLException: Unable to convert between net.sourceforge.jtds.jdbc.DateTime and BINARY" received when retrieving data from Microsoft SQL Server using a JTDS driver?

Resolution

Steps to resolve the error:

(i). Launch TDV Studio and navigate to the below configuration :  Administration > Configuration > Data Sources > MS SQL Server Sources > Performance > Enable Pass Thru Optimizations 
(ii). Change the configuration setting to FALSE 
(iii). Click Apply and OK
(iv). Test the table for results

The configuration "Enable Pass Thru Optimizations" setting when set to TRUE makes the TDV server read the DATE, DATETIME, DATETIME2, SMALLDATETIME, TIME, MONEY, and SMALLMONEY data type column values as bytes and then convert it to appropriate values instead of directly fetching those columns as corresponding values. The JTDS driver does not support this conversion. The default value for this configuration is TRUE.