Saving Blob data from SQLite database to a file

Question

I'm trying to save blob data from a SQLite database (Safari cache: Cache.db) to a file, but for some reason sqlite won't read the whole blob. I eventually would like to do this in ruby, but for now something that works directly in sqlite command prompt is fine. Also, I've read all of the entries that talk about this here on stackoverflow, but most of them only discuss the performance of saving images in blobs and the one entry that does show to save blobs to file is in C# which does not help me. Here is what I've tried:

sqlite> select * from cfurl_cache_response limit 1; 3501|0|945281827|0|http://www.gospelz.com/components/com_jomcomment/smilies/guest.gif|2010-02-24 16:20:07

sqlite> select receiver_data from cfurl_cache_blob_data where entry_ID = 3501;
GIF89a(

A hexdump of the original (guest.gif) file shows that sqlite stops reading the blob after the first null value:

$ hexdump -C guest.gif
00000000 47 49 46 38 39 61 28 00 28 00 f7 00 00 f1 f5 fd |GIF89a(.(.......|

sqlite> .output test.gif
sqlite> select receiver_data from cfurl_cache_blob_data where entry_ID = 3501;
$ hexdump -C test.gif
00000000 47 49 46 38 39 61 28 0a |GIF89a(.|

Answer 1

SQLite3 is reading the whole blob, but the sqlite shell program is only displaying up to the NUL.

To test this you can try:

select hex( receiver_data ) from cfurl_cache_blob_data where entry_ID = 3501;

Answer 2

Generating a file from a blob or raw binary is pretty straight forward in Ruby. You do it the same way you'd write to a file. For example, to create a jpeg:

File.open("image_name.jpg", 'w') {|f| f.write raw_blob_content }