SQLServerException: Unexpected TDS type DATETIMEOFFSETN in SQL_VARIANT

[max-datagamma]

Driver version

mssql-jdbc-9.4.0.jre11

SQL Server version

Microsoft SQL Server 2019 (RTM-CU11) (KB5003249) - 15.0.4138.2 (X64)
May 27 2021 17:34:14
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 20.04.2 LTS)

Client Operating System

MacOS Big Sur 11.6

JAVA/JVM version

OpenJDK 64-Bit Server VM 14.0.1

Table schema

create table data_types.test1
(
id int not null,
c_datetimeoffset sql_variant
);

insert into data_types.test1 (id, c_datetimeoffset)
values
(1, cast('0001-01-01 00:00:00.0000000 +00:00' as datetimeoffset))

Problem description

1. Expected behaviour: retrieving the value of the c_datetimeoffset column. According to the SQL Server docs on sql_variant (https://docs.microsoft.com/en-us/sql/t-sql/data-types/sql-variant-transact-sql?view=sql-server-ver15): "SQL Server 2012 and greater do not restrict datetimeoffset"

2. Actual behaviour: exception

3. Error message/stack trace:
   2021-10-14 15:05:49 ERROR SQL Error during retrieve Unexpected TDS type DATETIMEOFFSETN in SQL_VARIANT.
   com.microsoft.sqlserver.jdbc.SQLServerException: Unexpected TDS type DATETIMEOFFSETN in SQL_VARIANT.
   at com.microsoft.sqlserver.jdbc.ServerDTVImpl.readSqlVariant(dtv.java:4117)
   at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:3886)
   at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:247)
   at com.microsoft.sqlserver.jdbc.Column.getValue(Column.java:190)
   at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2077)
   at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getValue(SQLServerResultSet.java:2063)
   at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getObject(SQLServerResultSet.java:2395)

Reproduction code

    String connectionUrl = "jdbc:sqlserver://192.168.1.2;databaseName=master;user=test1;password=xxxx";
    Connection con = DriverManager.getConnection(connectionUrl);
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * from data_types.test1");
    while (rs.next()) {
        System.out.println(rs.getInt("id") + " " + rs.getObject("c_datetimeoffset")); // Exception in getObject
    }

[lilgreenbird]

hi @max-datagamma

Thank you, we are investigating will get back to you.

[lilgreenbird]

hi @max-datagamma.

The doc that you referred to is for data types supported by the SQL server. It looks like this is not supported in the JDBC driver you can see more details in issue #449 the error message was added to address that issue. This should be documented I do not see this in the any of our driver documentation I have added a task to get this updated. I have also added this issue to our backlog as an enhancement request we will look into the amount of work required and prioritize adding support for this in the future.

Thank you.

[max-datagamma]

Thank you @lilgreenbird. From a user's perspective, it does look a bit weird that this one data type is not supported in a sql_variant by this driver, but I'm a developer too and I understand this may not be high priority.