Issue Details (XML | Word | Printable)

Key: NUCRDBMS-412
Type: Task Task
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Andy Jefferson
Reporter: Andy Jefferson
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

Validate against HSQLDB 2.0.0 and add any updates in handling

Created: 09/Jun/10 08:20 PM   Updated: 27/Jun/10 08:57 AM   Resolved: 13/Jun/10 02:34 PM
Component/s: None
Affects Version/s: None
Fix Version/s: 2.1.1


 Description  « Hide
HSQLDB 2.0.0 supposedly provides the widest support for ANSI SQL than any other open source RDBMS. Likely need to update our handling for it

Andy Jefferson added a comment - 09/Jun/10 08:42 PM
Use of BLOB columns and getting the value via getBytes() no longer seems to work. Likely needs changing to getBlob().getBytes()


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.hsqldb.jdbc.Util.throwError(Unknown Source)
        at org.hsqldb.jdbc.JDBCResultSet.getColumnInType(Unknown Source)
        at org.hsqldb.jdbc.JDBCResultSet.getBytes(Unknown Source)
        at org.apache.commons.dbcp.DelegatingResultSet.getBytes(DelegatingResultSet.java:203)
        at org.datanucleus.store.rdbms.mapping.AbstractLargeBinaryRDBMSMapping.getObject(AbstractLargeBinaryRDBMSMapping.java:259)
        at org.datanucleus.store.mapped.mapping.AbstractContainerMapping.getObject(AbstractContainerMapping.java:228)
        at org.datanucleus.store.rdbms.fieldmanager.ResultSetGetter.fetchObjectField(ResultSetGetter.java:153)
        at org.datanucleus.state.AbstractStateManager.replacingObjectField(AbstractStateManager.java:1191)
        at org.jpox.samples.array.IntObjectArray.jdoReplaceField(IntObjectArray.java)
        at org.jpox.samples.array.IntObjectArray.jdoReplaceFields(IntObjectArray.java)
        at org.datanucleus.jdo.state.JDOStateManagerImpl.replaceFields(JDOStateManagerImpl.java:2860)
        at org.datanucleus.jdo.state.JDOStateManagerImpl.replaceFields(JDOStateManagerImpl.java:2879)
        at org.datanucleus.state.ObjectProviderImpl.replaceFields(ObjectProviderImpl.java:69)
        at org.datanucleus.store.rdbms.request.FetchRequest.execute(FetchRequest.java:334)
        at org.datanucleus.store.rdbms.RDBMSPersistenceHandler.fetchObject(RDBMSPersistenceHandler.java:319)
        at org.datanucleus.jdo.state.JDOStateManagerImpl.loadFieldsFromDatastore(JDOStateManagerImpl.java:1928)
        at org.datanucleus.jdo.state.JDOStateManagerImpl.loadSpecifiedFields(JDOStateManagerImpl.java:1564)
        at org.datanucleus.jdo.state.JDOStateManagerImpl.isLoaded(JDOStateManagerImpl.java:2008)

Andy Jefferson added a comment - 13/Jun/10 09:08 AM
CREATE TABLE IDENTITYGENERATORITEMNOFIELD
(
    IDENTITYGENERATORITEMNOFIELD_ID BIGINT NOT NULL IDENTITY,
    PRIMARY KEY (IDENTITYGENERATORITEMNOFIELD_ID)
)
09:07:00,432 (main) ERROR [DataNucleus.Datastore] - Error thrown executing CREATE TABLE IDENTITYGENERATORITEMNOFIELD
(
    IDENTITYGENERATORITEMNOFIELD_ID BIGINT NOT NULL IDENTITY,
    PRIMARY KEY (IDENTITYGENERATORITEMNOFIELD_ID)
) : primary key already exist
java.sql.SQLException: primary key already exist
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
        at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
        at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatement(AbstractTable.java:730)


so it seems that HSQL 2.0 objects to the specification of a PK when there is an IDENTITY column defined in the DDL. pedantic

Andy Jefferson added a comment - 13/Jun/10 09:37 AM
BLOB issue is fixed in SVN trunk.
IDENTITY issue is raised on HSQLDB forums

Andy Jefferson made changes - 13/Jun/10 12:08 PM
Field Original Value New Value
Assignee Andy Jefferson [ andy ]
Andy Jefferson added a comment - 13/Jun/10 12:11 PM
IDENTITY issue is fixed by changing the autoincrement keyword to
GENERATED BY DEFAULT AS IDENTITY
and disallowing NULL in the autoincrement column def.

Next problem is
SELECT DEVELOPER_ID as id, SKILL FROM DEVELOPER WHERE SKILL = <'jdo'>
returns the "id" column name as "ID" and this fails to match the field name in the result class. HSQLDB 1.8 (incorrectly) returned lowercase.

Andy Jefferson added a comment - 13/Jun/10 02:34 PM
SVN trunk now works fine with HSQLDB 2.0.0 with the exception of use of LOCATE(str1, str2, pos) since HSQLDB 2.0.0 has a bug where the last arg is currently ignored - should be fixed in HSQLDB 2.0.1

Andy Jefferson made changes - 13/Jun/10 02:34 PM
Status Open [ 1 ] Resolved [ 5 ]
Fix Version/s 2.1.1 [ 10991 ]
Fix Version/s 2.2.0.release [ 10932 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 27/Jun/10 08:57 AM
Status Resolved [ 5 ] Closed [ 6 ]