Search code examples
sqliteblobdump

how to get sql dump from sqlite database with blobs?

I need to create sql dumps in order to patch existing database files. I know how to create sql dumps with sqlite3 command line client but it does not work properly with blobs.

how to create sql dumps with proper blobs ? is there any option to sqlite3 .dump command to encode blobs as strings ?

I'd need a scriptable solution in order to integrate it into build scripts.

Cheers

Solution

  • If your data was inserted in a way, that SQLite thinks it's string (TEXT) instead of BLOB, you can convert it using temporary table and forcing type conversion. Lets say you have the following table:

    CREATE TABLE images ( id, name, data )

    The example solution is:

    CREATE TEMP TABLE images2 ( id INT, name TEXT, data BLOB );
INSERT INTO images2
    SELECT id, name, CAST ( data AS BLOB ) FROM images;
DROP TABLE images;
CREATE TABLE images ( id INT, name TEXT, data BLOB );
INSERT INTO images
    SELECT * FROM images;

    Worked for me...