Update to spring 3.1.3 causes SqlException when executing sql statement with null parameters, caused by changes in StatementCreatorUtils.setNull method. [SPR-10185]

[spring-projects-issues]

Christiaan Rudolfs opened SPR-10185 and commented

Updating our application to Spring 3.1.3 causes several SQL statements with null parameters to fail with an SqlException, while this worked in versions 3.0.x:

java.sql.SQLException: Unsupported feature.
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.DatabaseError.throwUnsupportedFeatureSqlException(DatabaseError.java:765) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at oracle.jdbc.driver.OracleParameterMetaData.getParameterType(OracleParameterMetaData.java:156) ~[ojdbc6.jar:Oracle JDBC Driver version - "11.1.0.7.0-Production"]
at org.springframework.jdbc.core.StatementCreatorUtils.setNull(StatementCreatorUtils.java:241) [spring-jdbc-3.1.3.RELEASE.jar:3.1.3.RELEASE]
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:214) [spring-jdbc-3.1.3.RELEASE.jar:3.1.3.RELEASE]
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:128) [spring-jdbc-3.1.3.RELEASE.jar:3.1.3.RELEASE]
at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:298) [spring-jdbc-3.1.3.RELEASE.jar:3.1.3.RELEASE]
at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:251) [spring-jdbc-3.1.3.RELEASE.jar:3.1.3.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:581) [spring-jdbc-3.1.3.RELEASE.jar:3.1.3.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:812) [spring-jdbc-3.1.3.RELEASE.jar:3.1.3.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:834) [spring-jdbc-3.1.3.RELEASE.jar:3.1.3.RELEASE]
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:260) [spring-jdbc-3.1.3.RELEASE.jar:3.1.3.RELEASE]
at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:264) [spring-jdbc-3.1.3.RELEASE.jar:3.1.3.RELEASE]

Further investigation shows that this problem is caused by changes in the StatementCreatorUtils.setNull method done in version 3.1.2. I see that these changes have been reverted as of version 3.2.0.RC2.

---

Affects: 3.1.2, 3.1.3

Issue Links:

• NamedParameterJdbcTemplate for "insert / select" clause, set NULL value causes JConnect 7 (Sybase) problem - java.io.IOException: JZ0SL: Unsupported SQL type 0. - Affects 3.1.3.RELEASE, works in 3.1.1.RELEASE [SPR-10084] #14717 NamedParameterJdbcTemplate for "insert / select" clause, set NULL value causes JConnect 7 (Sybase) problem - java.io.IOException: JZ0SL: Unsupported SQL type 0. - Affects 3.1.3.RELEASE, works in 3.1.1.RELEASE ("duplicates")
• JdbcTemplate cannot set null parameter with Oracle driver [SPR-10385] #15018 JdbcTemplate cannot set null parameter with Oracle driver

Referenced from: commits c38bde2, b62652a, 41f041e

Backported to: 3.1.4

[spring-projects-issues]

Juergen Hoeller commented

This has been fixed for the upcoming 3.1.4 release, with 3.2.1 containing the same code now (i.e. a getParameterType call but being defensive about unsupported feature exceptions).

Juergen

[spring-projects-issues]

Eirik Lygre commented

For those who arrive here in search of a solution: This was not fixed in the 3.1.4-release, and is at the time of this writing not fixed in the 3.1.x branch.

[spring-projects-issues]

Juergen Hoeller commented

Note that there was a follow-up change through #15018, which was only released in the 3.2.x branch (in 3.2.3). The originally synced code from 3.2.1 did end up in 3.1.4, as far as I checked, but wasn't taken forward from there since 3.1.4 was the end of its line.

At this point, we're about to release 4.0 soon and will only maintain 3.2.x for a further while. So please upgrade to 3.2.3 or higher; there won't be a further 3.1.x release. 3.2 was designed as a straightforward follow-up, so this should hopefully work smoothly.

Sorry for the inconvenience,

Juergen