default byte[] -> BLOB mapping for JavaDB

[Dies K]

Hi,

I've run into an issue with an entity with a persistence field of type
byte[] and JavaDB.
The application worked on GF V2.0, but I get an error message on GF
V2.1/V3 when I do not initialize the field (i.e. leave it null):

Caused by: java.sql.SQLException: An attempt was made to get a data
value of type 'LONGVARBINARY' from a data value of type 'BLOB'.
        at
org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown
Source)
        at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
        at org.apache.derby.client.am.PreparedStatement.setNull(Unknown Source)
        at
oracle.toplink.essentials.internal.databaseaccess.DatabasePlatform.setComplexParameterValue(DatabasePlatform.java:1438)

The problem, in a nutshell, seems to be:

I use the default table generation function, so on JavaDB it creates a
table with column type BLOB.
When I create an entity and leave this field null, I get the error above
when I flush it to the DB: the Derby driver complains that I try to put
a Null of type Types.LONGVARBINARY into a column of type BLOB.

I found a lot of discussions on the Derby mailing list, and it seems the
error check was added intentionally between the Derby versions bundled
with GF v2.0 and GFv2.1/V3.

Why does GlassFish/Toplink choose BLOB as column type during table
generation for JavaDB?
From the Derby manual I understand that if it were mapped to LONG
VARCHAR FOR BIT DATA, this problem would not occur.

Thanks,
Dies

[Lance J. Andersen]

This appears to be a bug in Toplink/Eclipselink that probably did not  
show until Derby 10.4.x as there was a bug fix made to Derby and  
previous versions of Java DB/Derby included with Glassfish was based  
on 10.2.x.


Regards
Lance
On Jun 25, 2009, at 10:15 PM, Dies Koper wrote:

> Hi,
>
> I've run into an issue with an entity with a persistence field of type
> byte[] and JavaDB.
> The application worked on GF V2.0, but I get an error message on GF
> V2.1/V3 when I do not initialize the field (i.e. leave it null):
>
> Caused by: java.sql.SQLException: An attempt was made to get a data
> value of type 'LONGVARBINARY' from a data value of type 'BLOB'.
> at
> org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown
> Source)
> at org.apache.derby.client.am.SqlException.getSQLException(Unknown  
> Source)
> at org.apache.derby.client.am.PreparedStatement.setNull(Unknown  
> Source)
> at
> oracle
> .toplink
> .essentials
> .internal
> .databaseaccess
> .DatabasePlatform.setComplexParameterValue(DatabasePlatform.java:1438)
>
> The problem, in a nutshell, seems to be:
>
> I use the default table generation function, so on JavaDB it creates a
> table with column type BLOB.
> When I create an entity and leave this field null, I get the error  
> above
> when I flush it to the DB: the Derby driver complains that I try to  
> put
> a Null of type Types.LONGVARBINARY into a column of type BLOB.
>
> I found a lot of discussions on the Derby mailing list, and it seems  
> the
> error check was added intentionally between the Derby versions bundled
> with GF v2.0 and GFv2.1/V3.
>
> Why does GlassFish/Toplink choose BLOB as column type during table
> generation for JavaDB?
> From the Derby manual I understand that if it were mapped to LONG
> VARCHAR FOR BIT DATA, this problem would not occur.
>
> Thanks,
> Dies
>

[Dies K]

Hi Lance, Mitesh,

Thanks for the quick reply, Lance!

Mitesh, if I understand correctly you have written the Derby support in
TLE/EclipseLink?

I also sent an e-mail to the Derby dev ML about this issue and got the
following reply from Rick Hillegas:

-------------
I am not an expert on Toplink or its default datatype mappings for
Derby. I think that LONGVARBINARY is a better mapping for byte[] and I
would reserve BLOB as the mapping for columns which you really want to
manipulate using the java.sql.Blob methods. It may be that BLOB is the
default mapping because it can hold more bytes than the Derby
LONGVARBINARY datatype (LONG VARCHAR FOR BIT DATA). A Derby BLOB can
hold up to 2,147,483,647 bytes while a Derby LONG VARCHAR FOR BIT DATA
can only hold up to 32,700 bytes.

I don't know why Toplink is trying to put a LONGVARBINARY NULL into a
column of type BLOB. The javadoc for PreparedStatement.setNull() states
that the type of the NULL must be the type of the column. So I would
expect that the code would do this instead:

   ps.setNull( n, java.sql.Types.BLOB );

Again, I'm not an expert on the O/R mapping frameworks, but I get the
impression that it is fairly easy to tweak their vendor-specific
datatype mappings. If your byte[] fields aren't longer than 32,700 bytes
and you don't use the java.sql.Blob methods, then LONG VARCHAR FOR BIT
DATA ought to work for you.
-------------

In TLE, for Oracle, byte[] fields map to LONG RAW while byte[] annotated
with @Lob map to BLOB. Can't we do the same for JavaDB/Derby and make
byte[] fields without the @Lob annotation map to LONG VARCHAR FOR BIT DATA?

Thanks,
Dies


Lance Andersen wrote:

> This appears to be a bug in Toplink/Eclipselink that probably did not
> show until Derby 10.4.x as there was a bug fix made to Derby and
> previous versions of Java DB/Derby included with Glassfish was based on
> 10.2.x.
>
>
> Regards
> Lance
> On Jun 25, 2009, at 10:15 PM, Dies Koper wrote:
>
>> Hi,
>>
>> I've run into an issue with an entity with a persistence field of type
>> byte[] and JavaDB.
>> The application worked on GF V2.0, but I get an error message on GF
>> V2.1/V3 when I do not initialize the field (i.e. leave it null):
>>
>> Caused by: java.sql.SQLException: An attempt was made to get a data
>> value of type 'LONGVARBINARY' from a data value of type 'BLOB'.
>>     at
>> org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown
>> Source)
>>     at org.apache.derby.client.am.SqlException.getSQLException(Unknown
>> Source)
>>     at org.apache.derby.client.am.PreparedStatement.setNull(Unknown
>> Source)
>>     at
>> oracle.toplink.essentials.internal.databaseaccess.DatabasePlatform.setComplexParameterValue(DatabasePlatform.java:1438)
>>
>>
>> The problem, in a nutshell, seems to be:
>>
>> I use the default table generation function, so on JavaDB it creates a
>> table with column type BLOB.
>> When I create an entity and leave this field null, I get the error above
>> when I flush it to the DB: the Derby driver complains that I try to put
>> a Null of type Types.LONGVARBINARY into a column of type BLOB.
>>
>> I found a lot of discussions on the Derby mailing list, and it seems the
>> error check was added intentionally between the Derby versions bundled
>> with GF v2.0 and GFv2.1/V3.
>>
>> Why does GlassFish/Toplink choose BLOB as column type during table
>> generation for JavaDB?
>> From the Derby manual I understand that if it were mapped to LONG
>> VARCHAR FOR BIT DATA, this problem would not occur.
>>
>> Thanks,
>> Dies

[Mitesh Meswani]

Hi Dies,

TLE is not actively being worked on. Can you please file an issue
against EclipseLink
(https://bugs.eclipse.org/bugs/enter_bug.cgi?product=EclipseLink)to 
track this.

Thanks,
Mitesh

Dies Koper wrote:

> Hi Lance, Mitesh,
>
> Thanks for the quick reply, Lance!
>
> Mitesh, if I understand correctly you have written the Derby support
> in TLE/EclipseLink?
>
> I also sent an e-mail to the Derby dev ML about this issue and got the
> following reply from Rick Hillegas:
>
> -------------
> I am not an expert on Toplink or its default datatype mappings for
> Derby. I think that LONGVARBINARY is a better mapping for byte[] and I
> would reserve BLOB as the mapping for columns which you really want to
> manipulate using the java.sql.Blob methods. It may be that BLOB is the
> default mapping because it can hold more bytes than the Derby
> LONGVARBINARY datatype (LONG VARCHAR FOR BIT DATA). A Derby BLOB can
> hold up to 2,147,483,647 bytes while a Derby LONG VARCHAR FOR BIT DATA
> can only hold up to 32,700 bytes.
>
> I don't know why Toplink is trying to put a LONGVARBINARY NULL into a
> column of type BLOB. The javadoc for PreparedStatement.setNull() states
> that the type of the NULL must be the type of the column. So I would
> expect that the code would do this instead:
>
>   ps.setNull( n, java.sql.Types.BLOB );
>
> Again, I'm not an expert on the O/R mapping frameworks, but I get the
> impression that it is fairly easy to tweak their vendor-specific
> datatype mappings. If your byte[] fields aren't longer than 32,700 bytes
> and you don't use the java.sql.Blob methods, then LONG VARCHAR FOR BIT
> DATA ought to work for you.
> -------------
>
> In TLE, for Oracle, byte[] fields map to LONG RAW while byte[]
> annotated with @Lob map to BLOB. Can't we do the same for JavaDB/Derby
> and make byte[] fields without the @Lob annotation map to LONG VARCHAR
> FOR BIT DATA?
>
> Thanks,
> Dies
>
>
> Lance Andersen wrote:
>> This appears to be a bug in Toplink/Eclipselink that probably did not
>> show until Derby 10.4.x as there was a bug fix made to Derby and
>> previous versions of Java DB/Derby included with Glassfish was based
>> on 10.2.x.
>>
>>
>> Regards
>> Lance
>> On Jun 25, 2009, at 10:15 PM, Dies Koper wrote:
>>
>>> Hi,
>>>
>>> I've run into an issue with an entity with a persistence field of type
>>> byte[] and JavaDB.
>>> The application worked on GF V2.0, but I get an error message on GF
>>> V2.1/V3 when I do not initialize the field (i.e. leave it null):
>>>
>>> Caused by: java.sql.SQLException: An attempt was made to get a data
>>> value of type 'LONGVARBINARY' from a data value of type 'BLOB'.
>>>     at
>>> org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown
>>> Source)
>>>     at
>>> org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
>>>     at org.apache.derby.client.am.PreparedStatement.setNull(Unknown
>>> Source)
>>>     at
>>> oracle.toplink.essentials.internal.databaseaccess.DatabasePlatform.setComplexParameterValue(DatabasePlatform.java:1438)
>>>
>>>
>>> The problem, in a nutshell, seems to be:
>>>
>>> I use the default table generation function, so on JavaDB it creates a
>>> table with column type BLOB.
>>> When I create an entity and leave this field null, I get the error
>>> above
>>> when I flush it to the DB: the Derby driver complains that I try to put
>>> a Null of type Types.LONGVARBINARY into a column of type BLOB.
>>>
>>> I found a lot of discussions on the Derby mailing list, and it seems
>>> the
>>> error check was added intentionally between the Derby versions bundled
>>> with GF v2.0 and GFv2.1/V3.
>>>
>>> Why does GlassFish/Toplink choose BLOB as column type during table
>>> generation for JavaDB?
>>> From the Derby manual I understand that if it were mapped to LONG
>>> VARCHAR FOR BIT DATA, this problem would not occur.
>>>
>>> Thanks,
>>> Dies
>
>