AnsweredAssumed Answered

Cannot get ProcessInstance with both name and variables on MSSQL

Question asked by fucida on Dec 23, 2014
I found a problem with getting values using RuntimeService on MSSQL database. I use following command:

ProcessInstance process = execution
   .getEngineServices()
   .getRuntimeService()
   .createProcessInstanceQuery()
   .processInstanceId(processId)
   .includeProcessVariables()
   .singleResult();

I need get both name of process instance (saved in column NAME_) and map of process variables.
If I run this command on Oracle, it's ok. But if I run the command on MSSQL Server, value of field "name" in variable "process" is null. The difference is in created and used SQL commands.
On Oracle, the created SQL looks like this:

select *
from (
      select a.*,
             ROWNUM rnum
      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,
                   VAR.ID_ as VAR_ID_,
                   VAR.NAME_ as VAR_NAME_,
                   VAR.TYPE_ as VAR_TYPE_,
                   VAR.REV_ as VAR_REV_,
                   VAR.PROC_INST_ID_ as VAR_PROC_INST_ID_,
                   VAR.EXECUTION_ID_ as VAR_EXECUTION_ID_,
                   VAR.TASK_ID_ as VAR_TASK_ID_,
                   VAR.BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_,
                   VAR.DOUBLE_ as VAR_DOUBLE_,
                   VAR.TEXT_ as VAR_TEXT_,
                   VAR.TEXT2_ as VAR_TEXT2_,
                   VAR.LONG_ as VAR_LONG_
            from igor.ACT_RU_EXECUTION RES
                 inner join igor.ACT_RE_PROCDEF P
                    on RES.PROC_DEF_ID_ = P.ID_
                 left outer join igor.ACT_RU_VARIABLE VAR
                    ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_
                   and VAR.TASK_ID_ is null
            WHERE RES.PARENT_ID_ is null
              and RES.ID_ = ?
              and RES.PROC_INST_ID_ = ?
            order by RES.ID_ asc
        ) a
  where ROWNUM < ?)
where rnum >= ?

All columns from ACR_RU_EXECUTION are returned.
On MSSQL looks this:

SELECT SUB.*
FROM (
      select distinct TEMPRES_ID_ as ID_,
             TEMPP_KEY_ as ProcessDefinitionKey,
             TEMPP_ID_ as ProcessDefinitionId,
             TEMPP_NAME_ as ProcessDefinitionName,
             TEMPP_VERSION_ as ProcessDefinitionVersion,
             TEMPP_DEPLOYMENT_ID_ as DeploymentId,
             TEMPRES_REV_ as REV_,
             TEMPRES_ACT_ID_ as ACT_ID_,
             TEMPRES_BUSINESS_KEY_ as BUSINESS_KEY_,
             TEMPRES_IS_ACTIVE_ as IS_ACTIVE_,
             TEMPRES_IS_CONCURRENT_ as IS_CONCURRENT_,
             TEMPRES_IS_SCOPE_ as IS_SCOPE_,
             TEMPRES_IS_EVENT_SCOPE_ as IS_EVENT_SCOPE_,
             TEMPRES_PARENT_ID_ as PARENT_ID_,
             TEMPRES_PROC_INST_ID_ as PROC_INST_ID_,
             TEMPRES_SUPER_EXEC_ as SUPER_EXEC_,
             TEMPRES_SUSPENSION_STATE_ as SUSPENSION_STATE_,
             TEMPRES_CACHED_ENT_STATE_ as CACHED_ENT_STATE_,
             TEMPVAR_ID_ as VAR_ID_,
             TEMPVAR_NAME_ as VAR_NAME_,
             TEMPVAR_TYPE_ as VAR_TYPE_,
             TEMPVAR_REV_ as VAR_REV_,
             TEMPVAR_PROC_INST_ID_ as VAR_PROC_INST_ID_,
             TEMPVAR_EXECUTION_ID_ as VAR_EXECUTION_ID_,
             TEMPVAR_TASK_ID_ as VAR_TASK_ID_,
             TEMPVAR_BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_,
             TEMPVAR_DOUBLE_ as VAR_DOUBLE_,
             TEMPVAR_TEXT_ as VAR_TEXT_,
             TEMPVAR_TEXT2_ as VAR_TEXT2_,
             TEMPVAR_LONG_ as VAR_LONG_ ,
             row_number() over (ORDER BY TEMPRES_ID_ asc) rnk
       FROM (
             select distinct RES.ID_ as TEMPRES_ID_,
                    RES.REV_ as TEMPRES_REV_,
                    P.KEY_ as TEMPP_KEY_,
                    P.ID_ as TEMPP_ID_,
                    P.NAME_ as TEMPP_NAME_,
                    P.VERSION_ as TEMPP_VERSION_,
                    P.DEPLOYMENT_ID_ as TEMPP_DEPLOYMENT_ID_,
                    RES.ACT_ID_ as TEMPRES_ACT_ID_,
                    RES.PROC_INST_ID_ as TEMPRES_PROC_INST_ID_,
                    RES.BUSINESS_KEY_ as TEMPRES_BUSINESS_KEY_,
                    RES.IS_ACTIVE_ as TEMPRES_IS_ACTIVE_,
                    RES.IS_CONCURRENT_ as TEMPRES_IS_CONCURRENT_,
                    RES.IS_SCOPE_ as TEMPRES_IS_SCOPE_,
                    RES.IS_EVENT_SCOPE_ as TEMPRES_IS_EVENT_SCOPE_,
                    RES.PARENT_ID_ as TEMPRES_PARENT_ID_,
                    RES.SUPER_EXEC_ as TEMPRES_SUPER_EXEC_,
                    RES.SUSPENSION_STATE_ as TEMPRES_SUSPENSION_STATE_,
                    RES.CACHED_ENT_STATE_ as TEMPRES_CACHED_ENT_STATE_,
                    VAR.ID_ as TEMPVAR_ID_,
                    VAR.NAME_ as TEMPVAR_NAME_,
                    VAR.TYPE_ as TEMPVAR_TYPE_,
                    VAR.REV_ as TEMPVAR_REV_,
                    VAR.PROC_INST_ID_ as TEMPVAR_PROC_INST_ID_,
                    VAR.EXECUTION_ID_ as TEMPVAR_EXECUTION_ID_,
                    VAR.TASK_ID_ as TEMPVAR_TASK_ID_,
                    VAR.BYTEARRAY_ID_ as TEMPVAR_BYTEARRAY_ID_,
                    VAR.DOUBLE_ as TEMPVAR_DOUBLE_,
                    VAR.TEXT_ as TEMPVAR_TEXT_,
                    VAR.TEXT2_ as TEMPVAR_TEXT2_,
                    VAR.LONG_ as TEMPVAR_LONG_
             from igor.ACT_RU_EXECUTION RES
                  inner join igor.ACT_RE_PROCDEF P
                     on RES.PROC_DEF_ID_ = P.ID_
                  left outer join igor.ACT_RU_VARIABLE VAR
                     ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_ and VAR.TASK_ID_ is null
             WHERE RES.PARENT_ID_ is null
               and RES.ID_ = ?
               and RES.PROC_INST_ID_ = ?
             )RES
     ) SUB
WHERE SUB.rnk >= ? AND SUB.rnk < ?

Columns TENNANT_ID_ and NAME_ are not fetched. When I run the upper java command without using "includeProcessVariables()", its also ok and all columns are returned (but without variables of course).
I didn't test it on another supported databases (PostreSQL, DB2 etc). I'am using Activiti version 5.16.3 now.
Is that bug or do I something wrong?

Thank you.

Outcomes