3 Replies Latest reply on Oct 27, 2009 5:34 PM by adamw

AuditReader.find causing SQL exception

jefftalley Oct 14, 2009 2:02 PM

I am using envers 1.2.1.GA with Oracle and am getting a SQLGrammarException when calling the find method of the AuditReader. My code is very simple in that it is calling:

public Person findHistory(long id, long revision) {
 AuditReader reader = AuditReaderFactory.get(entityManager);
 Person result = reader.find(Person.class, id, revision);
 return result;
}

The underlying exception thrown by Oracle is: java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY. In the query, it is trying to exclude revision types of deleted but is not passing the number 2 but binary data instead.

I did some digging in the envers code and the problem seems to be caused by the EntitiesAtRevisionQuery class (line 76) which sets a parameter in the query for the revision type to RevisionType.DEL. This is trying to pass the enum as a query parameter instead of the underlying value. It seems like this should be RevisionType.DEL.getRepresentation() or RevisionType.DEL.ordinal() instead. I did put a break point after this line and replaced the value with the number 2 using the debugger and the query succeeds as expected.

Since this functionality is core to retrieving history, I feel that there is something about my configuration that is causing this to surface because I cannot find where anyone else has had this problem. There are a couple of other places in the code that are similar and will probably cause the same problems.

I also tried 3.5.0.Beta-1 and have the same problem.

Has anyone else had this problem or does anyone have any ideas around it?

1. Re: AuditReader.find causing SQL exception

adamw Oct 16, 2009 4:38 AM (in response to jefftalley)

Hmm, that's quite weird, as the column (revision type) is mapped using hibernate using the appropriate hibernate type (enum here). So it should convert to an integer properly. You have the dialect set properly?

Otherwise, it's quite hard for me to test, as I don't have an Oracle database, and on hsqldb, h2, mysql and postgresql it works.

Adam
Actions

2. Re: AuditReader.find causing SQL exception

jefftalley Oct 16, 2009 10:06 AM (in response to jefftalley)

I do have the Oracle dialect configured and it seems to be working for other things. It gets the Oracle specific things right like selecting sequences from DUAL. Althought, the configuration is not real straight-forward when using with Spring.

My persistence.xml file is mostly empty:

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
 version="1.0">
 <persistence-unit name="cmsTest" transaction-type="RESOURCE_LOCAL">
 </persistence-unit>
</persistence>

Most of the configuration is the the applicationContext.xml:

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
 <property name="persistenceUnitName" value="test"/>
 <property name="dataSource" ref="oracleDataSource"/>
 <property name="jpaVendorAdapter">
 <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
 <property name="showSql" value="true"/>
 <property name="generateDdl" value="false"/>
 <property name="databasePlatform" value="org.hibernate.dialect.OracleDialect"/>
 </bean>
 </property>
 <property name="jpaDialect">
 <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect"/>
 </property>
 <property name="jpaPropertyMap">
 <map>
 <entry key="hibernate.transaction.flush_before_completion" value="false"/>
 <entry key="hibernate.transaction.auto_close_session" value="false"/>
 <entry key="hibernate.current_session_context_class" value="jta"/>
 <entry key="hibernate.connection.release_mode" value="auto"/>
 <entry key="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/>
 <entry key="hibernate.query.factory_class" value="org.hibernate.hql.classic.ClassicQueryTranslatorFactory"/>

 <entry key="hibernate.ejb.event.post-insert" value="org.hibernate.ejb.event.EJB3PostInsertEventListener,org.hibernate.envers.event.AuditEventListener"/>
 <entry key="hibernate.ejb.event.post-update" value="org.hibernate.ejb.event.EJB3PostUpdateEventListener,org.hibernate.envers.event.AuditEventListener"/>
 <entry key="hibernate.ejb.event.post-delete" value="org.hibernate.ejb.event.EJB3PostDeleteEventListener,org.hibernate.envers.event.AuditEventListener"/>
 <entry key="hibernate.ejb.event.pre-collection-update" value="org.hibernate.envers.event.AuditEventListener"/>
 <entry key="hibernate.ejb.event.pre-collection-remove" value="org.hibernate.envers.event.AuditEventListener"/>
 <entry key="hibernate.ejb.event.post-collection-recreate" value="org.hibernate.envers.event.AuditEventListener"/>

 <entry key="org.hibernate.envers.auditTableSuffix" value="HIST"/>
 <entry key="org.hibernate.envers.revisionFieldName" value="REV_SYS_NR"/>
 <entry key="org.hibernate.envers.revisionTypeFieldName" value="REV_TYP_NR"/>
 </map>
 </property>
</bean>

I've worked around this for now by using the AuditReader.forRevisionsOfEntity and passing true to get the deleted entities. I then use code to determine if the object is deleted. If I pass false as this parameter, I get the same error. Basically, if I use AuditEntity.revisionType() as a criterion in the query, it will fail.

Jeff

3. Re: AuditReader.find causing SQL exception

adamw Oct 27, 2009 5:34 PM (in response to jefftalley)

As I said, I don't have access to an Oracle DB, so I can't debug it ... but the column to an enum, so it should be converted properly.

Please create a JIRA in case I get hold of an OracleDB in the future.

Adam
Actions

Go to original post