Hypersonice DB problem with @Column(columnDefinition = "BLOB NOT NULL")

[Dr.Drane]

Hi all,

I hope this is still a bit on topic because it might be an hypernic issue...


As ROO-873 / ROO-1127 are a bit of a show stopper for using Mysql DB's to store images, I thought I might give Hypersonic DB a try.

Though now I run into another issue "unexpected token: NOT"...

My image variable:

    @Lob
    @Basic(fetch = FetchType.LAZY)
    @Column(columnDefinition = "BLOB NOT NULL")
    @NotNull
    private Byte[] source; 

The error it throws:

2010-10-08 00:47:23,288 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - Unsuccessful: create table image (id bigint generated by default as identity (start with 1), active bit, create_date timestamp, name varchar(255) not null, size double not null, source BLOB NOT NULL not null, title varchar(255) not null, type varchar(4) not null, update_date timestamp, version integer, article bigint, primary key (id))
2010-10-08 00:47:23,288 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - unexpected token: NOT
2010-10-08 00:47:23,290 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - Unsuccessful: alter table image add constraint FK5FAA95B9252F6B5 foreign key (article) references article
2010-10-08 00:47:23,290 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaExport - user lacks privilege or object not found: IMAGE 

Adjusting columnDefinition to:

@Column(columnDefinition = "BLOB") 

(or removing the @NotNull)

causes another issue trying to open a saved image entity:

2010-10-08 00:54:01,249 [tomcat-http--4] ERROR org.hibernate.util.JDBCExceptionReporter - incompatible data type in conversion: from SQL type BLOB to [B, value: instance of org.hsqldb.types.BlobDataID 

Any ideas for a fix or workaround?

I really want to save images related to other entitities, and since GAE entity relationships are no option, mysql and hypersonic have issues...are there any other options / DB's I could try perhaps?

Thanks a lot in advance and good luck with the project!

[Alan Stewart]

If you don't run any tests, does your app work? Are there Roo-generated annotations preventing it from working? If you push-in refactor everything and change the annotations etc can you get it to work?

[nypee]

I have the same problem with Roo 1.1.0 Release version with the Roo-generated IntegrationTest for the entity. The push-in refactoring does not help. Following is the entity and the stack trace.

I did not find and other discussion of this problem anywhere on the internet, except for here: http://www.datanucleus.org/servlet/j...e/NUCRDBMS-412

Does anyone know the solution for this problem? The entity works perfectly with Microsoft SQLServer in production, only the unit test fails.

@Configurable
@Entity
public class DocumentPart {

	@Basic(fetch = LAZY)
	@Lob
	@Column(name = "CONTENT", columnDefinition = "BLOB")
	@NotNull
	private byte[] content;

org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not execute query; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
	at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:311)
	at org.springframework.orm.jpa.aspectj.JpaExceptionTranslatorAspect.ajc$afterThrowing$org_springframework_orm_jpa_aspectj_JpaExceptionTranslatorAspect$1$18a1ac9(JpaExceptionTranslatorAspect.aj:15)
	at hu.sri.archiver.model.DocumentPart_Roo_Entity.findDocumentPartEntries_aroundBody8(DocumentPart_Roo_Entity.aj:103)
	at hu.sri.archiver.model.DocumentPart_Roo_Entity.ajc$interMethod$hu_sri_archiver_model_DocumentPart_Roo_Entity$hu_sri_archiver_model_DocumentPart$findDocumentPartEntries(DocumentPart_Roo_Entity.aj:1)
	at hu.sri.archiver.model.DocumentPart.findDocumentPartEntries(DocumentPart.java:1)
	at hu.sri.archiver.model.DocumentPart_Roo_Entity.ajc$interMethodDispatch1$hu_sri_archiver_model_DocumentPart_Roo_Entity$hu_sri_archiver_model_DocumentPart$findDocumentPartEntries(DocumentPart_Roo_Entity.aj)
	at hu.sri.archiver.model.DocumentPartDataOnDemand_Roo_DataOnDemand.ajc$interMethod$hu_sri_archiver_model_DocumentPartDataOnDemand_Roo_DataOnDemand$hu_sri_archiver_model_DocumentPartDataOnDemand$init(DocumentPartDataOnDemand_Roo_DataOnDemand.aj:58)
	at hu.sri.archiver.model.DocumentPartDataOnDemand.init(DocumentPartDataOnDemand.java:1)
	at hu.sri.archiver.model.DocumentPartDataOnDemand_Roo_DataOnDemand.ajc$interMethodDispatch1$hu_sri_archiver_model_DocumentPartDataOnDemand_Roo_DataOnDemand$hu_sri_archiver_model_DocumentPartDataOnDemand$init(DocumentPartDataOnDemand_Roo_DataOnDemand.aj)
	at hu.sri.archiver.model.DocumentPartDataOnDemand_Roo_DataOnDemand.ajc$interMethod$hu_sri_archiver_model_DocumentPartDataOnDemand_Roo_DataOnDemand$hu_sri_archiver_model_DocumentPartDataOnDemand$getRandomDocumentPart(DocumentPartDataOnDemand_Roo_DataOnDemand.aj:43)
	at hu.sri.archiver.model.DocumentPartDataOnDemand.getRandomDocumentPart(DocumentPartDataOnDemand.java:1)
	at hu.sri.archiver.model.DocumentPartDataOnDemand_Roo_DataOnDemand.ajc$interMethodDispatch1$hu_sri_archiver_model_DocumentPartDataOnDemand_Roo_DataOnDemand$hu_sri_archiver_model_DocumentPartDataOnDemand$getRandomDocumentPart(DocumentPartDataOnDemand_Roo_DataOnDemand.aj)
	at hu.sri.archiver.model.DocumentPartIntegrationTest_Roo_IntegrationTest.ajc$interMethod$hu_sri_archiver_model_DocumentPartIntegrationTest_Roo_IntegrationTest$hu_sri_archiver_model_DocumentPartIntegrationTest$testFindAllDocumentParts(DocumentPartIntegrationTest_Roo_IntegrationTest.aj:43)
	at hu.sri.archiver.model.DocumentPartIntegrationTest.testFindAllDocumentParts(DocumentPartIntegrationTest.java:1)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:240)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:180)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: 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)
	at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:250)
	... 40 more
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)
	... 40 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.hsqldb.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.apache.commons.dbcp.DelegatingResultSet.getBytes(DelegatingResultSet.java:291)
	at org.apache.commons.dbcp.DelegatingResultSet.getBytes(DelegatingResultSet.java:291)
	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)
	... 48 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)
	... 65 more