Mixing
How to exctract Bitmap image from SQL records?
Clash Royale CLAN TAG#URR8PPP
up vote
7
down vote
favorite
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 = ReadLine["C:\imgdata.txt"]
image databaselink sql
asked 11 hours ago
Jerry
39319
add a comment |Â
up vote
7
down vote
favorite
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 = ReadLine["C:\imgdata.txt"]
image databaselink sql
asked 11 hours ago
Jerry
39319
What is the database schema? e.g. what columns does the table have and what kind of values are in those columns.
– C. E.
11 hours ago
Theimgdatathat Selected from records of theNorthwindtableCategoriesand hidden in birds picture is a sample for testing.
– Jerry
10 hours ago
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.
10 hours ago
Theselectstatement's screen snapshot is uploaded for your reference. thanks!
– Jerry
10 hours ago
Now I understand, thank you for the update.
– C. E.
10 hours ago
add a comment |Â
up vote
7
down vote
favorite
up vote
7
down vote
favorite
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 = ReadLine["C:\imgdata.txt"]
image databaselink sql
asked 11 hours ago
Jerry
39319
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 = ReadLine["C:\imgdata.txt"]
image databaselink sql
image databaselink sql
asked 11 hours ago
Jerry
39319
asked 11 hours ago
Jerry
39319
edited 10 hours ago
asked 11 hours ago
Jerry
39319
asked 11 hours ago
Jerry
39319
asked 11 hours ago
Jerry
39319
39319
What is the database schema? e.g. what columns does the table have and what kind of values are in those columns.
– C. E.
11 hours ago
Theimgdatathat Selected from records of theNorthwindtableCategoriesand hidden in birds picture is a sample for testing.
– Jerry
10 hours ago
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.
10 hours ago
Theselectstatement's screen snapshot is uploaded for your reference. thanks!
– Jerry
10 hours ago
Now I understand, thank you for the update.
– C. E.
10 hours ago
add a comment |Â
What is the database schema? e.g. what columns does the table have and what kind of values are in those columns.
– C. E.
11 hours ago
Theimgdatathat Selected from records of theNorthwindtableCategoriesand hidden in birds picture is a sample for testing.
– Jerry
10 hours ago
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.
10 hours ago
Theselectstatement's screen snapshot is uploaded for your reference. thanks!
– Jerry
10 hours ago
Now I understand, thank you for the update.
– C. E.
10 hours ago
What is the database schema? e.g. what columns does the table have and what kind of values are in those columns.
– C. E.
11 hours ago
What is the database schema? e.g. what columns does the table have and what kind of values are in those columns.
– C. E.
11 hours ago
The
imgdata that Selected from records of the Northwind table Categories and hidden in birds picture is a sample for testing.– Jerry
10 hours ago
The
imgdata that Selected from records of the Northwind table Categories and hidden in birds picture is a sample for testing.– Jerry
10 hours ago
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.
10 hours ago
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.
10 hours ago
The
select statement's screen snapshot is uploaded for your reference. thanks!– Jerry
10 hours ago
The
select statement's screen snapshot is uploaded for your reference. thanks!– Jerry
10 hours ago
Now I understand, thank you for the update.
– C. E.
10 hours ago
Now I understand, thank you for the update.
– C. E.
10 hours ago
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
8
down vote
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).
answered 3 hours ago
WReach
52k2112206
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
8
down vote
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).
answered 3 hours ago
WReach
52k2112206
add a comment |Â
up vote
8
down vote
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).
answered 3 hours ago
WReach
52k2112206
add a comment |Â
up vote
8
down vote
up vote
8
down vote
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).
answered 3 hours ago
WReach
52k2112206
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).
answered 3 hours ago
WReach
52k2112206
edited 2 hours ago
answered 3 hours ago
WReach
52k2112206
answered 3 hours ago
WReach
52k2112206
answered 3 hours ago
WReach
52k2112206
52k2112206
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fmathematica.stackexchange.com%2fquestions%2f184743%2fhow-to-exctract-bitmap-image-from-sql-records%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
What is the database schema? e.g. what columns does the table have and what kind of values are in those columns.
– C. E.
11 hours ago
The
imgdatathat Selected from records of theNorthwindtableCategoriesand hidden in birds picture is a sample for testing.– Jerry
10 hours ago
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.
10 hours ago
The
selectstatement's screen snapshot is uploaded for your reference. thanks!– Jerry
10 hours ago
Now I understand, thank you for the update.
– C. E.
10 hours ago