How to extract Bitmap image from SQL records?

12

2

enter image description here

I'm trying to extract image from records of the MSSQL demo database NorthWind with this codes,

Needs["DatabaseLink`"];
conn = OpenSQLConnection["Northwind"];(*Northwind built mannually*)
imgdata = 
 Last@First@SQLExecute[conn, "select top 1 * from categories"];

and

ExportString[FromCharacterCode[First@imgdata], "RawBitmap", 
 ImageSize -> {60, 30}]

but it doesnot work properly, nor

file = "c:\\test.bmp";
BinaryWrite[file, FromCharacterCode@First@imgdata];
Close@file;

How to get the image exactly? Thanks! Northwind database could be downloaded from Microsoft web site https://www.microsoft.com/en-us/download/details.aspx?id=23654. Also the testing imgdata mentioned up could be found here.

URLDownload["https://i.stack.imgur.com/kaYzO.png", "C:\\test.zip"];
ExtractArchive["C:\\test.zip", "C:\\"];
imgdata = Import["C:\\imgdata.txt"]//ToExpression

enter image description here

Jerry

Posted 2018-10-27T07:57:52.227

Reputation: 2 272

What is the database schema? e.g. what columns does the table have and what kind of values are in those columns. – C. E. – 2018-10-27T08:05:23.857

The imgdata that Selected from records of the Northwind table Categories and hidden in birds picture is a sample for testing. – Jerry – 2018-10-27T08:37:42.317

That description makes no sense to me. I expected a description such as "there is one column r which is the row number, one column c which is the column number, one column v which is the value of the corresponding pixel." Something that would allow us to map the database table to an image. – C. E. – 2018-10-27T08:43:28.740

The select statement's screen snapshot is uploaded for your reference. thanks! – Jerry – 2018-10-27T09:00:32.120

Now I understand, thank you for the update. – C. E. – 2018-10-27T09:01:08.043

Answers

14

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"]

gotta love those old low-res BMPs

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

Northwind Categories Table 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).

WReach

Posted 2018-10-27T07:57:52.227

Reputation: 62 787

1

Thanks to @WReach's help,

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

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

then the last demo imgdata :

Needs["DatabaseLink`"]

URLDownload["https://i.stack.imgur.com/kaYzO.png", "C:\\test.zip"];
ExtractArchive["C:\\test.zip", "C:\\"];
imgdata = Import["C:\\imgdata.txt"]//ToExpression(*from string to expression*);

could be shown indeed.

fromOleBmp[imgdata]

or

ImportString[(Last@imgdata)[[79 ;;]] // FromCharacterCode, "BMP"]

enter image description here

Jerry

Posted 2018-10-27T07:57:52.227

Reputation: 2 272