AnsweredAssumed Answered

Process Instance Query returning completed or cancelled workflows (MS SQL)

Question asked by cchen on May 16, 2016
Latest reply on May 26, 2016 by jbarrez
I am trying to use a ProcessInstanceQuery to return all active workflows initiated by bpm_initiator. However, a number of inactive workflows are getting returned. When these inactive workflows are returned, exceptions are thrown, since bpm_package has been deleted. When querying ACT_RU_VARIABLE for the bpm_package values that are causing the exceptions, there are no process instances returned, implying that there are no active workflows for those bpm_package values.

Activiti Engine 5.16.4
MS SQL 2012 SP1

We are using the following Activiti API:

ProcessEngine pe = ProcessEngines.getDefaultProcessEngine();
pe.getIdentityService().setAuthenticatedUserId( userLoginName );
List<ProcessInstance> processes = pe.getRuntimeService().createProcessInstanceQuery().variableValueEquals("bpm_initiator", userLoginName).list();

Logging has been turned on for: to obtain the SQL Query. Executing the logged query in MS SQL Server Management Studio does not produce workflows that correspond to any of the completed/cancelled workflows with deleted node refs.

Due to the additional logging, this is the query we think gets executed, but does not seem to return the cancelled workflows that are causing our errors:

SELECT SUB.* FROM ( select distinct RES.* , row_number() over (ORDER BY RES.ID_ asc) rnk FROM ( select distinct RES.* , P.KEY_ as ProcessDefinitionKey, P.ID_ as ProcessDefinitionId, P.NAME_ as ProcessDefinitionName, P.VERSION_ as ProcessDefinitionVersion, P.DEPLOYMENT_ID_ as DeploymentId from ACT_RU_EXECUTION RES inner join ACT_RE_PROCDEF P on RES.PROC_DEF_ID_ = P.ID_ inner join ACT_RU_VARIABLE A0 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_ WHERE RES.PARENT_ID_ is null and A0.EXECUTION_ID_ = A0.PROC_INST_ID_ and A0.NAME_= ? and A0.TYPE_ = ? and A0.TEXT_ = ? )RES ) SUB WHERE SUB.rnk >= ? AND SUB.rnk < ?

Please advise. Thank you.