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

[MarcoWedel]

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

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

[MarcoWedel]

Anybody?

[johnca]

Hi Marco,

I'd like to do the same. I thought it may be similar to the limit of number of users you can get at one time, but looking at the pictures I did get they're all 4kb or 0kb (Blank). Some are full pictures though, so it must depend on the image size to begin with. Here's a comparison...note the resolution and sizes.

Here's mine...

Size on disk: 4.00 KB (4,096 bytes)

And here's another...

Size on disk: 4.00 KB (4,096 bytes)

[MarcoWedel]

Hi,

thanks for the reply.

I did not pursue this topic any further, but I do think it's a limitation of the AD / OLE DB provider for Microsoft Directory Services.

Unfortunately I have no clue as to how this limitation can be circumvented.

Maybe by loading parts of the data in a loop and concatenate afterwards.

There are posts that describe loading other AD account information in a loop because of those limitations.

If someone found a solution, I would be interested in learning from it.

regards

Marco

[johnca]

Yes, I believe that limitation is 999 or 1000 records, per request. I had to loop through each last name letter to get everyone. Yours is the first attempt I've seen to get the thumbnails, and although imperfect as it is its a good start.

--john