Spring NamedParameterJdbcTemplate issue with division and parameter in MSSqlServer

Question

I'm executing the following query using NamedParameterJdbcTemplate with a single parameter.

DDL For Table

create table TEST_TRANS
(
    DESCRIPTION_2 float,
    AMOUNT_STR varchar(255),
    DESCRIPTION varchar(255)
)

SQL

UPDATE TEST_TRANS
    SET DESCRIPTION_2 = CAST(AMOUNT_STR as float) / 100
WHERE 
    DESCRIPTION != :DESCRIPTION

When I execute the query using

Objects.requireNonNull(getNamedParameterJdbcTemplate())
                .update(testQuery, Collections.singletonMap("DESCRIPTION","AED"));

I'm getting the following error.

Exception in thread "main" org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [UPDATE TEST_TRANS SET DESCRIPTION_2 = CAST(AMOUNT_STR as float)/100 WHERE DESCRIPTION != ?]; The index 1 is out of range.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:110)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1442)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:632)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:861)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:882)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:320)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:325)
    at com.seenukarthi.so.jdbctest.repository.TestJdbcRepository.test(TestJdbcRepository.java:32)
    at com.seenukarthi.so.jdbctest.service.TestJdbcService.testService(TestJdbcService.java:19)
    at com.seenukarthi.so.jdbctest.Main.main(Main.java:12)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The index 1 is out of range.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:228)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setterGetParam(SQLServerPreparedStatement.java:1124)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setValue(SQLServerPreparedStatement.java:1138)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setString(SQLServerPreparedStatement.java:1683)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:400)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:232)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:147)
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:286)
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:244)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:614)
    ... 7 more

If I remove the /100 for the SQL everything is working. Also if I hardcode the parameter it's working

Environment:

1. MSSql Server 2017 in Ubuntu.
2. Application Server Jetty in macOs
3. Java openjdk version "1.8.0_222"

Reproducer can be found in GitHub

Malaspina

Can you show the code executing the update? – Hejira

@Karthikeyan Vaithilingam, could you provide your java code and your environment like version of OS, java, mssql etc? – Copacetic

@Copacetic I cannot share the actual code i'll create a reproducer and update the question. – Malaspina

@Copacetic I've added the reproducers. – Malaspina

@KarthikeyanVaithilingam stupid workaround - what happens if you replace /100 with *0.01? – Draughts

@Draughts your workaround works. but why / is not working? – Malaspina

Answer 1

After some research I found that the issue was with the JDBC driver mssql-jdbc version 7.2.0. After upgrading the driver to the latest version the issue is resolved

BUG Report for GitHub

Malaspina