Error with SQL Type with a null parameter in MS SQL Server spring-jdbc > 6.1.2

[PepperSniffer]

Affects: \ spring-jdbc 6.1.2 and up

---

Hi,
I have an issue with a spring boot application that uses spring-jdbc 6.1.11,
i have an error after version upgrade that tells me (in french)

La conversion implicite du type de données nvarchar en varbinary n'est pas autorisée. Utilisez la fonction CONVERT pour exécuter cette requête

the english equivalent is something like :

implicit conversion from data type nvarchar to varbinary is not allowed, use the CONVERT function

when doing a simple sql update request with a null parameter such as

UPDATE myTable SET name = :name WHERE id = :id

where the parameter value for name is null.

in my code the column that we try to set to null is a varbinary, but i've also tried to set a varchar to null, the same issue happens.

tested with multiple mssql-jdbc driver version the errors still occurs (even with the version provided by the spring boot parent pom)

tested by only changing spring-jdbc version worked in
6.1.0, 6.1.1, 6.0.23
but didn't work from 6.1.2 to 6.1.12

it seems from decompiling that the issue happens when the boolean useSetObject is set to true at line 272 and 297 in the file spring-jdbc/src/main/java/org/springframework/jdbc/core/StatementCreatorUtils.java

i've tried debugging, when forcing this value to false, the sql requests works,
when setting the property shouldIgnoreGetParameterType to false (by the spring property IGNORE_GETPARAMETERTYPE_PROPERTY_NAME) it also works

the SQL Server version used is : Microsoft SQL Server 2019 (KB5039747) - 15.0.4385.2 (X64)

my pom :

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>3.3.2</version>
    <relativePath />
</parent>
....
<dependencies>
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
//effective version is 6.1.11 provided by the parent
  </dependency>
  <dependency>
      <groupId>com.microsoft.sqlserver</groupId>
      <artifactId>mssql-jdbc</artifactId>
      <scope>runtime</scope>
//effective version is 12.6.3.jre11 provided by the parent
  </dependency>
</dependencies>

[sdeleuze]

@jhoeller Could be a side-effect of #25679 via 77b0382 ?

[jhoeller]

Looks like a side effect of #25679 indeed. For the time being, please keep setting spring.jdbc.getParameterType.ignore=false, even if this incurs extra overhead that we were trying to avoid with that change.

For the record, we also had another regression on MS SQL Server caused by this optimization: spring-projects/spring-data-relational#1827 - so we might have to revisit our default there.

[PepperSniffer]

i've downgraded the spring-jdbc dependency to 6.1.1 as there is only patch version differences, it works as expected with my spring boot versions.
Do you known or have an idea on when this would be fixed ?

[jhoeller]

I recommend the spring.jdbc.getParameterType.ignore=false system property rather than a downgrade of spring-jdbc. We may revert the default of that property for 6.1.13 in September if we don't find a specific solution for the MS SQL Server varchar conversion problem. So to be clear, the only thing that changed in 6.1.2 is that property's default on Postgres and SQL Server.

Note that there may be a performance impact on MS SQL Server which is why we changed the default of that property in 6.1.2 to begin with: #25679 (comment) - If in doubt, we'll nevertheless have to accept that performance overhead in order to provide correct behavior for scenarios like yours.

[jhoeller]

According to microsoft/mssql-jdbc#1269 (comment), this issue just appears for varbinary, binary and image columns on SQL Server. For all other column types, setObject(x, null) works fine. Unfortunately, the exception only happens on executeUpdate, not on the setObject call itself, so we cannot just catch the exception and retry with a setNull call. We could just check the expensive getParameterType upfront which we were trying to avoid to begin with.

So we currently avoid the performance overhead of getParameterType for all regular column types as of #25679. On review, this is hard to give up on if it has strong performance benefits for many scenarios. Whereas the present varbinary issue at least leads to an immediate exception and fortunately not to unnoticed misbehavior.

The safest solution is to pass your null value as new SqlParameterValue(Types.VARBINARY, null) so that the SQL type associated with the null value is explicit. No need for spring.jdbc.getParameterType.ignore=false then. Given those options and the overall impact, this is an unfortunate but known and documented side effect of #25679.