Regression in handling of String passed as Types.OTHER to JdbcTemplate [SPR-12890]

[spring-projects-issues]

Alex Panchenko opened SPR-12890 and commented

For PostgreSQL if columns are defined as JSON or XML, then String values should be passed via setObject. We are doing that via JdbcTemplate.
Now that code throws an exception after the change 759f430 from #13215 which always passes String via setString().

As I commented on the commit: IMHO JdbcTemplate should not be too smart - if I pass String as OTHER - it should reach the driver as is. OTHER is documented as "indicates that the SQL type is database-specific and gets mapped to a Java object that can be accessed via the methods getObject and setObject" So, probably some other place using OTHER is just not doing that correctly?

---

Affects: 4.1 GA

Issue Links:

• SqlTypes not propagated to database [SPR-12346] #16951 SqlTypes not propagated to database ("is duplicated by")
• Can't insert into nvarchar2 using SimpleJdbcInsert whereas it works with SimpleJdbcTemplate [SPR-8571] #13215 Can't insert into nvarchar2 using SimpleJdbcInsert whereas it works with SimpleJdbcTemplate
• Java boolean is not handled correctly when used with Oracle JDBC driver [SPR-14116] #18688 Java boolean is not handled correctly when used with Oracle JDBC driver
• Support for Oracle JDBC connection properties Ex: fixedString [SPR-15391] #19954 Support for Oracle JDBC connection properties Ex: fixedString
• Oracle 12c JDBC driver throws inconsistent exception from getParameterType (affecting setNull calls) [SPR-13825] #18398 Oracle 12c JDBC driver throws inconsistent exception from getParameterType (affecting setNull calls)

Referenced from: commits 050e581, e95a7a4

[spring-projects-issues]

Alex Panchenko commented

The simplified statement looks like this:

jdbcTemplate.queryForObject("insert into foo (id, json_field) values (nextval(foo_seq),?) returning id", 
new Object[]{jsonValue},
new int[]{Types.OTHER},
Long.class);

[spring-projects-issues]

Juergen Hoeller commented

The problem behind #13215 is that the Oracle driver returns Types.OTHER for an NVARCHAR2 column in its DatabaseMetaData but then doesn't accept a String value through setObject, just through setString... Arguably the Oracle driver isn't fully JDBC-compliant there. Maybe we'll need to a check for Oracle there and only use the specific setters for Types.OTHER in that case.

Juergen

[spring-projects-issues]

Juergen Hoeller commented

A search through Oracle's forums reveals that there are plenty of cases where Oracle exposes Types.OTHER in its metadata but doesn't accept it on setObject / setNull. As a consequence, I've introduced an explicit check that only uses setString etc for Types.OTHER in case of Oracle. I've left the null handling code path as-is since it doesn't seem to hurt to check the metadata parameter type for Types.OTHER on any database.

Juergen