From: Markus Fuchs <Markus.Fuchs_at_Sun.COM>
Date: Tue, 27 Jun 2006 16:59:22 -0700

Hi Tom,

One of our tests ran into an issue while binding a null value to an IMAGE column on MS SQLServer:

Based on the table definition:

CREATE TABLE DATATYPES2 (
 DATATYPES2_ID DATETIME NOT NULL,
 TIMEDATA DATETIME,
 TSDATA DATETIME,
 BYTEDATA IMAGE,
 CHARDATA VARCHAR(448),
 CONSTRAINT PK_DATATYPES2 PRIMARY KEY (DATATYPES2_ID)
) 

The following exception occurs when trying to insert a null value into DATATYPES2.BYTEDATA.

Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2006.4 (Build
060412)):
oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: [sunm][SQLServer JDBC Driver][SQLServer]
Operand type clash: nvarchar is incompatible with image Error Code: 206
Call:INSERT INTO DATATYPES2 (DATATYPES2_ID, TIMEDATA, TSDATA, CHARDATA, BYTEDATA) VALUES (?, ?, ?, ?, ?)
       bind => [2006-05-15, null, null, null, null]
Query:InsertObjectQuery(com.sun.ts.tests.ejb30.persistence.types.property.DataTypes2@1b8d241)
       at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:295)
       at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:639)

This problem is fixed by the attached NullParameterBinding.jar.

The fix basically adds JDBC sql type mappings for several java field types in the method DatabasePlatform#getJdbcType(Class):

665a667,670
>         } else if (javaType == ClassConstants.BYTE) {
>             return Types.TINYINT;
>         } else if (javaType == ClassConstants.CHAR) {
>             return Types.CHAR;
675a681,686
>         } else if (javaType == ClassConstants.SHORT ) {
>             return Types.SMALLINT;
>         } else if (javaType == ClassConstants.CALENDAR ) {
>             return Types.TIMESTAMP;
>         } else if (javaType == ClassConstants.UTILDATE ) {
>             return Types.TIMESTAMP;
681a693,694
>         } else if (javaType == ClassConstants.ABYTE) {
>             return Types.LONGVARBINARY;
685a699,704
>         } else if (javaType == ClassConstants.ACHAR) {
>             return Types.LONGVARCHAR;
>         } else if (javaType == ClassConstants.APCHAR) {
>             return Types.LONGVARCHAR;
>         } else if (javaType == ClassConstants.CLOB) {
>             return Types.CLOB;

Please note that none of the existing mappings is changed.

The archive also contains JUnit tests for entity-persistence-tests testing the binding of null values to sql parameters for multiple java field types. I ran these tests successfully against Oracle, JavaDB, DB2, SQLServer and Sybase. I also ran the FullRegressionSuite against JavaDB and Oracle. The tests against JavaDB succeeded, but there's one failure on Oracle, which seems to be unrelated. Please see the attached testresults.jar.

The other changes are:

- DatabasePlatform#buildClassTypes:
added mapping for "CLOB" -> Character[].class

- Added a data types test model:

PrimitiveTypes.java: A JPA class having all primitive type and a String field
WrapperTypes.java: A JPA class having all primitive wrapper type and a String field
ByteArrayType.java: A JPA class having a Byte[] field
PrimitiveByteArrayType.java: A JPA class having a byte[] field
CharacterArrayType.java: A JPA class having a Character[] field
CharArrayType.java: A JPA class having a char[] field

- Added an additional constructor for DateTime.java

- Added null binding tests for the datatypes and datetime test models

- PlatinumBuyer.java, Customer.java:
minor changes

- FullRegressionTestSuite.java:
added datatypes/NullBindingJUnitTestCase and datetime/NullBindingJUnitTestCase

Please have a look!

Thanks,

-- markus.