Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Problem with SimpleJdbcTemplate#update with null values in DB2 [SPR-4465] #9142

Closed
spring-projects-issues opened this issue Feb 14, 2008 · 10 comments

Comments

@spring-projects-issues
Copy link
Collaborator

@spring-projects-issues spring-projects-issues commented Feb 14, 2008

Andrew Wright opened SPR-4465 and commented

When trying to make updates with SimpleJdbcTemplate:

getSimpleJdbcTemplate().update("INSERT INTO jiveGroup (name, description, groupID, creationDate, modificationDate) VALUES (?, ?, ?, ?, ?)",
group.getName(),
group.getDescription(),
id,
group.getCreationDate().getTime(),
group.getModificationDate().getTime());

when the value of group.getDescription() is null I receive the following exception:

Caused by: com.ibm.db2.jcc.b.SqlException: [jcc][10271][10295][3.50.152] Unrecognized JDBC type: 0. ERRORCODE=-4228, SQLSTATE=null
at com.ibm.db2.jcc.b.wc.a(wc.java:55)
at com.ibm.db2.jcc.b.wc.a(wc.java:102)
at com.ibm.db2.jcc.b.cb.i(cb.java:1219)
at com.ibm.db2.jcc.b.uk.b(uk.java:612)
at com.ibm.db2.jcc.b.uk.setNull(uk.java:584)
at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValueInternal(StatementCreatorUtils. java:173)
at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValue(StatementCreatorUtils.java:88)
at org.springframework.jdbc.core.ArgPreparedStatement Setter.setValues(ArgPreparedStatementSetter.java:5 1)
at org.springframework.jdbc.core.JdbcTemplate$2.doInP reparedStatement(JdbcTemplate.java:771)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:566)

The issue was due to the following line inside oforg.springframework.jdbc.core.StatementCreatorUtils:180

setNull(paramIndex, sqlTypeToUse);

where sqlTypeToUse is equal to java.sql.Types.NULL. DB2 does not like receiving Types.NULL on update. I discovered that if you use Types.VARCHAR it works, even if the type is not a varchar type (tested with BIGINT).

As a work around in my app I subclasses SimpleJdbcTemplate and added the following check in my update method:
// test for db2 (jive specific)
if (ConnectionManager.getDatabaseType() == ConnectionManager.DatabaseType.DB2) {
List<Object> list = asList(args);
// check for null values and insert a SqlParameter value in place (using google collections)
List<Object> transformed = transform(list, new Function<Object,Object>() {
public Object apply(@Nullable Object o) {
if (o == null) {
return new SqlParameterValue(Types.VARCHAR, null);
}
return o;
}
});

It would be great StatementCreatorUtils could handle DB2 in similiar way with nulls, so I can get rid of the above hack.


Issue Links:

  • #9485 Problem with SimpleJdbcTemplate#update with null values in Derby
@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Feb 14, 2008

Juergen Hoeller commented

Thanks for pointing this out! As of Spring 2.5.2, JdbcTemplate falls back to type VARCHAR for a null value in case of an unknown SQL type specified against IBM DB2.

Juergen

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Feb 14, 2008

Andrew Wright commented

sweet! Thanks for the quick response.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Feb 15, 2008

Jessica-Aileen Alten commented

The same problem exists with the Microsoft jdbc driver 1.1 and the current version 1.2 too - it doesn't like the insertion of null values and prohibits therefore the usage of many wonderful features from SimpleJdbcTemplate. Microsoft won't fix this behaviour!

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Feb 15, 2008

Andrew Wright commented

Jessica,

Have you tried using the jtds sqlserver driver (http://jtds.sourceforge.net/). Microsoft's driver is horrible.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Feb 16, 2008

Jessica-Aileen Alten commented

Hi Andrew,

yes, I know this driver - and had other problems with it in a stand alone application. Therefore I dont't want to change back to it. Shure, it would be an alternative, but I think Spring should support the vendor jdbc driver of the M$-SQL-Server as much as it supports for example the IBM driver.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Feb 16, 2008

Juergen Hoeller commented

Jessica,

What kind of treatment does the Microsoft driver require for null values? I suppose that the standard setNull(index, Types.NULL) call doesn't work, so does it maybe work with a setObject(index, null) call? Or with a setNull(index, Types.VARCHAR) call like for DB2, even for non-string values?

Juergen

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Feb 16, 2008

Juergen Hoeller commented

BTW, Andrew: Do you have any experience with using null values on previous versions of DB2? Did it always accept Types.VARCHAR for non-string types, and does it do so on all platforms?

Juergen

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Feb 16, 2008

Andrew Wright commented

Juergen,

I couldn't tell you, I don't use DB2 a whole lot. It's just one of the databases we support (Jive Clearspace) and I was debugging an issue that came up when we started using Spring JDBC.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Sep 29, 2008

Randy Burgess commented

What is the possibility of backporting this to 2.0.x? WebSphere 6.x still uses JDK 1.4.2 and you can't use Spring 2.5.x with that version.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Sep 29, 2008

Juergen Hoeller commented

WebSphere 6.0 does indeed use JDK 1.4.2; that's why Spring 2.5 is still compatible with it! All of the Java 5 support there is optional; Spring 2.5 will automatically adapt to JDK 1.4.2 when running on it.

Juergen

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants