7 Replies Latest reply on Sep 4, 2017 6:48 AM by mccloud

JBPM 6.4.0.Final not working properly with MS SqlServer 2012 - Error Incorrect syntax near 'limit'

franky051 Feb 6, 2017 2:41 PM

Hello,

I configured JBPM 6.4.0.Final version to use with MS SqlServer 2012. I was able to start the JBOSS server with jbpm and kie workbench deployed successfully. When I looked at the Jboss server.log I am getting the below Exception "[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 2) Incorrect syntax near 'limit'". The stacktrace is pasted below. I think its the limit keyword which is not supported by SqlServer. I used the correct dialect and driver and tested this by writing a sample Java program. When I investigated further I am getting this when JBPM executed the below query again the Database. I got this query from SqlServer profiler. Please let me know how I can resolve this issue.

Hibernate Dialect (Tried Both):

org.hibernate.dialect.SQLServer2012Dialect & org.hibernate.dialect.SQLServerDialect

Driver:

sqljdbc4-2.0.jar

Driver Class:

com.microsoft.sqlserver.jdbc.SQLServerDriver

xa-datasource-class

com.microsoft.sqlserver.jdbc.SQLServerXADataSource

Query Causing the problem:

select requestinf0_.id as id1_31_, requestinf0_.commandName as commandN2_31_, requestinf0_.deploymentId as deployme3_31_, requestinf0_.executions as executio4_31_,

requestinf0_.businessKey as business5_31_, requestinf0_.message as message6_31_, requestinf0_.owner as owner7_31_, requestinf0_.requestData as requestD8_31_,

requestinf0_.responseData as response9_31_, requestinf0_.retries as retries10_31_, requestinf0_.status as status11_31_, requestinf0_.timestamp as timesta12_31_

from RequestInfo requestinf0_ where (requestinf0_.status='QUEUED' or requestinf0_.status='RETRYING') and requestinf0_.timestamp<@P0

and (requestinf0_.owner=@P1 or requestinf0_.owner is null) limit @P2 for update

Exception StackTrace:

2017-02-02 13:26:27,190 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (EJB default - 2) Incorrect syntax near 'limit'.

2017-02-02 13:26:27,206 WARN [org.jbpm.shared.services.impl.TransactionalCommandService] (EJB default - 2) Could not commit session: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387) [hibernate-entitymanager-4.2.21.Final.jar:4.2.21.Final]

at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310) [hibernate-entitymanager-4.2.21.Final.jar:4.2.21.Final]

at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:316) [hibernate-entitymanager-4.2.21.Final.jar:4.2.21.Final]

at org.jbpm.shared.services.impl.JpaPersistenceContext.queryStringWithParameters(JpaPersistenceContext.java:188) [jbpm-shared-services-6.4.0.Final.jar:6.4.0.Final]

at org.jbpm.shared.services.impl.JpaPersistenceContext.queryAndLockWithParametersInTransaction(JpaPersistenceContext.java:65) [jbpm-shared-services-6.4.0.Final.jar:6.4.0.Final]

at org.jbpm.executor.impl.jpa.ExecutorQueryServiceImpl$LockAndUpdateRequestInfoCommand.execute(ExecutorQueryServiceImpl.java:238) [jbpm-executor-6.4.0.Final.jar:6.4.0.Final]

at org.jbpm.executor.impl.jpa.ExecutorQueryServiceImpl$LockAndUpdateRequestInfoCommand.execute(ExecutorQueryServiceImpl.java:224) [jbpm-executor-6.4.0.Final.jar:6.4.0.Final]

at org.jbpm.shared.services.impl.TransactionalCommandService.execute(TransactionalCommandService.java:71) [jbpm-shared-services-6.4.0.Final.jar:6.4.0.Final]

at org.jbpm.executor.impl.jpa.ExecutorQueryServiceImpl.getRequestForProcessing(ExecutorQueryServiceImpl.java:211) [jbpm-executor-6.4.0.Final.jar:6.4.0.Final]

at org.jbpm.executor.impl.AvailableJobsExecutor.executeJob(AvailableJobsExecutor.java:36) [jbpm-executor-6.4.0.Final.jar:6.4.0.Final]

at org.kie.server.jms.executor.PollExecutorBean.executeJob(PollExecutorBean.java:84) [kie-server-jms-6.4.0.Final.jar:6.4.0.Final]

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_111]

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_111]

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_111]

at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_111]

at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52)