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]

[spring-projects-issues]

David Boden opened SPR-10084 and commented

On a SQL query that involves:

insert into AbcTable
select :param1, :param2, otherValue
from DefTable

... using a NamedParameterJdbcTemplate and mapping one of the named parameters to NULL in the parameter map results in:
java.io.IOException: JZ0SL: Unsupported SQL type 0.

Example code (Spring 3.1.3.RELEASE):
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("param1", null);
template.update(insertSql, parameters);

Works (inserts a null value into the table) in 3.1.1.RELEASE.

Using Sybase JConnect 7.07.ESD5 (latest) and other versions of JConnect spanning back 2 years.

Test details to follow on Monday, just wanted to raise the issue.

---

Affects: 3.1.3

Issue Links:

• Update to spring 3.1.3 causes SqlException when executing sql statement with null parameters, caused by changes in StatementCreatorUtils.setNull method. [SPR-10185] #14818 Update to spring 3.1.3 causes SqlException when executing sql statement with null parameters, caused by changes in StatementCreatorUtils.setNull method. ("is duplicated by")
• performancedrop due to repeated JDBC 3.0 getParameterType calls in setNull in StatementCreatorUtils [SPR-11100] #15726 performancedrop due to repeated JDBC 3.0 getParameterType calls in setNull in StatementCreatorUtils
• Protect against getParameterType cursor leak with Oracle 12c JDBC driver [SPR-14629] #19196 Protect against getParameterType cursor leak with Oracle 12c JDBC driver

Referenced from: commits f449b5a, f0a1ff2

Backported to: 3.1.4

[spring-projects-issues]

David Boden commented

Looks like the problem is caused by the range of Sybase JConnect 7 drivers that I've tried not behaving correctly when PreparedStatement.getParameterMetaData() is called.

StatementCreatorUtils line 234 is:
pmd = ps.getParameterMetaData();

JConnect 7 throws:
com.sybase.jdbc4.jdbc.SybSQLException: Incorrect syntax near the keyword 'select'.

This may be due to a misconfiguration on the server side but:

• We've used the most recent JConnect server-side scripts and they executed successfully.
• All other database operations work fine.

The line is already inside a try/catch. I'd appreciate it if a defensive "catch(SQLException ex)" could be added alongside the existing "catch (AbstractMethodError err)". That would return things to the 3.1.1.RELEASE behaviour for me.

[spring-projects-issues]

David Boden commented

I've confirmed that this is a bug with Sybase JConnect and have filed a bug report with Sybase. I'll ask them to make the bug report public and will specify the bug tracker id on here for reference.

But... I could still do with the defensive "catch(SQLException ex)" to restore the previous behaviour in spite of this Sybase bug.

[spring-projects-issues]

David Boden commented

Sybase Case# 11766880

[spring-projects-issues]

Chris Beams commented

Thanks, David for following up here. Please consider submitting a pull request for this per the instructions at https://github.com/SpringSource/spring-framework/blob/master/CONTRIBUTING.md. Otherwise we'll get to it as we can, thanks.

[spring-projects-issues]

David Boden commented

Apologies, thought I already commented on this one... I've confirmed that this is fixed in 3.2.0.RELEASE ! Would appreciate it if you could close this Jira for me.

[spring-projects-issues]

Chris Beams commented

Hi David,

Resolving as requested, but note that we haven't made any changes to the framework regarding this problem. Perhaps it's environmental, or a function of an upgraded Sybase driver that it's now working. In any case, thanks for the update.

[spring-projects-issues]

David Boden commented

Thanks Chris. I see differences between the 3.1.3 and 3.2.0 StatementUtils.java file in Maven Central:

Specifically, these lines are missing from 3.2.0. Perhaps this is a regression (changes were meant to be applied to 3.2.0 but weren't)?

ParameterMetaData pmd = null;
try {
         pmd = ps.getParameterMetaData();
}
catch (AbstractMethodError err) {
         // JDBC driver not compliant with JDBC 3.0
         // -> proceed with database-specific checks
}
if (pmd != null) {
         sqlType = pmd.getParameterType(paramIndex);
}
else {

[spring-projects-issues]

Chris Beams commented

Thanks, David. Will look back into this.

[spring-projects-issues]

Juergen Hoeller commented

Indeed, I've accidentally applied this to the 3.1.x branch only. To be fixed for 3.2.1.

Juergen

[spring-projects-issues]

Chris Beams commented

Looks like this showed up in 3.1.x with commit 79d9f7a5f731268a4a999ce45e0579f3c512a2f6.

[spring-projects-issues]

David Boden commented

Thanks. This is probably obvious to you, but just wanted to make it clear that applying the patch as-is to the 3.2.x codebase will introduce the problem I've reported to the 3.2.x branch too. Hopefully this Jira represents fixing the current on the 3.1 stream and applying both the original commit and the fix to the 3.2 stream?

[spring-projects-issues]

Juergen Hoeller commented

Applied to both branches with minor modifications for defensiveness against the JDBC driver now, as you suggested above.

Juergen