SQLite database convert string back into blob

Question

I have a three SQLite databases, that need to be joined together. I have already tried following procedure:

(1) SELECT column1, column2, quote(column3) from table1 - quote(X) is to convert blob field into string field

(2)I import table1.csv back into database, where i define first two fields as INTEGERin third as STRING

(3) I use: INSERT INTO table_new (column1, column2, column3) SELECT column1, column2, column3 FROM table1

but it doesn't convert correctly (output is a picture.png)

There should be an function that is opposite to QUOTE and would return me back my converted data?

Thank you very much for your help :)

CL. · Accepted Answer · 2013-07-18 10:43:24Z

2

There is no built-in SQL function that converts a blob literal (x'102234') back into a blob, because such a literal is intended to be used in SQL commands.

The easiest way to move data between databases is to use the sqlite3 command-line tool:

sqlite3 old.db ".dump" > data.sql
sqlite3 new.db < data.sql

Other tools like SQLite Manager have similar functions to export/import databases.

edited Jul 18, 2013 at 10:43

answered Jul 17, 2013 at 14:48

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

vvidmar Over a year ago

hello, I have tried: sqlite3 osm_grayscale_slo.mbtiles ".dump" > data.sql and also osm_grayscale_slo.mbtiles ".dump" > sqlite_master.sql, but it didn't work :( what am I doing wrong?

vvidmar Over a year ago

I am using SQLite Manager from Firefox and can't get the '.dump' function to work.

CL. Over a year ago

When I wrote "the sqlite3 command-line tool", I meant "the sqlite3 command-line tool" and not "SQLite Manager".

vvidmar Over a year ago

hello, I have figured this out, how to work with sqlite3 command tool. I think my main problem is that I have a database that has *.mbtiles ending and it doesn't recognise it. I think this would work on *db databases.

CL. Over a year ago

The actual file name does not matter.

|

Collectives™ on Stack Overflow

SQLite database convert string back into blob

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related