FirstFoundationClient and Oracle

[pcuvecle]

Hi all,

I try to run the FirstFoundationClient from the SDK with an Oracle database but unfortunately I have a Oracle PK violation when trying to create the node . Here is the stack trace :

- ORA-01400: cannot insert NULL into ("ALFRESCO"."NODE"."ID")

Exception in thread "main" org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not insert: [org.alfresco.repo.domain.hibernate.NodeImpl]; SQL [insert into node (protocol, identifier, uuid, type_qname, acl_id) values (?, ?, ?, ?, ?)]; ORA-01400: cannot insert NULL into ("ALFRESCO"."NODE"."ID")
; nested exception is java.sql.SQLException: ORA-01400: cannot insert NULL into ("ALFRESCO"."NODE"."ID")

java.sql.SQLException: ORA-01400: cannot insert NULL into ("ALFRESCO"."NODE"."ID")

   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
   at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
   at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
   at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
   at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
   at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
   at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
   at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
   at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:1976)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2405)
   at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:37)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
   at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:269)
   at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:167)
   at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:101)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
   at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
   at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
   at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:530)
   at org.hibernate.impl.SessionImpl.save(SessionImpl.java:518)
   at org.hibernate.impl.SessionImpl.save(SessionImpl.java:514)
   at org.springframework.orm.hibernate3.HibernateTemplate$12.doInHibernate(HibernateTemplate.java:615)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:365)
   at org.springframework.orm.hibernate3.HibernateTemplate.save(HibernateTemplate.java:612)
   at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.newNode(HibernateNodeDaoServiceImpl.java:264)
   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:585)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:335)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:181)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)
   at org.alfresco.repo.transaction.TransactionalDaoInterceptor.invoke(TransactionalDaoInterceptor.java:60)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
   at $Proxy1.newNode(Unknown Source)
   at org.alfresco.repo.node.db.DbNodeServiceImpl.createNode(DbNodeServiceImpl.java:281)
   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:585)
   at org.alfresco.repo.service.StoreRedirectorProxyFactory$RedirectorInvocationHandler.invoke(StoreRedirectorProxyFactory.java:213)
   at $Proxy2.createNode(Unknown Source)
   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:585)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:335)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:181)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)
   at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:116)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
   at net.sf.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:80)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
   at org.alfresco.repo.security.permissions.impl.ExceptionTranslatorMethodInterceptor.invoke(ExceptionTranslatorMethodInterceptor.java:40)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:176)
   at $Proxy15.createNode(Unknown Source)
   at org.alfresco.sample.FirstFoundationClient.doExample(FirstFoundationClient.java:108)
   at org.alfresco.sample.FirstFoundationClient$1.doWork(FirstFoundationClient.java:72)
   at org.alfresco.repo.transaction.TransactionUtil.executeInTransaction(TransactionUtil.java:168)
   at org.alfresco.repo.transaction.TransactionUtil.executeInUserTransaction(TransactionUtil.java:74)
   at org.alfresco.sample.FirstFoundationClient.main(FirstFoundationClient.java:76)

I've seen that a similar issue used to exist in Alfresco 1.1 for instance, But I though it was supposed to be fixed (as I'm running v1.3)

Any ideas about this ?

Thanks
Philippe

[derek]

Hi,

This is not the same issue, which referred to Oracle forcing empty strings to be null values.

I'm going to guess that you're using the MySQL dialect.  You'll need to make use of overrides (config/alfresco/extensions) or similar to change the Hibernate dialect (see config/alfresco/domain/hibernate-cfg.properties).

MySQL autogenerates the ID, whereas Hibernate has to use a sequence when inserting into Oracle.

Regards