Spring Data JDBC - Cannot persist null byte array

[Bram80]

We're receiving following stacktrace when persisting a byte array as null using Spring Data:

Caused by: org.springframework.jdbc.UncategorizedSQLException: 
PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO "SOME_TABLE" 
("CONTENT") VALUES (?)]; SQL state [S0003]; error code [257]; Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1549)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1001)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:365)
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:349)
at org.springframework.data.jdbc.core.convert.IdGeneratingInsertStrategy.execute(IdGeneratingInsertStrategy.java:68)
at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.insert(DefaultDataAccessStrategy.java:110)
at org.springframework.data.jdbc.core.JdbcAggregateChangeExecutionContext.executeInsertRoot(JdbcAggregateChangeExecutionContext.java:83)
at org.springframework.data.jdbc.core.AggregateChangeExecutor.execute(AggregateChangeExecutor.java:85)
... 34 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:261)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1752)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:686)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:605)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7748)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4410)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:293)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:263)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:548)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.springframework.jdbc.core.JdbcTemplate.lambda$update$3(JdbcTemplate.java:1002)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)

We're receiving this using spring-boot-starter-parent version 3.3.1,
with version 3.2.0 everythings works.

Example implementation:

@Table("SOME_TABLE")
public record SomeTable(
    @Id @Column("ID")
    Long id,
    @Column("CONTENT")
    byte[] content
) {
}

public interface SomeTableRepository extends CrudRepository<SomeTable, Long> {
}

@DataJdbcTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@EnableJdbcRepositories(considerNestedRepositories = true)
@ContextConfiguration(classes = {
    SomeTableRepository.class
})
@Sql(executionPhase = BEFORE_TEST_METHOD, value = {
    "classpath:0_init.sql",
    "classpath:1_load.sql"
})
@Sql(executionPhase = AFTER_TEST_METHOD, value = "classpath:2_clean.sql")
class ByteTest extends MssqlContainerBaseTest {

  @Autowired
  private SomeTableRepository someTableRepository;

  @Test
  void testByteArrayNotNull() {
      final SomeTable record = new SomeTable(null, "abc".getBytes());
      someTableRepository.save(record);
  }

  @Test
  void testByteArrayNull() {
      final SomeTable record = new SomeTable(null, null);
      someTableRepository.save(record);
  }

  @Test
  void testByteArrayEmpty() {
      final SomeTable record = new SomeTable(null, new byte[]{});
      someTableRepository.save(record);
  }

}

testByteArrayNull test method doesn't work.

testByteArrayNotNull and testByteArrayEmpty test methods work fine.

So we've a workaround by an using empty Byte array instead of null.
Although, null should be possible as it was by earlier versions.

I included a reproducable example.
test-container-test.zip

[schauder]

All the tests in the reproducer are green, but there is no testByteArrayEmpty.

Please provide an actual reproducer of the issue.

[Bram80]

Here you go :-)

test-container-test.zip

[schauder]

Thanks for the reproducer.

This does not seem to originate in Spring Data JDBC, since it can be reproduced with just a NamedParameterJdbcTemplate.

My first guess was a regression in the JDBC driver, but switching that arround doesn't seem to make a difference.

See the additional test and branches in https://github.com/schauder/issue-jdbc-1827-insert-null-array

I'll see what the Spring Framework team has to say.

[jhoeller]

@Bram80 assuming that spring-projects/spring-framework#25679 might be the cause here: Does it help to set the following system property (can also be an entry in a spring.properties file in the root of the classpath) - spring.jdbc.getParameterType.ignore=false

[Bram80]

@jhoeller Setting the system property you mentioned solves the issue.

[schauder]

@jhoeller I'm going to close this issue, since the problem of the OP is resolved. Thanks for the help there.