How can I import images from a Microsoft SQLServer database?

2

2

I have a table with binary data (images) in Microsoft SQlServer. I want to import that data and convert it into Image format. I wrote the following code, but it's not working.

(* demoSqlConn points to connectionId in between MM and DB and Images 
 points to Table name in Db *)
binarydata = SQLSelect[demoSqlConn, "Images"];
convertedData = FromCharacterCode[binarydata[[1, 1]]];
image=ImportString[convertedData, "JPEG"]

After evaluating this code, I get the following error:

The front end encountered an error while processing a "NotebookPredictions" packet.

For testing purposes, I evaluated binarydata separately. It returns a list like {{FFD8FFE000104A46494600010201006000600000FFEE000E41646F6265006400000\ 00001FFE1135D4578696600004D4D002A0000000800070132000200000014000000620\ 13B000200000007000000764746000300000001000400004749000300000001003F000\ 09C9D00010000000E00000000EA1C00070000080C00000000876900040000000100000\ 07D000000E7323030393A30333A31322031333A34363A343200436F726269730000059\ 003000200000014000000BF9004000200000014000000D392910002000000033534000\ 0929200020000000335340000EA1C0007000007B40000000000000000323030383A303\ 33A31342031333A35393A323600323030383A30333A31342031333A35393A323600000\ 5010300030000000100060000011A00050000000100....}}. So, I'm getting the data, the problem is converting it.

subbu

Posted 2013-05-16T13:16:35.153

Reputation: 2 204

Did you read the documentation on DatabaseLink? If yes, what did you try and what went wrong? – Sjoerd C. de Vries – 2013-05-16T18:42:26.327

@SjoerdC.deVries What I tried for conversion,that corresponding code posted.Can you check it once.also I mention Which Error I am getting at the conversion time. – subbu – 2013-05-17T06:42:02.600

That's Jpeg data, I think. – cormullion – 2013-05-17T07:48:19.880

@cormullion I tested my code step by step,even The following statement also not working. convertedData = FromCharacterCode[binarydata[[1, 1]]];. – subbu – 2013-05-17T07:58:41.567

@cormullion How can we know whether the data is JPEG or GIPor any other formats.. – subbu – 2013-05-17T08:01:12.560

2Why do you use FromCharacterCode? Why do you take the [[1,1]] part of the result? Could you provide a longer and more exact example of what binarydata looks like? Are you sure it's a GIF image? – Sjoerd C. de Vries – 2013-05-17T11:12:52.643

@SjoerdC.deVries I am getting Parent list from the database so,I used [[1,1]].I hope it's a JPEG file.I will mention longer binary data in my question – subbu – 2013-05-17T11:31:18.477

@SjoerdC.deVries I edited my question.can you check it once. – subbu – 2013-05-17T11:40:53.500

FFD8 is the magic (hex!) number that indicates JPEG files... You'll need to convert the hexadecimal back to binary though? – cormullion – 2013-05-17T11:57:53.627

Answers

3

Lets do an exercise importing and exporting JPEG data. First let's create a tiny single pixel JPEG file:

In[1]:= Export["~/Desktop/minitest.jpg", Image[{{0}}]]

Now lets import the binary data that we have just created:

In[2]:= Import["/Users/gdelfino/Desktop/minitest.jpg", "Binary"]

Out[2]:= {255, 216, 255, 224, 0, 16, 74, 70, 73, 70, 0, 1, 1, 1, 0, 72, 0, 72,
0, 0, 255, 219, 0, 67, 0, 8, 6, 6, 7, 6, 5, 8, 7, 7, 7, 9, 9, 8, 10,
12, 20, 13, 12, 11, 11, 12, 25, 18, 19, 15, 20, 29, 26, 31, 30, 29,
26, 28, 28, 32, 36, 46, 39, 32, 34, 44, 35, 28, 28, 40, 55, 41, 44,
48, 49, 52, 52, 52, 31, 39, 57, 61, 56, 50, 60, 46, 51, 52, 50, 255,
219, 0, 67, 1, 9, 9, 9, 12, 11, 12, 24, 13, 13, 24, 50, 33, 28, 33,
50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50,
50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50,
50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 255,
192, 0, 17, 8, 0, 1, 0, 1, 3, 1, 34, 0, 2, 17, 1, 3, 17, 1, 255, 196,
0, 31, 0, 0, 1, 5, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3,
4, 5, 6, 7, 8, 9, 10, 11, 255, 196, 0, 181, 16, 0, 2, 1, 3, 3, 2, 4,
3, 5, 5, 4, 4, 0, 0, 1, 125, 1, 2, 3, 0, 4, 17, 5, 18, 33, 49, 65, 6,
19, 81, 97, 7, 34, 113, 20, 50, 129, 145, 161, 8, 35, 66, 177, 193,
21, 82, 209, 240, 36, 51, 98, 114, 130, 9, 10, 22, 23, 24, 25, 26,
37, 38, 39, 40, 41, 42, 52, 53, 54, 55, 56, 57, 58, 67, 68, 69, 70,
71, 72, 73, 74, 83, 84, 85, 86, 87, 88, 89, 90, 99, 100, 101, 102,
103, 104, 105, 106, 115, 116, 117, 118, 119, 120, 121, 122, 131, 132,
133, 134, 135, 136, 137, 138, 146, 147, 148, 149, 150, 151, 152, 153,
154, 162, 163, 164, 165, 166, 167, 168, 169, 170, 178, 179, 180, 181,
182, 183, 184, 185, 186, 194, 195, 196, 197, 198, 199, 200, 201, 202,
210, 211, 212, 213, 214, 215, 216, 217, 218, 225, 226, 227, 228, 229,
230, 231, 232, 233, 234, 241, 242, 243, 244, 245, 246, 247, 248, 249,
250, 255, 196, 0, 31, 1, 0, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0, 0,
0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 255, 196, 0, 181, 17, 0, 2,
1, 2, 4, 4, 3, 4, 7, 5, 4, 4, 0, 1, 2, 119, 0, 1, 2, 3, 17, 4, 5, 33,
49, 6, 18, 65, 81, 7, 97, 113, 19, 34, 50, 129, 8, 20, 66, 145, 161,
177, 193, 9, 35, 51, 82, 240, 21, 98, 114, 209, 10, 22, 36, 52, 225,
37, 241, 23, 24, 25, 26, 38, 39, 40, 41, 42, 53, 54, 55, 56, 57, 58,
67, 68, 69, 70, 71, 72, 73, 74, 83, 84, 85, 86, 87, 88, 89, 90, 99,
100, 101, 102, 103, 104, 105, 106, 115, 116, 117, 118, 119, 120, 12
122, 130, 131, 132, 133, 134, 135, 136, 137, 138, 146, 147, 148, 149,
150, 151, 152, 153, 154, 162, 163, 164, 165, 166, 167, 168, 169, 170,
178, 179, 180, 181, 182, 183, 184, 185, 186, 194, 195, 196, 197, 198,
199, 200, 201, 202, 210, 211, 212, 213, 214, 215, 216, 217, 218, 226,
227, 228, 229, 230, 231, 232, 233, 234, 242, 243, 244, 245, 246, 247,
248, 249, 250, 255, 218, 0, 12, 3, 1, 0, 2, 17, 3, 17, 0, 63, 0, 249,
254, 138, 40, 160, 15, 255, 217}

Let's put this data in the same format as you are receiving it from the database:

In[3]:= myBinString = StringJoin[IntegerString[#, 16, 2] & /@ %]

Out[3]= \
"ffd8ffe000104a46494600010101004800480000ffdb0043000806060706050807070\
70909080a0c140d0c0b0b0c1912130f141d1a1f1e1d1a1c1c20242e2720222c231c1c2\
837292c30313434341f27393d38323c2e333432ffdb0043010909090c0b0c180d0d183\
2211c21323232323232323232323232323232323232323232323232323232323232323\
2323232323232323232323232323232323232ffc000110800010001030122000211010\
31101ffc4001f0000010501010101010100000000000000000102030405060708090a0\
bffc400b5100002010303020403050504040000017d010203000411051221314106135\
16107227114328191a1082342b1c11552d1f02433627282090a161718191a252627282\
92a3435363738393a434445464748494a535455565758595a636465666768696a73747\
5767778797a838485868788898a92939495969798999aa2a3a4a5a6a7a8a9aab2b3b4b\
5b6b7b8b9bac2c3c4c5c6c7c8c9cad2d3d4d5d6d7d8d9dae1e2e3e4e5e6e7e8e9eaf1f\
2f3f4f5f6f7f8f9faffc4001f010003010101010101010101000000000000010203040\
5060708090a0bffc400b51100020102040403040705040400010277000102031104052\
131061241510761711322328108144291a1b1c109233352f0156272d10a162434e125f\
11718191a262728292a35363738393a434445464748494a535455565758595a6364656\
66768696a737475767778797a82838485868788898a92939495969798999aa2a3a4a5a\
6a7a8a9aab2b3b4b5b6b7b8b9bac2c3c4c5c6c7c8c9cad2d3d4d5d6d7d8d9dae2e3e4e\
5e6e7e8e9eaf2f3f4f5f6f7f8f9faffda000c03010002110311003f00f9fe8a28a00ff\
fd9"

If we try to import back the JPEG in this format it fails:

In[4]:= ImportString[myBinString, "JPG"]

During evaluation of In[173]:= Import::fmterr: Cannot import data as JPEG format. >>

Out[4]= $Failed

But it will work if we do this:

In[5]:= FromCharacterCode[
 FromDigits[#, 16] & /@ 
  StringJoin /@ Partition[Characters[myBinString], 2]]

Out[5]= 

binary garbage

Now we can see the image successfully:

In[6]:= ImportString[%, "JPG"]

Out[6]= ◼

In the case of your data:

In[7]:= binarydata = 
  "FFD8FFE000104A46494600010201006000600000FFEE000E41646F6265006400000\
00001FFE1135D4578696600004D4D002A0000000800070132000200000014000000620\
13B000200000007000000764746000300000001000400004749000300000001003F000\
09C9D00010000000E00000000EA1C00070000080C00000000876900040000000100000\
07D000000E7323030393A30333A31322031333A34363A343200436F726269730000059\
003000200000014000000BF9004000200000014000000D392910002000000033534000\
0929200020000000335340000EA1C0007000007B40000000000000000323030383A303\
33A31342031333A35393A323600323030383A30333A31342031333A35393A323600000\
5010300030000000100060000011A00050000000100";

We can do the same conversion and get this binary:

In[8]:= FromCharacterCode[
          FromDigits[#, 16] & /@ 
            StringJoin /@ 
              Partition[Characters[binarydata], 2]]

more binary garbage

And this should import correctly, but as you posted a truncated version of the data it does not load:

In[9]:= ImportString[%, "JPG"]

During evaluation of In[181]:= Import::fmterr: Cannot import data as JPEG format. >>

Out[9]= $Failed

I think it should work your your data.

Gustavo Delfino

Posted 2013-05-16T13:16:35.153

Reputation: 6 847

I tried same procedure for PDF and .Txt.upto Fromcharactercode step is working.the next step ImportString is not working. I tried with the following code ImportString[%, "PDF"] – subbu – 2013-05-18T09:48:58.000

3

Your binarydata seems to be a string with hexadecimal digits. Converting this using FromCharacterCode yields nonsense, of course.

It can be solved in a few steps:

0) The string:

 imageString = 
  "FFD8FFE000104A46494600010201006000600000FFEE000E41646F6265006400000\
00001FFE1135D4578696600004D4D002A0000000800070132000200000014000000620\
13B000200000007000000764746000300000001000400004749000300000001003F000\
09C9D00010000000E00000000EA1C00070000080C00000000876900040000000100000\
07D000000E7323030393A30333A31322031333A34363A343200436F726269730000059\
003000200000014000000BF9004000200000014000000D392910002000000033534000\
0929200020000000335340000EA1C0007000007B40000000000000000323030383A303\
33A31342031333A35393A323600323030383A30333A31342031333A35393A323600000\
5010300030000000100060000011A00050000000100";

1) convert the concatenated string of hexadecimals to byte sized hexadecimal, and convert to lowercase:

StringCases[ToLowerCase@imageString, _ ~~ _, Overlaps -> False]

2) built a conversion table from hex to decimal (there probably is a built-in method, but I forgot which. It should be the inverse of IntegerString):

Thread[IntegerString[Range[0, 255], 16, 2] -> Range[0, 255]]]

{"00" -> 0, "01" -> 1, "02" -> 2, "03" -> 3, "04" -> 4, "05" -> 5, "06" -> 6, "07" -> 7, "08" -> 8, "09" -> 9, "0a" -> 10, "0b" -> 11, << ... >>
"f9" -> 249, "fa" -> 250, "fb" -> 251, "fc" -> 252, "fd" -> 253, "fe" -> 254, "ff" -> 255}

3) Convert the decimals to a string (all code together):

FromCharacterCode[
 StringCases[ToLowerCase@imageString, _ ~~ _, Overlaps -> False] /. 
  Evaluate@Thread[IntegerString[Range[0, 255], 16, 2] -> Range[0, 255]]]

enter image description here

It looks like various readable bits of header information can be seen here (Adobe, EXIF, time and dates etc.), so this looks like the real stuff. You should be able to import this now using ImportString (of course you would need the whole string and not only the part printed in the question).

Sjoerd C. de Vries

Posted 2013-05-16T13:16:35.153

Reputation: 63 549

Thanks,your code is working fine.I faced another problem about data I got another format of data from DB.it's format is in the following way 0xFFD8FFE000104A46494600010201006000600000FFE..This data starts with 0x.I tried same procedure(what you mention) with this data.I faced problem atFromCharacterCodestep.because this data starts with 0x.how can I reslove this.For my testing I removed 0xfrom the list and continued same procedure.it's working fine But the problem was I am not getting exact image. – subbu – 2013-05-18T05:59:14.407

@subbu 0x is just a convention marking the string as a hexadecimal value. – Sjoerd C. de Vries – 2013-05-18T06:32:10.750

,How you are Identifying ,This data is Hexdecimal data or another one.where you are finding this information.can you provide that link,I will also read that. Thank you.. – subbu – 2013-05-18T06:40:43.847

@subbu http://en.wikipedia.org/wiki/Hexadecimal

– Sjoerd C. de Vries – 2013-05-18T06:48:44.053