Inserting multiple lines into a sqlite column with bash script

Question

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
– Hrvoje Špoljar, Commented 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
– JpaytonWPD, Commented 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) — Cameron Kerr
– Cameron Kerr, Commented Dec 1, 2018 at 7:56

Radio Controlled · Accepted Answer · 2021-11-11 06:43:08Z

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.

Hrvoje Špoljar · Accepted Answer · 2014-11-17 18:24:52Z

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

JpaytonWPD · Accepted Answer · 2014-11-17 22:30:00Z

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)"

Stack Exchange Network

Inserting multiple lines into a sqlite column with bash script

3 Answers 3

You must log in to answer this question.

Hot Network Questions

Inserting multiple lines into a sqlite column with bash script

3 Answers 3

You must log in to answer this question.

Related

Hot Network Questions