2

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.

Community
  • 1
  • 1
merlin2011
  • 71,677
  • 44
  • 195
  • 329

2 Answers2

2

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!

Corbell
  • 1,283
  • 8
  • 15
  • I am not willing to tolerate the `doesn't have any quote characters` constraint, or else this solution would work. – merlin2011 May 10 '14 at 07:03
  • Wouldn't you have to deal with quote characters even if you could read the text inline into your statement at the sqlite prompt? – Corbell May 10 '14 at 07:06
  • The hope was that it would be possible to capture the contents of the file into a variable, where the process of reading the contents into the variable would perform all necessary escaping. – merlin2011 May 10 '14 at 07:08
  • There is a possibility that this question has no answer, and I will have to resort to using an actual programming language to write what I want. If I end up doing that, I will post an answer so other people do not have to rewrite it. – merlin2011 May 10 '14 at 07:08
  • Just for posterity - to escape all single quotes in a text file: $ sed -i -e "s/'/\\\'/g" TheFile.txt – Corbell May 10 '14 at 07:13
  • Absolutely correct, but if you now manually edit the resulting file and add another unescaped single quote, you cannot simple repeat the process to "fix" the file again. The solution would require you to maintain an escaped and unescaped version of a file constantly, which is reasonable but still cumbersome. :) – merlin2011 May 10 '14 at 07:16
2

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.

szym
  • 5,606
  • 28
  • 34