Support for Microsoft SQL Server driver version 4 and above [SPR-13772]

[spring-projects-issues]

Gareth Moorst opened SPR-13772 and commented

Using JdbcTemplate, trying to insert NULL into a DATE column using the Microsoft SQL Server driver no longer works in versions 4 and above of the JDBC driver.
It still works in driver version 3.
To be clear, the driver behaviour hasn't changed from version 3 to 4. The driver requires slightly different calling code to some other JDBC drivers - instead of using
PreparedStatement.setNull(int index, int type),
it needs
PreparedStatement.setObject(int index, Object value), with value = null.
There is a workaround in org.springframework.jdbc.core.StatementCreatorUtils.java:285 that checks if the driver name starts with "Microsoft SQL Server" and uses setObject instead of setNull.
Unfortunately, the driver name has changed in versions 4 and above.
Instead of being "Microsoft SQL Server JDBC Driver 3.0" it is now "Microsoft JDBC Driver 4.0 for SQL Server".

Here is test code that reproduces the problem (it works with driver version 3, but not 4):

CREATE TABLE dbo.NULL_DATE_TEST (
    DATE_FIELD DATE
)

import com.microsoft.sqlserver.jdbc.SQLServerXADataSource;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;

public class InsertNullTestCase {

    private static final String DB_URL="jdbc:sqlserver://HOSTNAME:PORTdatabaseName=DATABASENAME";
    private static final String PASSWORD="BLAH";
    private static final String USER="BLOO";

    @Test
    public void insertNullDate_shouldNotThrowException() throws Exception {

        SQLServerXADataSource ds = new SQLServerXADataSource();
        ds.setURL(DB_URL);
        ds.setPassword(PASSWORD);
        ds.setUser(USER);

        JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
        jdbcTemplate.update("INSERT INTO NULL_DATE_TEST VALUES (?)", new Object[]{null});
    }
}

---

Affects: 3.2.15, 4.1.8, 4.2.3

Backported to: 4.1.9, 3.2.16

[spring-projects-issues]

Juergen Hoeller commented

Thanks for raising this! I've refined the check to startsWith("Microsoft") and contains("SQL Server") there now...

This will be available in the upcoming 4.2.4.BUILD-SNAPSHOT snapshot in about an hour. I'll backport it to 4.1.9 and 3.2.16 later tonight. Please give it a try and let me know whether it works for you...

Juergen

[spring-projects-issues]

Gareth Moorst commented

Thanks for the quick fix - it does indeed solve the problem.

[spring-projects-issues]

Juergen Hoeller commented

Good to hear - thanks for raising this!

Juergen