4 Replies Latest reply: Jan 23, 2015 10:42 AM by John Cavoulas RSS

    Importing user picture ressource from AD using OLE DB Provider for Microsoft Directory Services

    Marco Wedel

      Hi everyone,

       

      I recently tried to import user pictures from Microsoft Active Directory to personalize a QV application (logged on users should see their AD picture).

      While doing so, I encountered a problem importing and converting the thumbnailPhoto active directory attribute.

      ThumbnailPhoto in this case contains a user picture (JPG) coded as an octet string.

      Unlike the expected string length of some kB I only got a string length of 511bytes leading to a corrupt jpg file as most of the information was missing.

      Nevertheless I tried to import this picture stub and decided to work on the string length issue later on.

      As I could not load the picture ressource directly from the string, I previously saved it to disk.

      The conversion and saving was done using vbs.

      Here are the load script and vbs function:

       

      OLEDB CONNECT32 TO [Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Data Source=<ADserverName>;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];

       

      tabADPhoto:

      SQL select

         SN,

         givenName,

        sAMAccountName,

        thumbnailPhoto

      from 'LDAP://<domainname>.com'

      where

        sAMAccountName = '<useraccount>';

       

      Left Join

      LOAD

        sAMAccountName,

        len(thumbnailPhoto) as LenThumbnailPhoto

      Resident tabADPhoto;

       

      tabTemp:

      LOAD

        sAMAccountName,

        OctetStringToFile(thumbnailPhoto, 'U:\Qlikview\'&sAMAccountName&'.jpg') as ResultOctetStringToFile

      Resident tabADPhoto;

       

      Bundle Info LOAD

        sAMAccountName,

        'U:\Qlikview\'&sAMAccountName&'.jpg'

      Resident tabADPhoto;

       

      ------------------------------------------------------------------------

       

      Function OctetStringToFile(OctetString, FilePath)

      Const adTypeText = 2

      Const adSaveCreateOverWrite = 2

       

      LenData = Len(OctetString)\2 + Len(OctetString) Mod 2

      set fso = createobject("scripting.filesystemobject")

      set stream = createobject("adodb.stream")

      stream.type = adTypeText

      stream.charset = "windows-1252"

      stream.open

      For i = 0 to LenData-1

        stream.writetext chr(CByte("&h" & Mid(OctetString, i*2+1, 2)))

      Next

      stream.savetofile FilePath, adSaveCreateOverWrite

      stream.close

      OctetStringToFile = "processed"

      End Function

       

       

      QlikCommunity_Thread_UserPics AD_Pic1.JPG.jpg

       

      QlikCommunity_Thread_UserPics AD_Pic2.JPG.jpg

       

      Any ideas as to why the thumbnailPhoto attribute octet string is truncated or whether there is a more straight forward approach for this picture ressource import?

       

      thanks in advance

       

      Marco