db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Christian Schwanke (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-2550) Types.NULL is not accepted when using setNull on a PreparedStatment
Date Mon, 16 Apr 2007 07:33:15 GMT
Types.NULL is not accepted when using setNull on a PreparedStatment

                 Key: DERBY-2550
                 URL: https://issues.apache.org/jira/browse/DERBY-2550
             Project: Derby
          Issue Type: Bug
          Components: JDBC
    Affects Versions:
         Environment: Ubuntu 6.10, Java 5 and Mac OSX 10.4, Java5
Spring 2.x
Derby "Embedded" mode
            Reporter: Christian Schwanke

Inserting data into table using a PreparedStatement will fail, if the setNull() method is
used with Types.NULL.
I have tracked down the problem to the method "isJDBCTypeEquivalent(int existingType, int
jdbcTypeId)" in class "org.apache.derby.iapi.types.DataTypeDescriptor" (Line 922).

This method checks the current column type against the type specified by the application.
The setNull() method will throw an error, if the types do not match. The problem here is,
that isJDBCTypeEquivalent will not accept Types.NULL as an valid equivalent to the column
When writing the JDBC code by hand one can avoid the problem - but this is quite annoying
since the Jdbc-Support provided by the Spring-Framework will use setNull() with Types.NULL
making it impossible to use Derby with Spring's plain JdbcTemplate.

String preparedSql = "INSERT INTO demo (stringValue) VALUES (?)";
PreparedStatement pstmt = con.prepareStatement(preparedSql);

// this will work, since the given type is equivalent
pstmt.setString(1, null);

// this will fail, since Types.NULL is not recognized as equivalent
pstmt.setNull(1, Types.NULL);

The exception thrown is 
"java.sql.SQLException: An attempt was made to get a data value of type 'VARCHAR' from a data
value of type '0'"

As far as I can see, it is sufficient to modify the first part of the isJDBCTypeEquivalent-method.
At least, it solved my problems.

		// Any type matches itself.
		if (existingType == jdbcTypeId)
			return true;

		// Any type matches itself.
		if (existingType == jdbcTypeId || jdbcTypeId == Types.NULL)
			return true;

I've attached a simple TestCase to reproduce the problem. 

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message