2 Replies Latest reply: Oct 27, 2011 10:28 AM by Jeffrey Goldberg RSS

    Configurable ODBC Group Resolution not working

      Has anyone gotten group resolution for configurable odbc to work?  I'm trying to add groups to a document and the configurable ODBC doesn't resolve groupid in search string in add users.

       

      Here is an example of my user view:

      EntityID                EntityName          descr      Email
      2500800000578     2500800000578    UserX      UserX@email.com
      
      

       

      Here is the SQL code for the Users view:

       

      select
       cast(clientid as bigint)*100000000 + cast(userid as bigint) as EntityID,
       cast(clientid as bigint)*100000000 + cast(userid as bigint) as EntityName,
       FirstName + ' ' + LastName as descr,
       Email
      from dbo.Users with (nolock);
      
      

       

      Here is an example of my group view:

       

      ID                         MemberID           GroupID
      2500800000024     2500800000578    2500800000024
      


      I'm trying to add group 2500800000024 but if I try to search for it when adding users/groups, nothing is resolved.

       

      Here is the SQL code to create the usergroup view:

       

      select
       cast(clientid as bigint)*100000000 + cast(usertypeid as bigint)  as ID,
       cast(clientid as bigint)*100000000 + cast(userid as bigint) as MemberID,
       cast(clientid as bigint)*100000000 + cast(usertypeid as bigint) as GroupID
      from dbo.Users with (nolock);
      
      

       

      Any ideas as to how this might work is greatly appreciated.

       

      Servers are running QlikView 10 SR2

      The ODBC connection is to SQL server.

       

      Thanks,

       

      Jeff G

        • Configurable ODBC Group Resolution not working

          hi Jeffrey

           

          documentations for Configurable ODBC...

          ---

          36.2 Configurable ODBC
          The ODBC database has to have two table, or two views, one for entities and one for groups.
          The entity table must have the four following fields: entityid, name, descr and email. The fields name,
          descr and email must be strings. Entityid must be a unique identifier (suitable for primary key).
          The groups table must contain two fields: groupid and memberid. Together these two fields create a
          unique identifier.

          ---

          The 'memberid' should contain the 'uid' and the 'groups' info should contain the name on the group.

           

          Thnks.

           

           

            • Configurable ODBC Group Resolution not working

              Ok, so bottomline, the documentation is completely unhelpful.  I worked through the group resolution issue and as far as I'm concerned it's a bug.

               

              The world I'm supporting has many many users, so many that using a bigint datatype for the entityid is required.  When doing user resolution, having the bigint works without issue.

               

              However, try to get a bigint working in the groups table and you are going to find that group resolution won't work.  The reason is that while you can make the memberid and groupid a bigint, the code for reading the fields will only recognize values up to max(int) or approximately 2.1 billion.  My opinion, this is a big ole bug that hopefully will be addressed soon.