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.