Activiti initial fail when deploy war to JBoss 6.2

[huntter]

I'm trying to deploy an activiti based bpm application to JBoss 6.2, but unfortunately when start JBoss, it is failed due to the sql statement

13:50:35,867 INFO  [stdout] (Thread-94) ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'where'.
13:50:35,867 INFO  [stdout] (Thread-94) org.apache.ibatis.exceptions.PersistenceException:
13:50:35,867 INFO  [stdout] (Thread-94) ### Error querying database.  Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'where'.
13:50:35,868 INFO  [stdout] (Thread-94) ### The error may exist in org/activiti/db/mapping/entity/Job.xml
13:50:35,868 INFO  [stdout] (Thread-94) ### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectNextJobsToExecute-Inline
13:50:35,868 INFO  [stdout] (Thread-94) ### The error occurred while setting parameters
13:50:35,868 INFO  [stdout] (Thread-94) ### SQL: select * from ( select a.*, ROWNUM rnum from (     select       RES.*            from ACT_RU_JOB RES          LEFT OUTER JOIN ACT_RU_EXECUTION PI ON PI.ID_ = RES.PROCESS_INSTANCE_ID_     where (RES.RETRIES_ > 0)  and (RES.DUEDATE_ is null or RES.DUEDATE_ <= ?)       and (RES.LOCK_OWNER_ is null or RES.LOCK_EXP_TIME_ <= ?)    and (         (RES.EXECUTION_ID_ is null)      or       (PI.SUSPENSION_STATE_ = 1)       )         ) a where ROWNUM < ?) where rnum  >= ?
13:50:35,869 INFO  [stdout] (Thread-94) ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'where'.
13:50:35,870 INFO  [stdout] (Thread-94)         at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23) ~[mybatis-3.2.5.jar:3.2.5]
13:50:35,870 INFO  [stdout] (Thread-94)         at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:107) ~[mybatis-3.2.5.jar:3.2.5]
13:50:35,870 INFO  [stdout] (Thread-94)         at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98) ~[mybatis-3.2.5.jar:3.2.5]
13:50:35,871 INFO  [stdout] (Thread-94)         at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:416) ~[activiti-engine-5.16.1.jar:5.16.1]
13:50:35,871 INFO  [stdout] (Thread-94)         at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:407) ~[activiti-engine-5.16.1.jar:5.16.1]
13:50:35,871 INFO  [stdout] (Thread-94)         at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:402) ~[activiti-engine-5.16.1.jar:5.16.1]
13:50:35,872 INFO  [stdout] (Thread-94)         at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:389) ~[activiti-engine-5.16.1.jar:5.16.1]
13:50:35,872 INFO  [stdout] (Thread-94)         at org.activiti.engine.impl.persistence.entity.JobEntityManager.findNextJobsToExecute(JobEntityManager.java:97) ~[activiti-engine-5.16.1.jar:5.16.1]
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

And this application running well in tomcat 7.

[huntter]

And from the activiti guide,

databaseType: it’s normally not necessary to specify this property as it is automatically analyzed from the database connection meta data. Should only be specified in case automatic detection fails. Possible values: {h2, mysql, oracle, postgres, mssql, db2}. This property is required when not using the default H2 database. This setting will determine which create/drop scripts and queries will be used. See the supported databases section for an overview of which types are supported.

It is mentioned the databaseType configuration, In general it is automatially analyzed from the database connection meta data, and I have tried to give the dedicate value mssql in the activiti configuration. but still encounter the same issue when startup.

[huntter]

I'm using the JBoss datasource, maybe this is caused by the activiti cannot parse the datasouce and get the real dataType.

[jbarrez]

> I'm using the JBoss datasource, maybe this is caused by the activiti cannot parse the datasouce and get the real dataType.

Hmm could be it, althought the metadata returned should be the same. Did you try setting the db type?

[huntter]

Yes, I set the databaseType to mssql in the spring confgiration.
<code><property name="databaseType" value="mssql" /></code>

And I have tried with a simple jsp file, it can return the correct database type from the JBoss datasource.

<code>
<%@ page import="javax.naming.InitialContext" %>
<%@ page import="javax.sql.DataSource" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DatabaseMetaData" %>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
         pageEncoding="ISO-8859-1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
    <title>Insert title here</title>
</head>
<body>
<%
    InitialContext context = new InitialContext();
    DataSource dataSource = (DataSource) context.lookup("jdbc/activitiDB");
    Connection connection = dataSource.getConnection();

    DatabaseMetaData metaData = connection.getMetaData();
    String databaseProductName = metaData.getDatabaseProductName();

    out.print(databaseProductName);
%>
</body>
</html>
</code>

[trademak]

Hi,

Are you using the same DB driver in JBoss and Tomcat 7?

Best regards,

[huntter]

Really sorry about it. now I have found the root cause, this is not activiti issue or JBoss issue, in our application, they defined another datasource which only used for the data migration, and I didn,t update the databaseType to mssql consistently.

Thanks guys for all your help.

[jbarrez]

No prob, thanks for posting back your solution.