0

I have an sqlite file. It has a table that stores images as BLOBs.
What is the easiest way to see the images stored in the table?
Doing this:
$sqlite3 mydb 'select(img) from image_table where id=“1”’ > image.png
The image.png does not open on double click.

I tried also: 

sqlite3 my.db "SELECT writefile('object0.gz', MyBlob) FROM MyTable WHERE id = 1"

but I am not sure how to open the object0.gz file. When I double click it the archive tool opens

Jim
  • 18,826
  • 34
  • 135
  • 254
  • That command produces text output. – Ben Jan 17 '16 at 14:02
  • @Ben: 1) I need to see all the rows in the table not just one 2) I tried the `sqlite3 m.db 'SELECT writefile("/proc/self/fd/1", img) FROM t WHERE id = 1;' > image.png` and when I double click on the `image.png` the file still doesn't open – Jim Jan 17 '16 at 14:23
  • @Ben: I tried the `sqlite3 my.db "SELECT writefile('object0.gz', MyBlob) FROM MyTable WHERE id = 1"` as the answer but how can I open the `object0.gz`? – Jim Jan 17 '16 at 14:26
  • Please click `edit` under your original question and add code & details there rather than in comments where the formatting is hard to read. – Mark Setchell Jan 17 '16 at 14:55
  • @Jim, the answer is on the linked question, first answer. "With SQLite 3.8.6 or later, the command-line shell includes the fileio extension, which implements the writefile function" – Ben Jan 17 '16 at 15:03
  • @MarkSetchell:Updated OP – Jim Jan 17 '16 at 15:57
  • @Ben: But the file does not open as an image. Please see updated OP – Jim Jan 17 '16 at 15:57
  • Jim, of course it doesn't open as an image! It is text. Read the other question - the one this duplicates. – Ben Jan 17 '16 at 16:11
  • How should the OS know that the file is an image when you give it a different extension? – CL. Jan 17 '16 at 16:29
  • How did you put the image into the database? – Mark Setchell Jan 17 '16 at 16:30
  • @CL.: When I do `f$ile object0.gz` I get: `file object0.gz object0.gz: JPEG image data, JFIF standard 1.01` – Jim Jan 17 '16 at 17:37
  • @MarkSetchell:See previous comment – Jim Jan 17 '16 at 17:37
  • @Ben:No, please see my comment to CL – Jim Jan 17 '16 at 17:37
  • @CL.:I changed the extension and it opened – Jim Jan 17 '16 at 17:51

0 Answers0