Using PostgreSQL for UFS repository DB

Post any questions you have about using the Verj.io Studio, including client and server-side programming with Javascript or FPL, and integration with databases, web services etc.

Moderators: Dave, Jon, Steve, Ian

ericb
Ebase User
Posts: 71
Joined: Fri Jan 15, 2016 2:34 pm

Using PostgreSQL for UFS repository DB

#1

Postby ericb » Mon Oct 07, 2019 6:51 pm

We are in the process of migrating from Oracle to PostgreSQL, and one of our applications has its UFS Repository DB in an Oracle database rather than the default Derby. We've migrated the UFS database schema from Oracle to our Postgre staging environment, and I've updated our application's configuration to connect to the new Postgre DB.

When calling a fetchTable on the WF_TASKLIST resource, I get the error below. This error didn't occur with the Oracle schema, so I'm assuming it's some sort of SQL incompatibility, however since it's a custom resource, even selecting the "Debug" option in the resource doesn't show what SQL query is being run, so I'm not sure how I can troubleshoot the issue.

Do we need to build our own custom resources for using the UFSRepository DB in Postgre?

This is using Ebase 5.2.0.

Code: Select all

Mon Oct 07 14:21:52: DEBUG Start execution of command - fetchtable: 14:21:52.340
com.ebasetech.ufs.utility.PersistenceException: SQL Exception generated by embedded code: ERROR: syntax error at or near "class"
  Position: 17
	at com.ebasetech.ufs.workflow.server.external.WorkflowManager.protect(WorkflowManager.java:932)
	at com.ebasetech.ufs.workflow.server.external.WorkflowManager.getFilteredTasks(WorkflowManager.java:2782)
	at com.ebasetech.ufs.workflow.form.customresources.WorkflowTaskList.getTaskList(WorkflowTaskList.java:411)
	at com.ebasetech.ufs.workflow.form.customresources.WorkflowTaskList.fetchTable(WorkflowTaskList.java:151)
	at com.ebasetech.ufs.runtime.resources.RuntimeCustomSource.fetchTable(RuntimeCustomSource.java:138)
	at com.ebasetech.ufs.runtime.RuntimeTableField.fetchTable(RuntimeTableField.java:504)
	at com.ebasetech.ufs.runtime.RuntimeTableField.fetchTable(RuntimeTableField.java:461)
	at com.ebasetech.ufs.runtime.external.api.impl.ApiTableBase.fetchTable(ApiTableBase.java:126)
	at com.ebasetech.ufs.runtime.external.api.impl.ApiTableBase.fetchTable(ApiTableBase.java:118)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:126)
	at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:225)
	at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1479)
	at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:815)
	at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:109)
	at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:393)
	at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3280)
	at org.mozilla.javascript.Context.callFunctionWithContinuations(Context.java:1284)
	at org.mozilla.javascript.Context.executeScriptWithContinuations(Context.java:1251)
	at com.ebasetech.ufs.runtime.events.rhinojavascript.JavascriptExecutionHandler.execute(JavascriptExecutionHandler.java:270)
	at com.ebasetech.ufs.runtime.events.JVMScriptNode.execute_(JVMScriptNode.java:133)
	at com.ebasetech.ufs.runtime.events.JVMScriptNode.execute(JVMScriptNode.java:99)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.events.SuspendableExecutable.execute(SuspendableExecutable.java:47)
	at com.ebasetech.ufs.runtime.events.SuspendableContainerExecutable.execute(SuspendableContainerExecutable.java:26)
	at com.ebasetech.ufs.runtime.events.BaseEventNode.execute(BaseEventNode.java:32)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.events.SuspendableExecutable.execute(SuspendableExecutable.java:47)
	at com.ebasetech.ufs.runtime.events.SuspendableContainerExecutable.execute(SuspendableContainerExecutable.java:26)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.events.SuspendableExecutable.execute(SuspendableExecutable.java:47)
	at com.ebasetech.ufs.runtime.events.SuspendableContainerExecutable.execute(SuspendableContainerExecutable.java:26)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.events.SuspendableExecutable.execute(SuspendableExecutable.java:47)
	at com.ebasetech.ufs.runtime.events.SuspendableContainerExecutable.execute(SuspendableContainerExecutable.java:26)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.Request.execute(Request.java:448)
	at com.ebasetech.ufs.runtime.Request.execute(Request.java:417)
	at com.ebasetech.ufs.runtime.Request._execute(Request.java:352)
	at com.ebasetech.ufs.runtime.Request.execute(Request.java:317)
	at formservlets.FormProcessingUtil.executeRequest(FormProcessingUtil.java:503)
	at formservlets.FormProcessor.performExecution(FormProcessor.java:162)
	at formservlets.FormProcessor.processRequest(FormProcessor.java:149)
	at formservlets.FormProcessor.runForm(FormProcessor.java:46)
	at formservlets.FormClient.process(FormClient.java:118)
	at formservlets.FormClient.doGet(FormClient.java:48)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at formservlets.EbaseFilter.processHtmlRequest(EbaseFilter.java:355)
	at formservlets.EbaseFilter.processRequest(EbaseFilter.java:258)
	at formservlets.EbaseFilter.performFilter(EbaseFilter.java:180)
	at formservlets.EbaseFilter.doFilter(EbaseFilter.java:100)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:198)
	at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:176)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:203)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1526)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1482)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "class"
  Position: 17
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:82)
	at sun.reflect.GeneratedMethodAccessor43.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at net.bull.javamelody.JdbcWrapper.doExecute(JdbcWrapper.java:403)
	at net.bull.javamelody.JdbcWrapper$StatementInvocationHandler.invoke(JdbcWrapper.java:128)
	at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:285)
	at com.sun.proxy.$Proxy6.executeQuery(Unknown Source)
	at com.ebasetech.ufs.workflow.server.optimisations.OptimisedTaskList.filteredTaskEnactments(OptimisedTaskList.java:133)
	at com.ebasetech.ufs.workflow.server.external.WorkflowManager$48.go(WorkflowManager.java:2788)
	at com.ebasetech.ufs.workflow.server.external.WorkflowManager.protect(WorkflowManager.java:928)
	... 80 more
Mon Oct 07 14:21:52: ERROR Error getting task list from workflow system - SQL Exception generated by embedded code: ERROR: syntax error at or near "class"
  Position: 17
Mon Oct 07 14:21:52: ERROR Wrapped com.ebasetech.xi.exceptions.FormRuntimeException: Error getting task list from workflow system - SQL Exception generated by embedded code: ERROR: syntax error at or near "class"
  Position: 17 (WF_FETCH_TASKLIST_JS#7)
com.ebasetech.ufs.runtime.events.rhinojavascript.RhinoScriptException: Wrapped com.ebasetech.xi.exceptions.FormRuntimeException: Error getting task list from workflow system - SQL Exception generated by embedded code: ERROR: syntax error at or near "class"
  Position: 17 (WF_FETCH_TASKLIST_JS#7)
	at com.ebasetech.ufs.runtime.events.rhinojavascript.JavascriptExecutionHandler.handleExecutionErrors(JavascriptExecutionHandler.java:355)
	at com.ebasetech.ufs.runtime.events.rhinojavascript.JavascriptExecutionHandler.execute(JavascriptExecutionHandler.java:280)
	at com.ebasetech.ufs.runtime.events.JVMScriptNode.execute_(JVMScriptNode.java:133)
	at com.ebasetech.ufs.runtime.events.JVMScriptNode.execute(JVMScriptNode.java:99)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.events.SuspendableExecutable.execute(SuspendableExecutable.java:47)
	at com.ebasetech.ufs.runtime.events.SuspendableContainerExecutable.execute(SuspendableContainerExecutable.java:26)
	at com.ebasetech.ufs.runtime.events.BaseEventNode.execute(BaseEventNode.java:32)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.events.SuspendableExecutable.execute(SuspendableExecutable.java:47)
	at com.ebasetech.ufs.runtime.events.SuspendableContainerExecutable.execute(SuspendableContainerExecutable.java:26)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.events.SuspendableExecutable.execute(SuspendableExecutable.java:47)
	at com.ebasetech.ufs.runtime.events.SuspendableContainerExecutable.execute(SuspendableContainerExecutable.java:26)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.events.SuspendableExecutable.execute(SuspendableExecutable.java:47)
	at com.ebasetech.ufs.runtime.events.SuspendableContainerExecutable.execute(SuspendableContainerExecutable.java:26)
	at com.ebasetech.ufs.runtime.events.RunnableContext.execute(RunnableContext.java:69)
	at com.ebasetech.ufs.runtime.Request.execute(Request.java:448)
	at com.ebasetech.ufs.runtime.Request.execute(Request.java:417)
	at com.ebasetech.ufs.runtime.Request._execute(Request.java:352)
	at com.ebasetech.ufs.runtime.Request.execute(Request.java:317)
	at formservlets.FormProcessingUtil.executeRequest(FormProcessingUtil.java:503)
	at formservlets.FormProcessor.performExecution(FormProcessor.java:162)
	at formservlets.FormProcessor.processRequest(FormProcessor.java:149)
	at formservlets.FormProcessor.runForm(FormProcessor.java:46)
	at formservlets.FormClient.process(FormClient.java:118)
	at formservlets.FormClient.doGet(FormClient.java:48)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at formservlets.EbaseFilter.processHtmlRequest(EbaseFilter.java:355)
	at formservlets.EbaseFilter.processRequest(EbaseFilter.java:258)
	at formservlets.EbaseFilter.performFilter(EbaseFilter.java:180)
	at formservlets.EbaseFilter.doFilter(EbaseFilter.java:100)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:198)
	at net.bull.javamelody.MonitoringFilter.doFilter(MonitoringFilter.java:176)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:203)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518)
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:673)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1526)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1482)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:745)
Caused by: com.ebasetech.xi.exceptions.FormRuntimeException: Error getting task list from workflow system - SQL Exception generated by embedded code: ERROR: syntax error at or near "class"
  Position: 17
	at com.ebasetech.ufs.runtime.external.api.impl.Exceptions.makeFormRuntimeException(Exceptions.java:23)
	at com.ebasetech.ufs.runtime.external.api.impl.ApiTableBase.fetchTable(ApiTableBase.java:130)
	at com.ebasetech.ufs.runtime.external.api.impl.ApiTableBase.fetchTable(ApiTableBase.java:118)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:126)
	at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:225)
	at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:1479)
	at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:815)
	at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:109)
	at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:393)
	at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3280)
	at org.mozilla.javascript.Context.callFunctionWithContinuations(Context.java:1284)
	at org.mozilla.javascript.Context.executeScriptWithContinuations(Context.java:1251)
	at com.ebasetech.ufs.runtime.events.rhinojavascript.JavascriptExecutionHandler.execute(JavascriptExecutionHandler.java:270)
	... 58 more
0 x

Online
Jon
Moderator
Moderator
Posts: 1289
Joined: Wed Sep 12, 2007 12:49 pm

Re: Using PostgreSQL for UFS repository DB

#2

Postby Jon » Tue Oct 08, 2019 10:45 am

This is a known problem with Postgres caused by the fact that "class" is a reserved word with Postgres. I think it was fixed in V5.3.
0 x

ericb
Ebase User
Posts: 71
Joined: Fri Jan 15, 2016 2:34 pm

Re: Using PostgreSQL for UFS repository DB

#3

Postby ericb » Tue Oct 08, 2019 1:45 pm

Thanks Jon, I'll do an upgrade and see if that resolves it.
0 x


Who is online

Users browsing this forum: Jon and 3 guests