12
$\begingroup$

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

$\endgroup$
  • $\begingroup$ What is the database schema? e.g. what columns does the table have and what kind of values are in those columns. $\endgroup$ – C. E. Oct 27 '18 at 8:05
  • $\begingroup$ The imgdata that Selected from records of the Northwind table Categories and hidden in birds picture is a sample for testing. $\endgroup$ – Jerry Oct 27 '18 at 8:37
  • $\begingroup$ 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. $\endgroup$ – C. E. Oct 27 '18 at 8:43
  • $\begingroup$ The select statement's screen snapshot is uploaded for your reference. thanks! $\endgroup$ – Jerry Oct 27 '18 at 9:00
  • $\begingroup$ Now I understand, thank you for the update. $\endgroup$ – C. E. Oct 27 '18 at 9:01
14
$\begingroup$

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).

$\endgroup$
1
$\begingroup$

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

$\endgroup$

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.