Uploaded image for project: 'Red Hat Process Automation Manager'
  1. Red Hat Process Automation Manager
  2. RHPAM-2993

Invalid column name 'process_inst_id' or 'activity_id' due to case sensitivity on ms sql server

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 7.8.1.GA
    • 7.7.1.GA
    • jBPM Core
    • None
    • 2020 Week 25-27 (from Jun 15), 2020 Week 28-30 (from Jul 6), 2020 Week 31-33 (from Jul 27)

    Description

      The following error occurs when using sql server 2016 with rhpam 7.7.1:
       
      ERROR [org.kie.server.remote.rest.jbpm.QueryDataResource] (default task-5) Unexpected error during processing Can't get metadata on specified data set: jbpmProcessInstances: org.dashbuilder.dataset.exception.DataSetLookupException: Can't get metadata on specified data set: jbpmProcessInstances
      at deployment.kie-server.war//org.dashbuilder.dataset.DataSetManagerImpl.getDataSetMetadata(DataSetManagerImpl.java:189)
      at deployment.kie-server.war//org.jbpm.kie.services.impl.query.QueryServiceImpl.replaceQuery(QueryServiceImpl.java:205)
      at deployment.kie-server.war//org.kie.server.services.jbpm.QueryDataServiceBase.replaceQuery(QueryDataServiceBase.java:98)
      at deployment.kie-server.war//org.kie.server.remote.rest.jbpm.QueryDataResource.replaceQueryDefinition(QueryDataResource.java:204)
      ...
      Caused by: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'process_inst_id'.
      at deployment.kie-server.war//org.dashbuilder.dataprovider.sql.model.Select.fetch(Select.java:197)
      at deployment.kie-server.war//org.dashbuilder.dataprovider.sql.dialect.SQLServerDialect.fetchColumns(SQLServerDialect.java:124)
      at deployment.kie-server.war//org.dashbuilder.dataprovider.sql.dialect.SQLServerDialect.getSQL(SQLServerDialect.java:109)
      at deployment.kie-server.war//org.dashbuilder.dataprovider.sql.model.Select.getSQL(Select.java:167)
      at deployment.kie-server.war//org.dashbuilder.dataprovider.sql.SQLDataSetProvider.logSQL(SQLDataSetProvider.java:601)
      at deployment.kie-server.war//org.dashbuilder.dataprovider.sql.SQLDataSetProvider._getColumns(SQLDataSetProvider.java:416)
      at deployment.kie-server.war//org.dashbuilder.dataprovider.sql.SQLDataSetProvider._getDataSetMetadata(SQLDataSetProvider.java:328)
      at deployment.kie-server.war//org.dashbuilder.dataprovider.sql.SQLDataSetProvider.getDataSetMetadata(SQLDataSetProvider.java:261)
      at deployment.kie-server.war//org.dashbuilder.dataset.DataSetManagerImpl.getDataSetMetadata(DataSetManagerImpl.java:187)
      ... 73 more
      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'process_inst_id'.
      at com.microsoft.sqlserver//com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
      ...
       
      By default, the sql server is case insensitive. If the case sensitivity is turned on, rhpam throws such exception during startup.
       
      The problematic sql query is something like:
       
      SELECT * FROM (select t.activationTime, t.actualOwner, t.createdBy, t.createdOn, t.deploymentId, t.description, t.dueDate, t.name, t.parentId, t.priority, t.processId, t.processInstanceId, t.processSessionId, t.status, t.taskId, t.workItemId, t.lastModificationDate, pil.correlationKey, pil.processInstanceDescription ,oe.id, nil.sla_due_date, nil.slaCompliance, (select COUNT(errInfo.id) from ExecutionErrorInfo errInfo where errInfo.activity_id = t.taskId and errInfo.process_inst_id = pil.processInstanceId and errInfo.error_ack = 0 and errInfo.error_type = 'Task') as errorCount from AuditTaskImpl t  left join ProcessInstanceLog pil on pil.processInstanceId = t.processInstanceId left join PeopleAssignments_BAs ba on t.taskId = ba.task_id left join OrganizationalEntity oe on ba.entity_id = oe.id left join NodeInstanceLog nil on nil.workItemId=t.workItemId) "dbSQL"
       
      You can see the join clause 
          errInfo.activity_id = t.taskId and errInfo.process_inst_id = pil.processInstanceId
      uses lower case.
       
      However the real column names in ExecutionErrorInfo are upper case 
        ACTIVITY_ID or PROCESS_INST_ID.
       
      Can we handle this in our code?  For example, make the columns all lower case?
       

      Attachments

        Issue Links

          Activity

            People

              xiabai@redhat.com Xiaofeng Bai
              rhn-support-ghu Gary Hu
              Antonio Fernandez Alhambra Antonio Fernandez Alhambra (Inactive)
              Antonio Fernandez Alhambra Antonio Fernandez Alhambra (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: