DataNucleus JIRA is now in read-only mode. Raise any new issues in GitHub against the plugin that it applies to. DataNucleus JIRA will remain for the foreseeable future but will eventually be discontinued
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

Sort Order: Ascending order - Click to sort in descending order
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 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