How to dump a file stored in a sqlite database as a blob?
I had to make some minor changes on CL's answer, in order to make it work for me:
The structure for the command that he is using does not have the database name in it, the syntax that I am using is something like:
sqlite3 mydatabase.sqlite3 "Select quote(BlobField) From TableWithBlod Where StringKey = '1';" | ...
The way he is using the
cut
command does not work in my machine. The correct way for me is:cut -d "'" -f2
So the final command would be something like:
sqlite3 mydatabase.sqlite3 "Select quote(BlobField) From TableWithBlod Where StringKey = '1';" | cut -d "'" -f2 | xxd -r -p > myfile.extension
And in my case:
sqlite3 osm-carto_z14_m8_m.mbtiles "select quote(images.tile_data) from images where images.tile_id = '1';" | cut -d "'" -f2 | xxd -r -p > image.png
You can use writefile
if using the sqlite3
command line tool:
Example usage:
select writefile('blob.bin', blob_column) from table where key='12345';
sqlite3
cannot output binary data directly, so you have to convert the data to a hexdump, use cut
to extract the hex digits from the blob literal, and use xxd
(part of the vim
package) to convert the hexdump back into binary:
sqlite3 my.db "SELECT quote(MyBlob) FROM MyTable WHERE id = 1;" \
| cut -d\' -f2 \
| xxd -r -p \
> object0.gz
With SQLite 3.8.6 or later, the command-line shell includes the fileio
extension, which implements the writefile
function:
sqlite3 my.db "SELECT writefile('object0.gz', MyBlob) FROM MyTable WHERE id = 1"
In my case, I use "hex" instead of "quote" to retrieve image from database, and no need "cut" in the command pipe. For example:
sqlite3 fr.db "select hex(bmp) from reg where id=1" | xxd -r -p > 2.png