4

I am trying to insert a value into my sqlite DB that consists of multiple lines and special characters.

For example:

{
"foo"="$BAR"
}

I have been using the sqlite3 command but I dont know how to get this in.

Also within my bash script I have variables like $BAR that need to be parsed.

Updated command (with samples variables.)

API=2039470928570983
USERNAME=Admin
PASSWORD=PASSWORD
sqlite3 /home/xbmc/test.db "INSERT INTO DownloadClients VALUES (1,1,Sabnzbd,Sabnzbd,'{"host": "localhost", "port": 8085, "apiKey": "$API", "username": "$USERNAME", "password": "$PASSWORD", "tvCategory": "tv", "recentTvPriority": 1, "olderTvPriority": -100, "useSsl": false}', SabnzbdSettings)"

I now get Error: no such column: Sabnzbd I did try listing the column names before the VALUES operator but I got the same error.

3
  • post script block; I don't understand what is the problem with "foo"="$BAR" part. Maybe you want your script to literally have $BAR at some point; not evaluated, in which case you should use '$BAR' Nov 17, 2014 at 16:55
  • The variable may work for all I know. I cant get past the { with out an error. There are also commas within the value. Everything between and including the {} is a value for one column
    – JpaytonWPD
    Nov 17, 2014 at 17:16
  • If you don’t want to move to something like Python, where this would be safer, you could install jq and use that to escape the json to be shell-escaped (I think no has a function for that) Dec 1, 2018 at 7:56

3 Answers 3

1

Somewhere on the internet I had found this solution some time ago:

sqlite3 $database <<EOF
ATTACH DATABASE "${example_db}" AS ref;
YOUR MULTILINE
SQLITE STATEMENT;
CREATE TEMPORARY TABLE...;
TEMPORARY TABLE STILL THERE;
EOF

Not sure what happens when you do multiple SELECT statements.

0

try with this

sqlite3 test.db "INSERT INTO table VALUES (1,1,Sabnzbd,Sabnzbd,q'{"host": "localhost", "port": 8085, "apiKey": "$API", "username": "$USERNAME", "password": "$PASSWORD", "tvCategory": "tv", "recentTvPriority": 1, "olderTvPriority": -100, "useSsl": false}', SabnzbdSettings)"

This way you will get $VARIABLES to render inside query, if {} still throw errors you will need to figure out a way to escape this json string you're trying to insert.

3
  • That does seem to work but now I am getting Error: no such column: Sabnzbd Sabnzbd is the value not the column. I tried adding the list of column names relitave to the list of the values before the VALUES operator but it says the same thing.
    – JpaytonWPD
    Nov 17, 2014 at 22:14
  • try longer form INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...) Nov 17, 2014 at 22:18
  • Thats what I tried. Turns out it needs single quotes around the values.
    – JpaytonWPD
    Nov 17, 2014 at 22:31
0

The whole string has to be in Double Quotes. " The Values have to be in Single Quotes. '

sqlite3 /home/xbmc/test.db "INSERT INTO DownloadClients VALUES (NULL,'$USERNAME','Three','Four','{"host": "localhost", "port": 8085, "apiKey": "$API", "username": "$USERNAME", "password": "$PASSWORD", "tvCategory": "tv", "recentTvPriority": 1, "olderTvPriority": -100, "useSsl": false}', 5)"

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .