Microsoft SQL JDBC v6.2 driver returns invalid SQL type code for DATETIME fields

Question

Usually ResultSetMetaData#getColumnType()should return 93for fields DATETIME(usually indicated as java.sql.Timestamp).

This is true for driver versions 4.2.6420.100 and 4.0.4621.201 (as well as jTDS ).

When using the newer JDBC drivers for Microsoft ( 6.0.7728.100 , 6.0.8112.100 and 6.2.1.0 in partucular) with Microsoft SQL Server 2005 (9.0.1399), I see that a different type of code is returned: -151 , which does not even match with no type in java.sql.Types.

At the same time ResultSetMetaData#getColumnClassName(int), ResultSetMetaData#getColumnTypeName(int)they behave correctly (always returning java.sql.Timestampand DATETIMEaccordingly).

Here is the unit test, which does not work at startup using the above combinations of drivers and servers:

package com.example;

import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.is;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import javax.sql.DataSource;

import org.eclipse.jdt.annotation.Nullable;
import org.testng.annotations.AfterMethod;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.BeforeMethod;
import org.testng.annotations.Test;

import com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource;

public final class MsSqlServerTest {
    @Nullable
    private static DataSource dataSource;

    @Nullable
    private Connection conn;

    @BeforeClass
    public static void setUpOnce() {
        dataSource = new SQLServerConnectionPoolDataSource();
        ((SQLServerConnectionPoolDataSource) dataSource).setURL("jdbc:sqlserver://localhost\\SQLEXPRESS:1433;databaseName=...");
    }

    @BeforeMethod
    public void setUp() throws SQLException {
        this.conn = dataSource.getConnection("...", "...");
    }

    @AfterMethod
    public void tearDown() throws SQLException {
        if (this.conn != null) {
            this.conn.close();
        }
        this.conn = null;
    }

    @Test
    public void testDateTimeCode() throws SQLException {
        try (final Statement stmt = this.conn.createStatement()) {
            try {
                stmt.executeUpdate("drop table test");
            } catch (@SuppressWarnings("unused") final SQLException ignored) {
                // ignore
            }
            stmt.executeUpdate("create table test (value datetime)");

            try (final ResultSet rset = stmt.executeQuery("select * from test")) {
                final ResultSetMetaData metaData = rset.getMetaData();
                assertThat(metaData.getColumnClassName(1), is(java.sql.Timestamp.class.getName()));
                assertThat(metaData.getColumnTypeName(1), is("datetime"));
                assertThat(metaData.getColumnType(1), is(Types.TIMESTAMP));
            }
        }
    }
}

The above problem does not occur with newer versions of Microsoft SQL Server (e.g. 2014).

SQL Server Management Studio 2014 correctly reports the type of column ( DATETIME), regardless of the version of the server to which it is connected.

What happened to the JDBC driver? Has Microsoft again broken compatibility with one of its products?

Answer 1

"Has Microsoft again broken compatibility with one of its products?"

, , JDBC SQL Server 2005. SQL Server JDBC:

Microsoft JDBC Driver 4.2 4.1 SQL Server SQL Server 2008. Microsoft JDBC Driver 4.0 SQL Server [sic] SQL Server 2005.

GitHub.