5

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.

JpaytonWPD
  • 151

3 Answers3

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.

Radio Controlled
  • 113
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.

Hrvoje Špoljar
  • 5,405
  • 28
  • 42
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)"
JpaytonWPD
  • 151