Incorrect SQL generated by Configurable ODBC Directory Service Connector

[#1]

Hello,

I was just wondering if anybody else had encountered this problem or devised a workaround. In QV10, when searching for a user in QEMC such as 'ACTOHE01', the generated SQL would be as follows:

SELECT
  e.entityid,
  e.NAME,
  e.descr,
  e.email,
  COUNT(g.memberid) AS noGroupMembers
FROM
  QV_ENTITY e
LEFT JOIN QV_ROLES g
ON
  e.entityid=g.groupid
WHERE
  e.NAME = 'ACTOHE01'
GROUP BY
  e.entityid,
  e.NAME,
  e.descr,
  e.email 

However when I perform the same search in QV11 QMC, the following SQL is generated:

SELECT
  e.entityid,
  e.NAME,
  e.descr,
  e.email,
  COUNT(g.memberid) AS noGroupMembers
FROM
  QV_ENTITY e
LEFT JOIN QV_ROLES g
ON
  e.entityid=g.groupid
WHERE
  e.NAME   = 'actohe01'
OR e.descr = 'actohe01'
OR e.NAME  = 'actohe01'
GROUP BY
  e.entityid,
  e.NAME,
  e.descr,
  e.email

This may seem like a trivial difference, however string comparisons in Oracle are case-sensitive so this second query does not return any results. It seems like whatever you search for, the generated SQL will have your search string in all lower case. Since all of our user and group names are in upper case, it is impossible to look up users in our DSP in QMC at all, making it hard to manually add user CALs, examine which documents a user has DMS authorisation for, etc.

Has anybody else encountered the same issue?

Kind regards,

Michael.

[#2]

Michael,

I also observed something odd. 

Based on the documentation, I would expect groupid should be the unique identifier of the group and memberid would be the unique identifier for the user (entityid) from the entities table.  Yet the QV_ENTITY table is being joined to the QV_ROLES table (in your example and similarly in my environment) using QV_ENTITY.entityid = QV_ROLES.groupid.  This will produce some interesting results, if any at all.

Mike

[#3]

Hi Mike,

I think this is intended - The QV_ENTITY table contains both groups and users and then the QV_ROLES table is essentially a bridge from QV_ENTITY to itself, containg the entityid of the user (aliased as memberid) and group (aliased as groupid). So a join on QV_ENTITY.entityid = QV_ROLES.groupid will just return a row for each member of each group.

Presumably this query is performed so you can search for group names as well as usernames - If noGroupMembers = 0 then it will just return the details for the username searched for, otherwise it performs another query to find details for all the users in the group name searched for.

It's a slightly confusing data model, having to join on fields with different names, but it works fine when it generates the right sql. It just needs to either preserve the case of the search string (which it did in QV10...) or do:

...
   lower(e.name) = 'actohe01'
OR lower(e.descr) = 'actohe01'
...

Kind regards,

Michael.