How to get sql dump from sqlite database using blob?

Question

I need to create sql dumps to fix existing database files. I know how to create sql dumps using the sqlite3 command line client, but it does not work properly with blobs.

how to create a sql dump with the right blobs? is there any option for sqlite3.dump command to encode the drop as strings?

I will need a scripted solution to integrate it into build scripts.

Greetings

Answer 1

, SQLite (TEXT) BLOB, , . , :

CREATE TABLE images ( id, name, data )

:

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;

...

Answer 2

.

:

sqlite> CREATE TABLE images( id INTEGER PRIMARY KEY, name TEXT, content BLOB );

:

sqlite> INSERT INTO images VALUES ( 1, 'test 1', X'00ff00ffaabbcc' );

:

sqlite> .dump

:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE images( id INTEGER PRIMARY KEY, name TEXT, content BLOB );
INSERT INTO "images" VALUES(1,'test 1',X'00FF00FFAABBCC');
COMMIT;