CODEWITHSUNDEEP
sqlsqlite
merlin2011
merlin2011

Reputation: 75565

How can I update a single field in sqlite3 with the contents of a file?

This is equivalent to my earlier question here, but for sqlite.

As before, I am trying to do the following using the sqlite3 command line client.

UPDATE my_table set my_column=CONTENT_FROM_FILE where id=1;

I have looked at the documentation on .import, but that seems to be a little heavyweight for what I am trying to do.

What is the correct way to set the value of one field from a file?

The method I seek should not impose constraints on the contents of the file.

Upvotes: 2

Views: 1771

Answers (2)

szym
szym

Reputation: 5846

See: http://www.sqlite.org/cli.html#fileio

sqlite> INSERT INTO images(name,type,img)
   ...>   VALUES('icon','jpeg',readfile('icon.jpg'));

In your case:

UPDATE my_table set my_column=readfile('yourfile') where id=1;

If you don't have readfile, you need to .load the module first.

Note

I found that the provided fileio module: http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/fileio.c uses sqlite3_result_blob. When I use it in my project with text columns, it results in Chinese characters being inserted into the table rather than the bytes read from file. This can be fixed by changing it to sqlite3_result_text. See http://www.sqlite.org/loadext.html for instructions on building and loading run-time extensions.

Upvotes: 2

Corbell
Corbell

Reputation: 1393

Assuming the file content is all UTF-8 text and doesn't have any quote characters that would be misinterpreted, you could do this (assuming posix shell - on Windows try cygwin):

$ echo "UPDATE my_table set my_column='" >> temp.sql
$ cat YourContentFile >> temp.sql
$ echo "' where id=1;" >> temp.sql
$ sqlite3
SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read temp.sql

If the content does have single quotes, escape them first with a simple find-and-replace (you'd need to do that anyway).

hth!

Upvotes: 2

Related Questions