Spring Data JDBC - Cannot persist null byte array #1827
Description
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