Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-12632

queryFilter on recon audit fails using MSSQL as repo

    Details

    • Target Version/s:
    • Verified Version/s:
    • Story Points:
      1
    • Sprint:
      OpenIDM Sprint 7.0-5, 2019.6 - IDM
    • Support Ticket IDs:

      Description

      IDM 6.5.
      Using MSSQL as repository, and repo as handler for audit queries.

      Querying the recon audit with "queryFilter=... and /message eq ..." fails because in the DB schema for MSSQL, "message" is defined as ntext.

      E.g.

      REST GET of

      /openidm/audit/recon?_queryFilter=/reconId eq "65e155de-c8f3-41ad-b674-b3d96256660f-57366" and /message eq "Policy validation failed" and /status pr and /status eq "FAILURE"&aggregateFields=TIMESTAMP=/timestamp,VALUE=/message,VALUE=/status,VALUE=/messageDetail,VALUE=/sourceObjectId

       

      Response is:

      {
       "code": 500,
       "reason": "Internal Server Error",
       "message": "DB reported failure executing query."
      }

       

      openidm log:

      [162] Feb 18, 2019 6:14:43.277 PM org.forgerock.openidm.repo.jdbc.impl.query.TableQueries query
      FEIN: DB reported failure executing query org.forgerock.openidm.repo.jdbc.impl.query.TracedPreparedStatement@751323cc with params: {_pageSize=2147483647, _queryFilter=(/reconId eq "65e155de-c8f3-41ad-b674-b3d96256660f-57366" and /message eq "Policy validation failed" and /status pr and /status eq "FAILURE"), _pagedResultsCookie={}, aggregateFields=TIMESTAMP=/timestamp,VALUE=/message,VALUE=/status,VALUE=/messageDetail,VALUE=/sourceObjectId, _queryExpression=null, _pagedResultsOffset=0, _resource=audit/recon, _queryId=null, _sortKeys=[]} error code: 402 sqlstate: S0001 message: The data types ntext and nvarchar are incompatible in the equal to operator.
      com.microsoft.sqlserver.jdbc.SQLServerException: The data types ntext and nvarchar are incompatible in the equal to operator.
       at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)
      ...

       

       WORKAROUND:

      The query works if the "co" operator is used instead of "eq".
      (queryFilter=/message co ...)

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                jason.vincent jason vincent
                Reporter:
                wei-yee.lum Wei-Yee Lum
              • Votes:
                0 Vote for this issue
                Watchers:
                8 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: