Boundary Timer Event throwing SQLException - Invalid column

[ct1]

Sirs:

While trying to use a bounday timer event, I am getting "SQLException: Invalid column name 'TRUE'."

I copied the loanrequest.bpmn20.xml file from the Activiti In Action source code into a project in Eclipse (Indigo Release 2).  I also put the Java program needed by the service task in the loanrequest process in a jar file on the Explorer classpath.  The timer is set to escalate the process from one user task to another user task if the first user task is not completed within one minute. 

I deployed it via the Activiti Explorer (5.9) on a Windows 7 PC.  I signed on to Activiti Explorer as fozzie, as instructed, and started the process.

That the process starts is noted in the log file.  The row created in the ACT_RU_JOB table is also shown below.  It is still there even after the log shows the stack trace and I stop tomcat to stop the cycle of exceptions. 

At the time the boundary timer event should escalate (cancel one user process and start another), the exception shown below is thrown.  Tomcat continues to throw this exception repeatedly until I either stop tomcat or delete the deployment.

Would you please have time to point out what I have done incorrectly?

Here is my copy of the loanrequest.bpmn20.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<definitions id="taskAssigneeExample"
     xmlns="http://www.omg.org/spec/BPMN/20100524/MODEL"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:activiti="http://activiti.org/bpmn"
   targetNamespace="http://www.bpmnwithactiviti.org/loanrequest">
   
  <process id="loanrequest" name="Process to handle a loan request">
    <startEvent id="theStart">
      <extensionElements>
        <activiti:formProperty id="name" name="Name" required="true" type="string" />
        <activiti:formProperty id="emailAddress" name="Email address" required="true" type="string" />
        <activiti:formProperty id="income" name="Income" required="true" type="long" />
        <activiti:formProperty id="loanAmount" name="Loan amount" required="true" type="long" />
      </extensionElements>
    </startEvent>
    <sequenceFlow sourceRef="theStart" targetRef="checkCredit" />
      <scriptTask id="checkCredit" scriptFormat="groovy">
         <script>
            out:println "Checking credit for " + name;
            creditCheckOk = false;
            if((income / 2) > loanAmount){
               creditCheckOk = true;
            }
            out:println "Checked credit for " + name + " outcome is " + creditCheckOk;
         </script>
    </scriptTask>
    <sequenceFlow sourceRef="checkCredit" targetRef="createApplication" />
    <serviceTask id="createApplication"   activiti:class="org.bpmnwithactiviti.chapter5.CreateApplicationTask" />
    <sequenceFlow sourceRef="createApplication" targetRef="evaluateLoanRequest" />
    <userTask id="evaluateLoanRequest" name="Evaluate loan request" activiti:assignee="fozzie">
      <extensionElements>
        <activiti:formProperty id="customerName" name="Customer name" expression="${loanApplication.customerName}" writable="false"/>
        <activiti:formProperty id="income" name="Income of customer" expression="${loanApplication.income}" writable="false"/>
        <activiti:formProperty id="requestedAmount" name="Requested loan amount" expression="${loanApplication.requestedAmount}" writable="false"/>
        <activiti:formProperty id="creditCheckOk" name="Outcome of credit check" expression="${loanApplication.creditCheckOk}" writable="false"/>
        <activiti:formProperty id="requestApproved" name="Do you approve the request?" required="true" type="enum">
          <activiti:value id="true" name="Yes"/>
          <activiti:value id="false" name="No"/>
        </activiti:formProperty>
        <activiti:formProperty id="motivation" name="Motivation"/>
      </extensionElements>
    </userTask>
    <sequenceFlow sourceRef="evaluateLoanRequest" targetRef="approvalGateway" />
    <boundaryEvent id="escalationTimer" cancelActivity="true" attachedToRef="evaluateLoanRequest">
      <timerEventDefinition>
        <timeDuration>PT1M</timeDuration>
      </timerEventDefinition>
    </boundaryEvent>
    <sequenceFlow sourceRef="escalationTimer" targetRef="evaluateRequestByManager" />
    <userTask id="evaluateRequestByManager" name="Evaluate loan request by manager" activiti:candidateGroups="management">
      <extensionElements>
        <activiti:formProperty id="customerName" name="Customer name" expression="${loanApplication.customerName}" writable="false"/>
        <activiti:formProperty id="income" name="Income of customer" expression="${loanApplication.income}" writable="false"/>
        <activiti:formProperty id="requestedAmount" name="Requested loan amount" expression="${loanApplication.requestedAmount}" writable="false"/>
        <activiti:formProperty id="creditCheckOk" name="Outcome of credit check" expression="${loanApplication.creditCheckOk}" writable="false"/>
        <activiti:formProperty id="requestApproved" name="Do you approve the request?" required="true" type="enum">
          <activiti:value id="true" name="Yes"/>
          <activiti:value id="false" name="No"/>
        </activiti:formProperty>
        <activiti:formProperty id="motivation" name="Motivation"/>
      </extensionElements>
    </userTask>
    <sequenceFlow sourceRef="evaluateRequestByManager" targetRef="approvalGateway" />
    <exclusiveGateway id="approvalGateway" />
    <sequenceFlow sourceRef="approvalGateway" targetRef="informCustomer">
      <conditionExpression xsi:type="tFormalExpression">${requestApproved == false}</conditionExpression>
    </sequenceFlow>
    <sequenceFlow sourceRef="approvalGateway" targetRef="processRequest">
        <conditionExpression xsi:type="tFormalExpression">${requestApproved == true}</conditionExpression>
    </sequenceFlow>
    <serviceTask id="informCustomer" activiti:type="mail">
      <extensionElements>
        <activiti:field name="to" expression="${loanApplication.emailAddres}" />
        <activiti:field name="subject" stringValue="Loan Request Denied" />
        <activiti:field name="html">
          <activiti:expression>
            <![CDATA[
              <html>
                <body>
                  Hello ${loanApplication.customerName},<br/><br/>
                  
                  Your loan request has been denied for the following reason: ${motivation}.<br/><br/>
                  
                  Kind regards,<br/>
                  
                  The Loan Sharks Company.
                </body>
              </html>
            ]]>
          </activiti:expression>
        </activiti:field>      
      </extensionElements>
    </serviceTask>
    <sequenceFlow sourceRef="informCustomer" targetRef="theEnd" />
    <userTask id="processRequest" name="Process the loan request" activiti:assignee="fozzie" />
    <sequenceFlow sourceRef="processRequest" targetRef="theEnd" />
    <endEvent id="theEnd" />
  </process>
</definitions>

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This is the exception that is thrown:

INFO: Server startup in 18183 ms
May 30, 2012 9:14:29 AM org.activiti.explorer.cache.TrieBasedUserCache loadUsers
INFO: Caching users 0 to 25
May 30, 2012 9:15:25 AM org.activiti.engine.impl.bpmn.deployer.BpmnDeployer deploy
INFO: Processing resource loanrequest.bpmn20.xml
May 30, 2012 9:15:25 AM org.activiti.engine.impl.bpmn.parser.BpmnParse parseDefinitionsAttributes
INFO: XMLSchema currently not supported as typeLanguage
May 30, 2012 9:15:25 AM org.activiti.engine.impl.bpmn.parser.BpmnParse parseDefinitionsAttributes
INFO: XPath currently not supported as expressionLanguage
Checking credit for Charles Thomas                       <———————————–loan request process started
Checked credit for Charles Thomas outcome is true
name Charles Thomas
May 30, 2012 9:17:06 AM org.activiti.engine.impl.interceptor.CommandContext close   <—— Timer should escalate process.
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: java.sql.SQLException: Invalid column name 'TRUE'.    
### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectExclusiveJobsT
oExecute-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: Invalid column name 'TRUE'.
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:81
)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:73
)
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:182)
        at org.activiti.engine.impl.persistence.entity.JobManager.findExclusiveJobsToExecute(JobMana
ger.java:123)
        at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:62)
        at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:33)
        at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java
:24)
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInte
rceptor.java:42)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
        at org.activiti.engine.impl.jobexecutor.AcquireJobsRunnable.run(AcquireJobsRunnable.java:57)

        at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: Invalid column name 'TRUE'.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
        at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:558)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHand
ler.java:39)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandle
r.java:55)
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:243)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:117)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:72)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:79
)
        … 10 more
May 30, 2012 9:17:06 AM org.activiti.engine.impl.interceptor.CommandContext close
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: java.sql.SQLException: Invalid column name 'TRUE'.
### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.selectExclusiveJobsT
oExecute-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: Invalid column name 'TRUE'.
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:81
)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:73
)
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:182)
        at org.activiti.engine.impl.persistence.entity.JobManager.findExclusiveJobsToExecute(JobMana
ger.java:123)
        at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:62)
        at org.activiti.engine.impl.cmd.AcquireJobsCmd.execute(AcquireJobsCmd.java:33)
        at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java
:24)
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInte
rceptor.java:42)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
        at org.activiti.engine.impl.jobexecutor.AcquireJobsRunnable.run(AcquireJobsRunnable.java:57)

        at java.lang.Thread.run(Thread.java:662)
Caused by: java.sql.SQLException: Invalid column name 'TRUE'.
        at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
        at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
        at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
        at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
        at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
        at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:558)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHand
ler.java:39)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandle
r.java:55)
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:41)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:243)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:117)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:72)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:79
)
        … 10 more
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This is what the row in the ACT_RU_JOB table contains:

908725   1   timer   NULL   NULL   1   908724   908710   3   NULL   NULL   2012-05-30 09:17:06.320   NULL   timer-transition   escalationTimer

[ct1]

Sirs:

Before you ask whether the Job Executor is turned on, here is a copy of the activiti.cfg.xml file on the classpath:


<?xml version="1.0" encoding="UTF-8"?>
 
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans   http://www.springframework.org/schema/beans/spring-beans.xsd">

  <bean id="processEngineConfiguration" class="org.activiti.engine.impl.cfg.StandaloneProcessEngineConfiguration">
    <property name="databaseSchemaUpdate" value="true"/>
   
    <!– Use these values for LUSR0989 –>
<!– <property name="jdbcUrl" value="jdbc:jtds:sqlserver://LUSR0989:1433/Workflow_DB1" />
    <property name="jdbcDriver" value="net.sourceforge.jtds.jdbc.Driver"/>
    <property name="jdbcUsername" value="yyyyyyyyyy" />
    <property name="jdbcPassword" value="xxxxxxxxxx" />
–>

    <!– Use these values for LUSR1506 –>
<!– <property name="jdbcUrl" value="jdbc:jtds:sqlserver://LUSR1506:1433/Workflow_DB1" />
    <property name="jdbcDriver" value="net.sourceforge.jtds.jdbc.Driver"/>
    <property name="jdbcUsername" value="yyyyyyyyyy" />
    <property name="jdbcPassword" value="xxxxxxxxxx" />
–>
   
    <!– Use these values for LUSR1315 –>
<property name="jdbcUrl" value="jdbc:jtds:sqlserver://LUSR1315:1433/Activiti_DB" />
    <property name="jdbcDriver" value="net.sourceforge.jtds.jdbc.Driver"/>
    <property name="jdbcUsername" value="yyyyyyyyyy" />
    <property name="jdbcPassword" value="xxxxxxxxxx" />

    <property name="jobExecutorActivate" value="true" />
  </bean>
 
</beans>


Thank you.

[trademak]

It looks like the wrong select exclusive query is executed and MS SQL Server doesn't understand it.
Can you get the query string that's being executed against MS SQL Server?

Best regards,

[ct1]

Thank you for your reply.

I stumbled around in SQL Server Management Studio to try to find a way to log the SQL statement sent just prior to the exception noted in my previous post.

I think this is the statement (please let me know if I found the wrong SQL statement):


(@P0 datetime,@P1 datetime,@P2 nvarchar(4000))select *
    from ACT_RU_JOB    
    where (RETRIES_ > 0)
      and (DUEDATE_ is null or DUEDATE_ <  @P0 )
      and (LOCK_OWNER_ is null or LOCK_EXP_TIME_ <  @P1 )
      and (RETRIES_ > 0)
      and (EXCLUSIVE_ = TRUE)
      and (PROCESS_INSTANCE_ID_ =  @P2 )

The row in the ACT_RU_JOB table and the activiti.cfg.xml file on the classpath are shown in my previous post.

Here is the db.properties file:


db=mssql
jdbc.driver=net.sourceforge.jtds.jdbc.Driver
jdbc.url=jdbc\:jtds\:sqlserver\://LUSR1315\:1433/Activiti_DB
jdbc.username=yyyyyyyyyyyy
jdbc.password=xxxxxxxxxxxx

Here is the DDL used to create this table:


create table ACT_RU_JOB (
    ID_ nvarchar(64) NOT NULL,
REV_ int,
    TYPE_ nvarchar(255) NOT NULL,
    LOCK_EXP_TIME_ datetime,
    LOCK_OWNER_ nvarchar(255),
    EXCLUSIVE_ bit,
    EXECUTION_ID_ nvarchar(64),
    PROCESS_INSTANCE_ID_ nvarchar(64),
    RETRIES_ int,
    EXCEPTION_STACK_ID_ nvarchar(64),
    EXCEPTION_MSG_ nvarchar(4000),
    DUEDATE_ datetime NULL,
    REPEAT_ nvarchar(255),
    HANDLER_TYPE_ nvarchar(255),
    HANDLER_CFG_ nvarchar(4000),
    primary key (ID_)
);

When I first saw the exception, I also thought that a query that was using a different schema must have been executed, but I thought that which queries are used is determined by the definition of the database in the configuration and the properties files.  As far as I can tell, these point to the MS SQL database.

What could trigger the execution of the wrong select exclusive query with this activiti.xml.cfg and this db.properties file?

Thank you again.

[frederikherema1]

As far as my MSSQL knowledge goos, MSSQL doesn't have a Boolean type, so I doubt the 'TRUE' literal will exist.

What version of MSSQL-server are you using (we test using 2008 using JDBC jtds-1.2.4)?
Can you try setting the 'databaseType' parameter in your configuration manually to 'mssql' to be sure the right db-type is used?

[uhn]

Hello,

maybe it helps. We have the same problem with Activiti 5.9 and MSSQL-Database Version 10.50.1617.0. We use the MS jdbc driver in sqljdbc4.jar.

With Activiti 5.8 everthing works fine.

Kind regards

[ct1]

Thank you for your response.  I was finally able to return to this problem and try your suggestion.
I changed the activiti.cfg.xml to explicitly define the databaseType rather than relying on the automatic detection.

The problem of the SQL Exception - Invalid Column Name went away.

Again, thank you for your help.