CODEWITHSUNDEEP
javasql-serverjdbcsql-server-2005mssql-jdbc
Bass
Bass

Reputation: 5348

Microsoft SQL JDBC driver v6.2 returning incorrect SQL type code for DATETIME fields

Normally, ResultSetMetaData#getColumnType() should return 93 for DATETIME fields (usually represented as java.sql.Timestamp).

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

When using newer Microsoft JDBC drivers (6.0.7728.100, 6.0.8112.100 and 6.2.1.0 in partucular) with Microsoft SQL Server 2005 (9.0.1399), I observe a different type code is returned: -151, which doesn't even map to any type in java.sql.Types.

At the same time, ResultSetMetaData#getColumnClassName(int) and ResultSetMetaData#getColumnTypeName(int) behave correctly (always returning java.sql.Timestamp and datetime, respectively).

Here's the unit test which fails when run using the above driver and server combinations:

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 issue doesn't occur with newer Microsoft SQL Server versions (like 2014).

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

What's wrong with the JDBC driver? Has Microsoft once again broken compatibility with one of its own products?

Upvotes: 4

Views: 612

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

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

Technically, no, because the current versions of the JDBC driver do not support SQL Server 2005. According to the SQL Server requirements for the JDBC driver:

For Microsoft JDBC Driver 4.2 and 4.1 for SQL Server, support begins with SQL Server 2008. For Microsoft JDBC Driver 4.0 for SQL Server, support beings [sic] with SQL Server 2005.

This was also discussed on GitHub.

Upvotes: 2

Related Questions