1

I have a SQLite database, stuff.db (with TABLE notes (id INTEGER PRIMARY KEY, timeStamp DATE, note TEXT);). The timeStamp is automatically triggered.

I want to populate it the quick and dirty way from the command line.

So, I have written a script named bashscript1 which goes :

sqlite3 script.db 'insert into notes (note) values ("Stuff happens.");'

But I wanted both to keep this sentence as the standard entry and if necessary, to be more precise : I would like to add variable information such as : "Foo enters the room." or "Bar makes a phone call.". The idea is to concatenate those strings and have : "Stuff happens. Foo enters the room." or "Stuff happens. Bar makes a phone call."

So I rewrote the script :

echo "Do you want to add somme text ?"
read Str1
sqlite3 script.db 'insert into notes (note) values ("Stuff happens."||\"$Str1\");'

And, of course I got an error message. How should I proceed ?

Thanks in advance

ThG

Improve this question

2 Answers 2

Reset to default
2

Try:

sqlite3 script.db "insert into notes (note) values ('Stuff happens.$Str1');"

Note that this will break if your $Str1 contains a single quote.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks for your answer, but I get in sqlite an error message : Error: unrecognized token: "\"
sorry, I am rather new.. What did you mean by Edited ? I hope I did not forget something. BTW, would'nt it be a better solution to concatenate the 2 strings in bash before sending it to sqlite ? But I do not know how to do that ...(same mesg to SiegeX)
@ThG - I'm kinda new too :) I edited my answer - it looks to me like the single quotes work in a test script I tried, and the syntax for SQLite expects single quotes around strings.
1

Shell variables wont expand when wrapped in single-quotes like you have it. Perhaps you want:

sqlite3 script.db "insert into notes (note) values (\"Stuff happens.\"||\"$Str1\");"

P.S. I'm assuming the || is part of the sqlite3 syntax because it certainly isn't bash, at least not in that context. If that's not right, let me know.

Improve this answer

6 Comments

I confirm that || is the concatenate operator in sqlite. But as with sje397 I get an error message : Error: unrecognized token: "\"
@SiegeX : Would'nt it be a better solution to concatenate the 2 strings in bash before sending it to sqlite ? But I do not know how to do that ...(same mesg to sje397)
It would be better not to get in to the quote-soup business and do it this way: sqlite3 script.db 'insert into notes (note) values ("Stuff happens."||"'"$Str1"'");'. I know it's a bit hypocritical to call this "not quote-soup" but this is the most reliable method.
@Sorpigal : that worked perfectly (but I must admit I don't grasp the logic of single quotes embedded in double quotes embedded in single quotes...). Thanks a lot. ThG
@ThG Look at Sorpigal's line in parts delimited by the single quotes. The first part is 'insert into notes (note) values ("Stuff happens."||"'. Since it's in single quotes that is a literal line not to be expanded or changed by the shell, i.e. the double quotes are literal. The next part is "$Str1" which is surrounded by double quotes telling the shell go ahead and expand $Str1. Finally, the last part is '");' which is again a literal string due to the single quotes. This method allows you to manually turn on & off shell expansion.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.