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

For an MS SQL repository, queries in the repo config file containing concatenation functions do not work

    XMLWordPrintable

Details

    • Sprint 21

    Description

      In MS SQL repo.jdbc.json queries containing concatenation function does not work.

      For example, the "get-managed-users" contains CONCAT('/',${orderBy}), when you try to call this query by logging into UI and Reload the user grid, you'll get exception

      Jul 28, 2014 4:03:55 AM org.forgerock.openidm.servlet.internal.ServletConnectionFactory$1 filterGenericError
      WARNING: Resource exception: 500 Internal Server Error: "DB reported failure executing query ;DECLARE @dir varchar(4) =
      ?;WITH results AS (SELECT rowNo = ROW_NUMBER() OVER( ORDER BY CASE WHEN @dir = 'asc' THEN orderby.propvalue END asc, CAS
      E WHEN @dir = 'desc' THEN orderby.propvalue END desc   ), obj.fullobject FROM openidm.managedobjects obj INNER JOIN open
      idm.objecttypes objTypes ON obj.objecttypes_id = objTypes.id and objTypes.objecttype = ? INNER JOIN openidm.managedobjec
      tproperties orderby on obj.id = orderby.managedobjects_id and orderby.propkey = CONCAT('/',?)) SELECT * FROM results WHE
      RE rowNo BETWEEN ?+1 AND ?+? with params: {formatted=false, orderByDir=asc, orderBy=userName, _pagedResultsOffset=0, res
      ource=managed/user, _queryId=get-managed-users, sidx=userName, rows=10, _resource=managed/user, sord=asc, page=1, search
      =false, nd=1406545435318, _pageSize=10, _queryExpression=null} error code: 195 sqlstate: S00010 message: 'CONCAT' is not
       a recognized built-in function name."
      

      According to discussion in http://stackoverflow.com/questions/8547737/sql-server-2008-express-concat-doesnt-exist
      this can be resolved by wrapping the CONCAT into curly brackets, like

      {fn CONCAT('/',?))}
      

      Attachments

        Activity

          People

            huck.elliott Huck Elliott
            Ladislav.Folta Ladislav Folta
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: