MATLAB: Do I get Invalid connection Error with when querying ‘sql_variant’ data type from SQL Server using JDBC Driver

Question

I am using JDBC Driver in MATLAB R2016b to connect to SQL Server.

I am using the database function from Matlab to connect to the database:

>> databaseConnection = database(databaseName,'','','Vendor','Microsoft SQL Server',serverName,'AuthType','Windows');

When I run the exec function I get the following error: ERROR: >> exec(databaseConnection,sqlStatement) Jan 04, 2018 1:25:46 PM com.microsoft.sqlserver.jdbc.TDSParser throwUnexpectedTokenException SEVERE: ConnectionID:2 ClientConnectionId: 37d6a1b8-a218-4e23-9f61-2a83b32f8c30: getNextResult: Encountered unexpected unknown token (0x49) Jan 04, 2018 1:25:46 PM com.microsoft.sqlserver.jdbc.TDSReader throwInvalidTDSToken SEVERE: ConnectionID:2 ClientConnectionId: 37d6a1b8-a218-4e23-9f61-2a83b32f8c30 got unexpected value in TDS response at offset:61 ans =  cursor with properties:         Attributes: []               Data: 0     DatabaseObject: [1×1 database.jdbc.connection]           RowLimit: 0           SQLQuery: 'SELECT Quantity,Symbol,Price FROM PROD_SOURCEFILES WHERE Date = '2018-01-03 00:00:00' AND [Fund Code] = 'PMMN''            Message: 'The "variant" data type is not supported.'               Type: 'Database Cursor Object'          ResultSet: 0             Cursor: 0          Statement: 0              Fetch: 0

              

What is also strange is if I immediately run the same command again I just get this response: ERROR: >> exec(databaseConnection,sqlStatement) ans = struct with fields: Message: 'Invalid connection.'

I can run the same DB query from SQL Server Management Studio without any problem.

I am able to query other tables and views on same database.

It happens when I execute the following query-

>> sqlquery = SELECT Quantity,Symbol,Price FROM PROD_SOURCEFILES WHERE Date = '2018-01-04 00:00:00'

If I remove a specific column 'Price' from my SELECT statement, it works fine.

How to fix this?

 

Answer 1

The reason for this error is that the data type of column 'Price' is 'sql_variant'.

Please refer the following Microsoft article -

https://docs.microsoft.com/en-us/sql/connect/jdbc/using-basic-data-types

It explains that The SQL Server sqlvariant data type is not currently supported by the JDBC driver. If a query is used to retrieve data from a table that contains a column of the sqlvariant data type, an exception will occur.

There are two workarounds to fix this issue-

1. Change the data type of 'Price' column to varchar.

Or

2. use CAST to type cast 'Price' in SQL query.

It should be something like-

 

>> sqlquery = 'SELECT Quantity,Symbol,CAST(PRICE AS varchar(120)) FROM PROD_SOURCEFILES WHERE Date = '2018-01-04 00:00:00'