Hibernate/hsqldb 2 Cannot Hydrate Blob Column

I am trying to load an entity with byte data (annotated with @Lob) from an HSQL 2.0 database using Hibernate 3.5.6. This entity is able to be saved without any problems and is loaded fine if it is in the cache (i.e. does not need to be hydrated). However, when the entity is not in the cache (needs to be hydrated), I receive the following exception:

Caused by: org.hsqldb.HsqlException: incompatible data type in conversion: from SQL type BLOB to [B, value: instance of org.hsqldb.types.BlobDataID
    at org.hsqldb.error.Error.error(Unknown Source)
    ... 68 more

Here is the full stack trace (minus some domain specific trace) for more context:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1235)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1168)
...

Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2452)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2192)
at org.hibernate.loader.Loader.list(Loader.java:2187)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1258)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:241)
... 45 more

Caused by: java.sql.SQLSyntaxErrorException: incompatible data type in conversion: from SQL type BLOB to [B, value: instance of org.hsqldb.types.BlobDataID
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsq开发者_运维百科ldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.JDBCResultSet.getColumnInType(Unknown Source)
at org.hsqldb.jdbc.JDBCResultSet.getBytes(Unknown Source)
at org.hsqldb.jdbc.JDBCResultSet.getBytes(Unknown Source)
at org.hibernate.type.AbstractBynaryType.get(AbstractBynaryType.java:103)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:186)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:175)
at org.hibernate.type.AbstractType.hydrate(AbstractType.java:105)
at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2267)
at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1443)
at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1371)
at org.hibernate.loader.Loader.getRow(Loader.java:1271)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:619)
at org.hibernate.loader.Loader.doQuery(Loader.java:745)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
at org.hibernate.loader.Loader.doList(Loader.java:2449)
... 53 more
Caused by: org.hsqldb.HsqlException: incompatible data type in conversion: from SQL type BLOB to [B, value: instance of org.hsqldb.types.BlobDataID
at org.hsqldb.error.Error.error(Unknown Source)
... 68 more

This problem did not occur when I used Hibernate 3.5.6 and HSQL 1.8.0.10, probably because of the different dialect used (Blob column wasn't supported in HSQL 1.8). The version of Hibernate that I'm using is supposed to support HSQL 2 and I have checked that it is properly detecting the HSQL version and using the correct dialect. This problem also does not occur when I use MySQL.

The entity in question has a Lob column which is set up like so:

@Entity
public class ImageEntity extends IdEntity {

    @Lob
    @Column(name="IMAGE")
    private byte[] imageBytes;
    ...

Is this a Hibernate/HSQL 2.0 bug?

---

I found the problem in JDBCResultSet.java (HSQLDB 2.0.0 downloaded from Sourceforge project) getBytes method. HSQLDB 2.0 supports a blob column where 1.8.* doesn't, so it might be a bug where they haven't fully updated their JDBC implementation. The following patch to the getBytes method solves the problem:

public byte[] getBytes(int columnIndex) throws SQLException {

    Type sourceType = resultMetaData.columnTypes[columnIndex-1];
    if (sourceType.isLobType()){
        Blob b = getBlob(columnIndex);
        return b.getBytes(1, (int)b.length());
    }

    Object x = getColumnInType(columnIndex, Type.SQL_VARBINARY);

    if (x == null) {
        return null;
    }

    return ((BinaryData) x).getBytes();
}

Original method was:

public byte[] getBytes(int columnIndex) throws SQLException {

    Object x = getColumnInType(columnIndex, Type.SQL_VARBINARY);

    if (x == null) {
        return null;
    }

    return ((BinaryData) x).getBytes();
}