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

Endpoint reconResults not working with MS-SQL/DB2 as repo (internal error 500)

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: OpenIDM 3.2.0, OpenIDM 4.0.0
    • Fix Version/s: OpenIDM 4.0.0
    • Environment:
      Windows 2008, MS-SLQ, r5493, Openidm 616 on a CentOS with DB2 as repo
    • Story Points:
      3
    • Sprint:
      OpenIDM Sprint 50, OpenIDM Sprint 54

      Description

      When using MS-SQL as repo, the endpoint reconResults fails with an error 500:

      curl --header "X-OpenIDM-Password: openidm-admin" --header "X-OpenIDM-Username: openidm-admin" --request GET "http://localhost:8080/openidm/endpoint/reconResults?_queryId=reconResults&source=system/xmlfile/account&target=managed/user&sourceProps=email,firstname,lastname,description&targetProps=mail,givenName,sn,description&reconId=9f672064-90f2-48ae-935c-13895494ff42&situations=SOURCE_IGNORED,MISSING,FOUND,AMBIGUOUS,UNQUALIFIED,CONFIRMED,SOURCE_MISSING,ABSENT,TARGET_IGNORED,UNASSIGNED,FOUND_ALREADY_LINKED&mapping=systemXmlfileAccounts_managedUser&nd=1433396445446&rows=10&page=1&sidx=&sord=asc&search=false"
      
      {"code":500,"reason":"Internal Server Error","message":"DB reported failure executing query WITH results AS ( SELECT rowNo = ROW_NUMBER() OVER( ORDER BY sourceobjectid ASC, targetobjectid ASC, ambiguoustargetobjectids ASC, activitydate ASC, situation ASC, linkqualifier ASC, activitydate DESC ), openidm.auditrecon.* FROM openidm.auditrecon  WHERE (reconid = ? AND entrytype = ? AND activitydate <= ? AND (situation = ? OR situation = ? OR situation = ? OR situation = ? OR situation = ? OR situation = ? OR situation = ? OR situation = ? OR situation = ? OR situation = ? OR situation = ?))) SELECT * FROM results WHERE rowNo BETWEEN 1 AND 10 with params: {formatted=false, _resource=audit/recon, situations=SOURCE_IGNORED,MISSING,FOUND,AMBIGUOUS,UNQUALIFIED,CONFIRMED,SOURCE_MISSING,ABSENT,TARGET_IGNORED,UNASSIGNED,FOUND_ALREADY_LINKED, _pagedResultsOffset=0, _sortKeys=[+/sourceObjectId, +/targetObjectId, +/ambiguousTargetObjectIds, +/timestamp, +/situation, +/linkQualifier, -/timestamp], _queryId=null, _pageSize=10, _queryExpression=null, _queryFilter=(/reconId eq \"9f672064-90f2-48ae-935c-13895494ff42\" and /entryType eq \"entry\" and /timestamp le \"2015-06-27T11:35:56.188Z\" and (/situation eq \"SOURCE_IGNORED\" or /situation eq \"MISSING\" or /situation eq \"FOUND\" or /situation eq \"AMBIGUOUS\" or /situation eq \"UNQUALIFIED\" or /situation eq \"CONFIRMED\" or /situation eq \"SOURCE_MISSING\" or /situation eq \"ABSENT\" or /situation eq \"TARGET_IGNORED\" or /situation eq \"UNASSIGNED\" or /situation eq \"FOUND_ALREADY_LINKED\")), completed=2015-06-27T11:35:56.188Z} error code: 306 sqlstate: S0002 message: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.","detail":{"fileName":"/C:/jenkins/workspace/OpenIDM-Trunk-Functional-Tests-Win2008-64-bit-MS-SQL/PyBot/OpenIDM/install/openidm/bin/defaults/script/ui/reconResults.js","lineNumber":138,"columnNumber":0}}
      

      To reproduce, launch OpenIDM with sample1, launch the recon and launch the GET on the reconResults endpoint.

      Note: this endpoint is used in the UI to grab information from audit/recon and display results from last recon in the mapping pages.

        Attachments

          Activity

            People

            • Assignee:
              jason Jason Lemay
              Reporter:
              laurent.bristiel Laurent Bristiel [X] (Inactive)
              QA Assignee:
              Tinghua.Xu
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: