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

DB error when setting SqlTypeValue.TYPE_UNKNOWN to null [SPR-1234] #5936

Closed
spring-projects-issues opened this issue Aug 17, 2005 · 16 comments
Closed

Comments

@spring-projects-issues
Copy link
Collaborator

@spring-projects-issues spring-projects-issues commented Aug 17, 2005

Gethin James opened SPR-1234 and commented

The StatementCreatorUtils.setParameterValue automatically sets parameters even if we don't specify the type. However when this parameter is NULL, DB2 returns a "CLI0613E Program type out of range. SQLSTATE=S1003" error.

In the code you have a commented out line of ps.setNull(paramIndex, Types.NULL);

Can you please use this line instead of ps.setObject(paramIndex, null);


Affects: 1.2.2

Attachments:

Issue Links:

  • #9485 Problem with SimpleJdbcTemplate#update with null values in Derby

1 votes, 2 watchers

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Aug 17, 2005

Juergen Hoeller commented

Thomas, what do you think? It sounds reasonable to me to use the setNull call instead.

Juergen

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Aug 18, 2005

Thomas Risberg commented

Sounds reasonable - but this is driver specific behavior and we should test the most commonly used ones before we commit to a change. Oracle 10g driver works fine either way. I'd like to try MySQL and Postgres before we make the change.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Aug 18, 2005

Juergen Hoeller commented

I agree - we should test the common drivers before changing this. It would be great if you could give MySQL and PostgreSQL a try till Saturday, which would allow us to get this change into 1.2.4. Else, it would take a while until it could appear in a Spring production release, as 1.3 final is still a while away.

JUergen

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Aug 18, 2005

Thomas Risberg commented

Tests OK with recent versions of common drivers except Oracle 9i (9.2.0.5) but the old way did not work with this driver either so we will not lose any functionality.

Tested OK:
mysql-connector-java-3.1.10
Oracle 10g 10.1.0.3.0
HSQLDB 1.8.0
PostgreSQL 8.0 JDBC3 with SSL (build 312)

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Sep 25, 2005

Gethin James commented

Sorry Gents, that didn't fix it for DB2. It still seems to call the same method in the driver. The work around is to specify the TYPES, then it seems to work. Here's the stack trace for your information.

BEFORE THE FIX:
COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0613E Program type out of range. SQLSTATE=S1003
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.pstmtException(SQLExceptionGenerator.java:653)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.setNull(DB2PreparedStatement.java:1821)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.setObject(DB2PreparedStatement.java:3226)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.setObject(DB2PreparedStatement.java:3167)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:165)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:92)

AFTER THE FIX:
COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0613E Program type out of range. SQLSTATE=S1003
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.pstmtException(SQLExceptionGenerator.java:653)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.setNull(DB2PreparedStatement.java:1821)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:104)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:95)

WORKAROUND:
Specifiy the types,eg. new int[] {
Types.INTEGER,
Types.VARCHAR,
Types.TIMESTAMP,
Types.VARCHAR,
Types.VARCHAR }

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 14, 2007

Dave Syer commented

I'm going to take the liberty of re-opening this issue even though it is really old - it didn't seem like a big deal so maybe it can be fixed finally in a new release. I know that it is not actually resolved for DB2 because we came across it on a project. Apache Derby also has the same issue (and the same workaround).

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 15, 2007

Thomas Risberg commented

I'm not able to reproduce this - can you give me some more details regarding the statement and JdbcTemplate methods that were used?

I ran the following:

    String insertData = "INSERT INTO test_table (id, name, a_date, a_timestamp, a_number, a_decimal)" +
            " values(?, ?, ?, ?, ?, ?)";

    int r = jdbcTemplate.update(insertData, new Object[] {2L, null, null, null, null, null});

on DB2 and Derby without issues.

This is the Derby info:

Database Name: Apache Derby
Database Version: 10.2.2.0 - (485682)
Driver Name: Apache Derby Network Client JDBC Driver
Driver Version: 10.2.2.0 - (485682)

and this is my DB2 info:

Database Name: DB2/LINUX
Database Version: SQL09012
Driver Name: IBM DB2 JDBC Universal Driver Architecture
Driver Version: 3.1.57

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 15, 2007

Dave Syer commented

Did you try SQL TIMESTAMP (as opposed to DATE)?

Attached Eclipse/Maven project with Derby example that fails.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 15, 2007

Thomas Risberg commented

The difference is the JDBC driver. I was using the client driver while your test uses the embedded driver. We already have an exception for Informix where we use setObject instead of setNull - I'll try adding a similar exception for the embedded driver to see what happens.

This doesn't solve the DB2 problem though. My test runs fine n DB2, but I'm using a fairly recent driver. Maybe the DB2 issue is in an older driver. If someone can give me a version that fails I can do some further investigation.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 15, 2007

Thomas Risberg commented

The Derby embedded driver doesn't seem to support either setNull(ix, Types.NULL) or setObject(ix, null). Seems like the only option is to provide the type information either with the type array or by embedding the null value in a SqlParameterValue like "new SqlParameterValue(Types.TIMESTAMP, null)". And it's not just timestamps, I see an error passing in null when using plain VARCHAR columns as well.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 15, 2007

Thomas Risberg commented

Resolution:

For Derby:

Use the "client" driver ("org.apache.derby.jdbc.ClientDriver") from "derbyclient.jar". This involves starting up a separate database process that accepts network connections - "java -jar derbyrun.jar server start".

When using the "embedded" driver ("org.apache.derby.jdbc.EmbeddedDriver") you must provide type information for any parameters with a 'null' value.

See https://issues.apache.org/jira/browse/DERBY-2550 for more info.

For DB2:

Use a recent version of the DB2 UDB JDBC Universal Driver if possible. See http://www.ibm.com/developerworks/db2/library/techarticle/dm-0512kokkat/ for more info on this driver.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 16, 2007

Dave Syer commented

I love it when two vendors both blame each other for an issue.

Can we at least translate the exception into something more meningful for developers?

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 16, 2007

Thomas Risberg commented

We aren't blaming anyone, it's just that (as far as I can tell), using the Derby embedded driver, there is no way of setting a 'null' on a prepared statement parameter without knowing the actual SQL Type. If there were a way, we could implement some special handling.

We translate the exception to a DataIntegrityViolationException. As for the error message, we never translate the error messages. We just wrap the SQLException re-using the original message.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 16, 2007

Dave Syer commented

OK. Maybe I would argue that it is an InvalidDatAccessApiUsageException, or even something else more specific? It confuses everyone that comes across it because it seems from the exception that you must have violated a server side integrity constraint, but actually its just the driver telling you it doesn't have enough information.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented Nov 16, 2007

Thomas Risberg commented

We don't translate SQLExceptions to InvalidDatAccessApiUsageException. This exception is just used internally by the framework code. The actual error code could also be thrown for other issues so I would prefer to stick to the current translation.

I could however switch to using setObject(ix, null) which results in a different error mesage that seems a bit more meaningful -- An attempt was made to get a data value of type 'TIMESTAMP' from a data value of type 'null'.

I think that's the best we can do for now.

@spring-projects-issues
Copy link
Collaborator Author

@spring-projects-issues spring-projects-issues commented May 21, 2008

Thomas Risberg commented

This seems to be solved by using setNull with a type of Types.VARCHAR. Works regardless of the actual column type. We already do this for DB2 and Sybase. I will include Apache Derby Embedded for this strategy as well.

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
1 participant