Extracting Photos from Dynamics with Go

We use Microsoft Dynamics AX 2012 as our “System of Record (SOR)” and single source of truth for our people (HR) data. One of the things we keep there is a photo of each person. These days, there are several places that the photo ideally needs to be, in order to provide a consistent user experience across the variety of applications we use on a daily basis, ranging from Business Messaging to internally developed applications and dashboards using a static load balancer.

Ultimately we developed a command line application using Cobra that runs on a scheduled basis to keep photos across all the platforms up to date. This post is about the way Dynamics stores the photos and how we overcame that using Go.

Microsoft Dynamics uses SQL Server to store all its data. Photos are stored as a varbinary(max) in the HCMPERSONIMAGE table. I therefore thought it would be a simple case of extracting the binary image and saving it to disk. On attempting this, the resulting file never resulted in the expected image. Since I’m quite new to Go, I suspected I was just doing it wrong, but after some discussion on Stack Overflow it seemed I wasn’t doing anything wrong in my code.

Microsoft saves the image to the database using their BinData class, and I started to suspect that it wasn’t just the binary image they were storing. I therefore started digging deeper and comparing the downloaded file with the original.

On closer inspection, each downloaded image had 7 bytes of additional data at the start of the file compared to the original. Removing those additional bytes before saving the file resulted in an exact copy of the original image! 😓😃

Following the download of the image, we check to see what type of image it is and convert it to jpeg if necessary. An extract of the code follows.

First we get the data from SQL:

query := `SELECT t1.PERSON, t1.IMAGE, t2.FIRSTNAME, t2.LASTNAME, t3.LOCATOR
FROM HCMPERSONIMAGE t1
LEFT JOIN DIRPERSONNAME t2 on t2.PERSON = t1.PERSON
LEFT JOIN LOGISTICSELECTRONICADDRESS t3 ON t3.PRIVATEFORPARTY = t1.PERSON AND t3.TYPE = 2 AND t3.ISPRIMARY = 1
WHERE t3.LOCATOR IS NOT NULL`

rows, err := db.Query(query)
if err != nil {
    return err
}
defer rows.Close()
defer db.Close()

Then we enumerate the rows, converting if necessary and saving each image, of course dropping the troublesome first seven bytes:

var count int
for rows.Next() {
    p := new(Person)
    err := rows.Scan(&p.PersonID, &p.Image, &p.FirstName, &p.LastName, &p.EmailAddress)
    if err != nil {
        log.Println(err)
    }
    count++
        
    // This is where we drop the first 7 bytes
    rawImage := p.Image[7:] 
    image, kind, err := image.Decode(bytes.NewReader(rawImage))
    if err != nil {
        log.Println(err)
    }

    // location is a command line flag for the path to save the files
    formattedFilePath := path.Clean(location)
    s := filepath.FromSlash(fmt.Sprintf("%s/%s.%s", formattedFilePath, p.EmailAddress, "jpg"))
    log.Printf("writing %s file %s\n", "jpeg", s)
    f, err := os.Create(s)
    if err != nil {
        log.Fatal(err)
    }
    defer f.Close()

    // If it's not jpeg, convert it and save the file
    if kind != "jpeg" {
        log.Printf("converting %s to jpeg", kind)
        err = jpeg.Encode(f, image, &jpeg.Options{Quality: 100})
        if err != nil {
            log.Printf("error converting file %s: %s\n", s, err)
        }
    } else {
        if _, err := f.Write([]byte(rawImage)); err != nil {
            log.Printf("error writing file %s: %s\n", s, err)
        }
    }
}
err = rows.Err()
if err != nil {
    return err
}
if count < 1 {
    log.Println("no records found!")
    return nil
}
log.Printf("returned %d record(s).\n", count)
return nil

This allowed us to download the images, which is useful in some situations, but in reality, we actually use various libraries as part of the command to put the photos where we need them, including Microsoft Graph for Office 365, and the "github.com/pkg/sftp" and "golang.org/x/crypto/ssh" libraries for transferring them to our load balancers – that might be worth another post!