topic Re: Boundary Timer Event throwing SQLException - Invalid column in Alfresco Archive

Boundary Timer Event throwing SQLException - Invalid column

ct1 — Wed, 30 May 2012 15:38:14 GMT

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 loanre

Re: Boundary Timer Event throwing SQLException - Invalid column

ct1 — Wed, 30 May 2012 21:30:12 GMT

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.

Re: Boundary Timer Event throwing SQLException - Invalid column

trademak — Fri, 01 Jun 2012 09:03:09 GMT

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,

Re: Boundary Timer Event throwing SQLException - Invalid column

ct1 — Mon, 04 Jun 2012 16:39:24 GMT

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.

Re: Boundary Timer Event throwing SQLException - Invalid column

frederikherema1 — Wed, 13 Jun 2012 07:37:14 GMT

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?

Re: Boundary Timer Event throwing SQLException - Invalid column

uhn — Mon, 18 Jun 2012 11:47:10 GMT

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

Re: Boundary Timer Event throwing SQLException - Invalid column

ct1 — Thu, 28 Jun 2012 17:56:25 GMT

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.