3

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.

  • 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' – Hrvoje Špoljar Nov 17 '14 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 '14 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) – Cameron Kerr Dec 1 '18 at 7:56
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.

  • 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 '14 at 22:14
  • try longer form INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...) – Hrvoje Špoljar Nov 17 '14 at 22:18
  • Thats what I tried. Turns out it needs single quotes around the values. – JpaytonWPD Nov 17 '14 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)"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.