How to extract Bitmap image from SQL records?

Summary

The Northwind database was originally an MS Access database, so the pictures are BMP images wrapped within OLE objects. We must strip the OLE header before parsing them. The following function will perform that operation upon a SQLBinary column:

fromOleBmp[SQLBinary[data_]] := data[[79;;]] // ByteArray // ImportByteArray[#, "BMP"]&

These details are very specific to the images in the Northwind database. However, the principle is generally the same for images in other databases. Namely, if the image format is supported by Mathematica then ImportByteArray is the tool for the conversion.

For older versions of Mathematica, see the compatibility note at the bottom of this posting.

Details

Let's start by loading all rows from the Categories table:

Needs["DatabaseLink`"]

$sql = OpenSQLConnection["Northwind"];

$data = SQLExecute[$sql, "SELECT * FROM Categories"];

Length[$data]

(* 8 *)

Here is the first row:

$data // First // InputForm // Shallow

(* {1, "Beverages", "Soft drinks, coffees, teas, beers, and ales", SQLBinary[<<1>>]} *)

... and here is the first Picture value:

$data[[1, 4]] // Short

(* SQLBinary[{21,28,47,0,2,0,0,0,13,0,14,0,20,0,33,0,255,255,255,255,66,
              105,116,<<10700>>,53,0,0,0,0,0,0,0,0,0,0,1,5,0,0,0,0,0,0,199,173,5,254}] *)

The Northwind database was originally an MS Access database. So we might guess that the pictures are in BMP format. Alas, it is not so simple:

$firstPicture = $data[[1, 4, 1]];

ImportByteArray[$firstPicture // ByteArray, "BMP"]

(* Import::fmterr: Cannot import data as BMP format. *)

It turns out that the images in Access databases were all wrapped within OLE Objects. For BMP images, that header is 78 bytes long. We can see the BMP file signature bytes "BM" at the appropriate position:

$firstPicture[[79 ;; 80]] // FromCharacterCode

(* "BM" *)

The bytes from that point forward constitute a valid, if low-quality, BMP:

ImportByteArray[$firstPicture[[79 ;;]] // ByteArray, "BMP"]

We will make a helper function for this conversion:

fromOleBmp[SQLBinary[data_]] := data[[79;;]] // ByteArray // ImportByteArray[#, "BMP"]&

... and then apply it to the 4th (Picture) column in the original data:

$converted = $data // Query[All, {4 -> fromOleBmp}];

$converted // Dataset

Compatibility Note

In older versions of Mathematica which lack the function ImportByteArray, we can convert the BMP bytes using ImportString instead:

ImportString[$firstPicture[[79 ;;]] // FromCharacterCode, "BMP"]

The byte array approach is preferable if possible as arbitrary strings of bytes are not guaranteed to be preserved when converted into Unicode strings (although in practice they usually are in older versions).