Tuesday, 12 October 2010

inconsistent datatypes with JPA.. eish

I've been having some weird errors with JPA eg.
(pasted here so you can find it with google)
==
INFO - batching 1 statements: 1: insert into dds.DDS_EMAIL_STATUS (DOC_REGISTER_ID, EMAIL_ADDRESS_FROM,
EMAIL_ADDRESS_TO, LAST_UPDATE_TIME, MESSAGE_ID, SEND_DATETIME, STATUS_ID, SUBJECT, DDS_EMAIL_STATUS_ID)
values (1001, 'dds-test@example.com', 'test@example.com', to_date('10/12/2010 12:41:05', 'mm/dd/yyyy
hh24:mi:ss'), NULL, to_date('10/12/2010 12:41:05', 'mm/dd/yyyy hh24:mi:ss'), 0, 'testSend',
800)  {executed in 4 msec}
INFO - executeBatch()
ERROR - 2. PreparedStatement.executeBatch() batching 2 statements:
1:  insert into dds.DDS_EMAIL_STATUS_LOG (DATE_TIME, DDS_EMAIL_STATUS_ID, MESSAGE, DDS_EMAIL_STATUS_LOG_ID) values (to_date('10/12/2010 12:41:05', 'mm/dd/yyyy hh24:mi:ss'), '', 'html body:
html body', 750)
java.sql.BatchUpdateException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY
    at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:343)
    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10720)
    at net.sf.log4jdbc.StatementSpy.executeBatch(StatementSpy.java:523)
    at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
    at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
    at org.hibernate.ejb.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:304)
    at org.apache.openejb.persistence.JtaEntityManager.flush(JtaEntityManager.java:130)
==
java.sql.SQLException: incompatible data types in combination in statement [alter table DDS_EMAIL_STATUS_LOG add constraint FKB7F2A8E6BAAD6FBB foreign key (DDS_EMAIL_STATUS_ID) references DDS_EMAIL_STATUS]
 at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
 at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
 at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
 at net.sf.log4jdbc.StatementSpy.executeUpdate(StatementSpy.java:694)
 at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
 at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
 at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:383)
 at org.hibernate.tool.hbm2ddl.SchemaExport.create(SchemaExport.java:341)
 at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:262)
 at org.hibernate.tool.hbm2ddl.SchemaExport.create(SchemaExport.java:211)
 at org.hibernate.impl.SessionFactoryImpl.(SessionFactoryImpl.java:343)
 at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1327)
 at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:867)
 at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:669)
...
Caused by: org.hsqldb.HsqlException: incompatible data types in combination
 at org.hsqldb.error.Error.error(Unknown Source)
 at org.hsqldb.error.Error.error(Unknown Source)
 at org.hsqldb.Table.checkColumnsMatch(Unknown Source)
 at org.hsqldb.TableWorks.checkCreateForeignKey(Unknown Source)
 at org.hsqldb.TableWorks.addForeignKey(Unknown Source)
 at org.hsqldb.ParserDDL.processAlterTableAddForeignKeyConstraint(Unknown Source)
 at org.hsqldb.ParserDDL.processAlterTable(Unknown Source)
 at org.hsqldb.ParserDDL.processAlter(Unknown Source)
 at org.hsqldb.StatementSchema.getResult(Unknown Source)
 at org.hsqldb.StatementSchema.execute(Unknown Source)
 at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
 at org.hsqldb.Session.executeDirectStatement(Unknown Source)
 at org.hsqldb.Session.execute(Unknown Source)
==
javax.persistence.PersistenceException: org.hibernate.type.SerializationException: could not deserialize
    at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
    at org.hibernate.ejb.AbstractEntityManagerImpl.remove(AbstractEntityManagerImpl.java:259)
    at org.apache.openejb.persistence.JtaEntityManager.remove(JtaEntityManager.java:107)
==
1:  update DDS_EMAIL_STATUS_LOG set DATE_TIME='10/12/2010 13:11:54.206', DDS_EMAIL_STATUS_ID='', MESSAGE='text body:
text body' where DDS_EMAIL_STATUS_LOG_ID=1 {FAILED after 0 msec}
java.sql.BatchUpdateException: data exception: string data, right truncation
 at org.hsqldb.jdbc.JDBCPreparedStatement.executeBatch(Unknown Source)
 at net.sf.log4jdbc.StatementSpy.executeBatch(StatementSpy.java:523)
 at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
 at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
 at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:168)
==
I tried different databases because it looked to me like the hsqldb is getting a bit confused with the column order. But in the end it turned out that I made a bit of a mistake:
I converted an embedded complex primary key to have a generated Id. In stead of annotating the referenced object with a @ManyToOne(optional = false)
it was still using @Column(name = "DDS_EMAIL_STATUS_ID"). After much debugging, googling and figuring out how to print the actual sql using http://code.google.com/p/log4jdbc-remix/ I figured out that I neglegted to change @Column to @ManyToOne.

Why is nothing ever easy ;-P

1 comment:

  1. I saw something else that was tricky with HSQL. If I execute the following using HSQL 2.2.9 , then it runs successfully.

    merge into LE101.daily_dph_instocks a
    USING ( SELECT ? activity_day FROM dual) b
    ON (a.activity_day = b.activity_day)
    WHEN MATCHED THEN UPDATE SET
    a.activity_day = b.activity_day

    But if I replace "ON (a.activity_day = b.activity_day)" in line 3 with "ON (b.activity_day = a.activity_day)"

    then my unit test throws this exception: java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: incompatible data types in combination

    ReplyDelete