JR47309: ACCESSING PERFORMANCE QUERY REST API CAUSES A SQL SYNTAX ERROR (/REST/BPM/WLE/V1/PERFORMANCE/QUERY)

Download Version 8.0.1 Fix Pack 2 for the IBM Business Process Manager products
Version 8.5.0 Fix Pack 1 for the IBM Business Process Manager products
Version 8.5 Refresh Pack 5 for the IBM Business Process Manager products

APAR status

• Closed as program error.

Error description

• Accessing Performance Query through the REST API without any
  conditional filters set leads to appending of "AND ()" to the
  query and an SQL exception when the query is executed.
  
  This is when you dont use the condition parameter listed in the
  infocenter.
  http://pic.dhe.ibm.com/infocenter/dmndhelp/v8r0m1/topic/com.ibm.
  wbpm.ref.doc/rest/bpmrest/rest_bpm_wle_v1_performance_query_get.
  htm
  
  This error message can be seen. The SQL query part will include
  this invalid item: AND()
  
  com.lombardisoftware.core.TeamWorksException: StatementCallback;
  Caused by: org.springframework.jdbc.BadSqlGrammarException:
  StatementCallback; bad SQL grammar [long SQL query with AND ()]
  nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException:
  DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601,
  SQLERRMC=);-bccb3ea08131' AND (;<search_condition>,
  DRIVER=4.12.55
   at
  org.springframework.jdbc.support.SQLExceptionSubclassTranslator.
  translate(SQLExceptionSubclassTranslator.java:78)
  ...
  com.lombardisoftware.server.ejb.analysis.
  DataRetrievalServiceCore.executePerformanceDataSearch
  (DataRetrievalServiceCore.java:198)
   at
  com.lombardisoftware.server.ejb.analysis.
  DataRetrievalServiceCore.executePerformanceDataSearch
  (DataRetrievalServiceCore.java:185)
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:  Users of BPM Express, Standard, and         *
  *                  Advanced                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: Accessing Performance Query through     *
  *                      the                                     *
  *                      REST API without any                    *
  *                      conditional filters set leads to        *
  *                      appending of "AND ()" to the            *
  *                      query and an SQL exception when the     *
  *                      query is executed.                      *
  *                      This is when you don't use the          *
  *                      condition                               *
  *                      parameter listed in the                 *
  *                      infocenter.                             *
  *                      http://pic.dhe.ibm.com/infocenter/dmndh *
  *                      elp/v8r0m1/topic/com.ibm.               *
  *                      wbpm.ref.doc/rest/bpmrest/rest_bpm_wle_ *
  *                      v1_performance_query_get.               *
  *                      htm                                     *
  *                      This error message can be seen. The     *
  *                      SQL                                     *
  *                      query part will include                 *
  *                      this invalid item: AND()                *
  *                      com.lombardisoftware.core.TeamWorksExce *
  *                      ption: StatementCallback;               *
  *                      Caused by:                              *
  *                      org.springframework.jdbc.BadSqlGrammarE *
  *                      xception:                               *
  *                      StatementCallback; bad SQL grammar      *
  *                      [long SQL query with AND ()]            *
  *                      nested exception is                     *
  *                      com.ibm.db2.jcc.am.SqlSyntaxErrorExcept *
  *                      ion:                                    *
  *                      DB2 SQL Error: SQLCODE=-104,            *
  *                      SQLSTATE=42601,                         *
  *                      SQLERRMC=);-bccb3ea08131' AND (;        *
  *                      <search_condition>,                     *
  *                      DRIVER=4.12.55                          *
  *                      at                                      *
  *                      org.springframework.jdbc.support.SQLExc *
  *                      eptionSubclassTranslator.               *
  *                      translate(SQLExceptionSubclassTranslato *
  *                      r.java:78)                              *
  *                      ...                                     *
  *                      com.lombardisoftware.server.ejb.analysi *
  *                      s.                                      *
  *                      DataRetrievalServiceCore.executePerform *
  *                      anceDataSearch                          *
  *                      (DataRetrievalServiceCore.java:198)     *
  *                      at                                      *
  *                      com.lombardisoftware.server.ejb.analysi *
  *                      s.                                      *
  *                      DataRetrievalServiceCore.executePerform *
  *                      anceDataSearch                          *
  *                      (DataRetrievalServiceCore.java:185)     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The code for the Performance Query was incorrectly determining
  whether search constraints were provided or not. This would
  result
  in the addition of an empty parameter, "AND ()", to end of its
  query as no constraints were actually present.
  

Problem conclusion

• This issue was resolved by modifying the logic to ensure that an
  empty set of constraints would not trigger the logic to add
  search constraints to the query.
  
  Install/Dependency information is available in the readme.txt on
  FixCentral. When the user goes to the download files page in
  FixCentral, the readme will be available with the fix package as
  a separate download.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  BPM ADVANCED

• Fixed component ID

  5725C9400

Applicable component levels

• R800 PSY

     UP