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?