AnsweredAssumed Answered

Pointing to a Remote Server

Question asked by suraj.shankar on Jun 10, 2008
Hi,

We are trying to upload content to a alfresco server present on a

remote location using the sample JCR API provided with alfresco SDK. Alfresco server is backed by Oracle database.

We have changed the following configuration files in the remote server so that alfresco would point to oracle.

\Alfresco\tomcat\shared\classes\alfresco\extension\custom-repository.properties
db.driver=oracle.jdbc.OracleDriver
db.url=jdbc:oracle:thin:@xx.xxx.x.xxx:1521:orcl

\Alfresco\tomcat\shared\classes\alfresco\extension\custom-hibernate-dialect.properties

hibernate.dialect=org.hibernate.dialect.Oracle9Dialect



\Alfresco\tomcat\webapps\alfresco\WEB-INF\classes\alfresco\domain\hibernate-cfg.properties

hibernate.dialect=org.hibernate.dialect.OracleDialect


\Alfresco\tomcat\webapps\alfresco\WEB-INF\classes\alfresco\repository.properties
dir.root=./alf_data
db.schema.update=true
db.driver=oracle.jdbc.OracleDriver
db.name=alfresco
db.url=jdbc:oracle:thin:@xx.xxx.x.xxx:1521:orcl
db.username=alfresco
db.password=alfresco
db.pool.initial=10
db.pool.max=20

We are having a configuration file custom-repository.properties to override the dir.root value
dir.root=xx.xxx.x.xxx/root/LinuxV/alfresco210/alf_data

However when we execute the code, We are getting the following error.

 18:19:36,704 User:System WARN [remoting.rmi.RmiRegistryFactoryBean] Could not detect RMI registry - creating new one

18:19:39,892 User:System INFO [domain.schema.SchemaBootstrap] Schema managed by database dialect org.hibernate.dialect.MySQLInnoDBDialect.

18:19:39,892 User:System INFO [domain.schema.SchemaBootstrap] Bypassing schema update checks.

org.hibernate.exception.SQLGrammarException: could not execute query

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.loader.Loader.doList(Loader.java:2214)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)

at org.hibernate.loader.Loader.list(Loader.java:2090)

at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:388)

at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)

at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)

at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)

at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)

at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:804)

at org.jbpm.db.GraphSession.findLatestProcessDefinition(GraphSession.java:153)

at org.alfresco.repo.workflow.jbpm.JBPMEngine$2.doInJbpm(JBPMEngine.java:345)

at org.alfresco.repo.workflow.jbpm.JBPMEngine$2.doInJbpm(JBPMEngine.java:338)

at org.springmodules.workflow.jbpm31.JbpmTemplate$1.doInHibernate(JbpmTemplate.java:87)

at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:367)

at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:333)

at org.springmodules.workflow.jbpm31.JbpmTemplate.execute(JbpmTemplate.java:80)

at org.alfresco.repo.workflow.jbpm.JBPMEngine.isDefinitionDeployed(JBPMEngine.java:336)

at org.alfresco.repo.workflow.WorkflowServiceImpl.isDefinitionDeployed(WorkflowServiceImpl.java:153)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)

at org.alfresco.repo.security.permissions.impl.AlwaysProceedMethodInterceptor.invoke(AlwaysProceedMethodInterceptor.java:40)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)

at org.alfresco.repo.security.permissions.impl.ExceptionTranslatorMethodInterceptor.invoke(ExceptionTranslatorMethodInterceptor.java:49)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)

at org.alfresco.repo.audit.AuditComponentImpl.audit(AuditComponentImpl.java:241)

at org.alfresco.repo.audit.AuditMethodInterceptor.invoke(AuditMethodInterceptor.java:69)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)

at $Proxy44.isDefinitionDeployed(Unknown Source)

at org.alfresco.repo.workflow.WorkflowDeployer.init(WorkflowDeployer.java:247)

at org.alfresco.repo.workflow.WorkflowDeployer$1.doWork(WorkflowDeployer.java:287)

at org.alfresco.repo.security.authentication.AuthenticationUtil.runAs(AuthenticationUtil.java:299)

at org.alfresco.repo.workflow.WorkflowDeployer.onBootstrap(WorkflowDeployer.java:283)

at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:62)

at org.springframework.context.event.SimpleApplicationEventMulticaster$1.run(SimpleApplicationEventMulticaster.java:77)

at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49)

at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:75)

at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:241)

at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:349)

at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:92)

at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:77)

at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:68)

at com.wipro.dms.DMSHandler.<init>(DMSHandler.java:51)

at com.wipro.dms.DMSHandler.searchNode(DMSHandler.java:231)

at com.wipro.dms.DMSHandler.main(DMSHandler.java:68)

Caused by: java.sql.SQLException: ORA-00933: SQL command not properly ended


at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185)

at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:503)

at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:965)

at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:535)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1051)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3026)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)

at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)

at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)

at org.hibernate.loader.Loader.doQuery(Loader.java:662)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)

at org.hibernate.loader.Loader.doList(Loader.java:2211)

… 51 more

18:19:41,142 User:System ERROR [jbpm.db.GraphSession] org.hibernate.exception.SQLGrammarException: could not execute query

org.alfresco.error.AlfrescoRuntimeException: Workflow deployment failed

at org.alfresco.repo.workflow.WorkflowDeployer.init(WorkflowDeployer.java:268)

at org.alfresco.repo.workflow.WorkflowDeployer$1.doWork(WorkflowDeployer.java:287)

at org.alfresco.repo.security.authentication.AuthenticationUtil.runAs(AuthenticationUtil.java:299)

at org.alfresco.repo.workflow.WorkflowDeployer.onBootstrap(WorkflowDeployer.java:283)

at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:62)

at org.springframework.context.event.SimpleApplicationEventMulticaster$1.run(SimpleApplicationEventMulticaster.java:77)

at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49)

at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:75)

at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:241)

at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:349)

at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:92)

at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:77)

at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:68)

at com.wipro.dms.DMSHandler.<init>(DMSHandler.java:51)

at com.wipro.dms.DMSHandler.searchNode(DMSHandler.java:231)

at com.wipro.dms.DMSHandler.main(DMSHandler.java:68)

Caused by: org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:636)

at org.springmodules.workflow.jbpm31.JbpmAccessor.convertJbpmException(JbpmAccessor.java:47)

at org.springmodules.workflow.jbpm31.JbpmTemplate.execute(JbpmTemplate.java:97)

at org.alfresco.repo.workflow.jbpm.JBPMEngine.isDefinitionDeployed(JBPMEngine.java:336)

at org.alfresco.repo.workflow.WorkflowServiceImpl.isDefinitionDeployed(WorkflowServiceImpl.java:153)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)

at org.alfresco.repo.security.permissions.impl.AlwaysProceedMethodInterceptor.invoke(AlwaysProceedMethodInterceptor.java:40)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)

at org.alfresco.repo.security.permissions.impl.ExceptionTranslatorMethodInterceptor.invoke(ExceptionTranslatorMethodInterceptor.java:49)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)

at org.alfresco.repo.audit.AuditComponentImpl.audit(AuditComponentImpl.java:241)

at org.alfresco.repo.audit.AuditMethodInterceptor.invoke(AuditMethodInterceptor.java:69)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:107)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)

at $Proxy44.isDefinitionDeployed(Unknown Source)

at org.alfresco.repo.workflow.WorkflowDeployer.init(WorkflowDeployer.java:247)

… 15 more

Caused by: org.hibernate.exception.SQLGrammarException: could not execute query

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.loader.Loader.doList(Loader.java:2214)

at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)

at org.hibernate.loader.Loader.list(Loader.java:2090)

at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:388)

at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)

at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)

at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)

at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)

at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:804)

at org.jbpm.db.GraphSession.findLatestProcessDefinition(GraphSession.java:153)

at org.alfresco.repo.workflow.jbpm.JBPMEngine$2.doInJbpm(JBPMEngine.java:345)

at org.alfresco.repo.workflow.jbpm.JBPMEngine$2.doInJbpm(JBPMEngine.java:338)

at org.springmodules.workflow.jbpm31.JbpmTemplate$1.doInHibernate(JbpmTemplate.java:87)

at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:367)

at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:333)

at org.springmodules.workflow.jbpm31.JbpmTemplate.execute(JbpmTemplate.java:80)

… 36 more

Caused by: java.sql.SQLException: ORA-00933: SQL command not properly ended


at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)

at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)

at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185)

at oracle.jdbc.driver.T4CPreparedStatement.execute_for_describe(T4CPreparedStatement.java:503)

at oracle.jdbc.driver.OracleStatement.execute_maybe_describe(OracleStatement.java:965)

at oracle.jdbc.driver.T4CPreparedStatement.execute_maybe_describe(T4CPreparedStatement.java:535)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1051)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984)

at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3026)

at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)

at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)

at org.hibernate.loader.Loader.getResultSet(Loader.java:1778)

at org.hibernate.loader.Loader.doQuery(Loader.java:662)

at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)

at org.hibernate.loader.Loader.doList(Loader.java:2211)

… 51 more

Since it still displayed that Schema managed by database dialect org.hibernate.dialect.MySQLInnoDBDialect, we replaced the db.script.dialect parameter manually to org.hibernate.dialect.Dialect in the below files.

\Alfresco\tomcat\webapps\alfresco\WEB-INF\classes\alfresco\bootstrap-context.xml
\Alfresco\tomcat\webapps\alfresco\WEB-INF\classes\alfresco\patch\patch-services-context.xml

My questions are
1. How do we ensure that our code uploads content to the remote alfresco server
2. How to resolve the error org.hibernate.exception.SQLGrammarException: could not execute query

Can you please let us know where we are going wrong.

Outcomes