14

I'm trying to save clipboard content into an sqlite database. Created database and tables.

I don't want it to create journal file in every clipboard change, so I tried to pass PRAGMA journal_mode = OFF; flag. But it's tricky to pass those commands in a one liner commands because sqlite only accepts two command like

sqlite3 clipboard_archive.db "insert into cb (cb_context) values ('clipboard');"

it works. I looked for Q&A sites, some advises echoing the commands in the following way.

echo "PRAGMA journal_mode = OFF;" | sqlite3 clipboard_archive.db "insert into cb (cb_context) values ('clipboard');"

But PRAGMA journal_mode = OFF; doesn't take effect in that way though it works within the sqlite3 command prompt.

What's wrong with my one liner script?

2
  • 3
    You should either put all of the commands in through echo or all of them as an argument to sqlite3.
    – chicks
    Commented Oct 13, 2017 at 2:29
  • @chicks "I believe I tried that but it didn't work in my attempt." you know it's a noob phenomenon. If you posted it as an answer, I'd have another option.
    – kenn
    Commented Oct 13, 2017 at 11:20

4 Answers 4

16

derobert's answer doesn't seem to work with dot-commands, but you can use -cmd: sqlite3 tolls.sql3 -cmd ".mode csv" ".import tolls.csv tolls"

it accepts multiple -cmd commands and executes them in order, before the final arg.

3
  • 1
    On CentOS 7 when I try to use more than one command with the -cmd argument I get an Error: too many options. However, what does work is the following: sqlite3 some.db -cmd ".separator ','" <<< ".import table1.csv table1" <<< ".import table2.csv table2"
    – hndcrftd
    Commented May 18, 2020 at 14:41
  • 2
    on Windows, if you install sqlite3 you can pass dot commands without the -cmd argument, i.e.: sqlite3 some.db ".separator ','" ".import table1.csv table1" ".import table2.csv table2" I know this is unrelated to UNIX, but I wanted to include this comment just in case people find this article online and they are on Windows.
    – hndcrftd
    Commented May 18, 2020 at 14:47
  • I wonder if there is any nicer way to make it not stop to read from stdin after doing that than echo | sqlite3 .... Tried -cmd '.quit' but didn't work, sqlite3 3.33. Commented Jan 28, 2021 at 0:08
8

Not sure why you want to use SQLite if you don't want the journal (have you considered the much faster WAL mode if speed is a concern?) but you can give multiple commands separated by semicolons:

sqlite3 clipboard_archive.db "PRAGMA journal_mode = OFF; insert into cb (cb_context) values ('clipboard');"
1
  • I just had time to test it, it works, thanks. I am just a beginner in data storing. Thank you for the suggestion, I was not aware of WAL mode. I ll take a look at it. My concern was not speed, I didn't want hard disk to wear down.
    – kenn
    Commented Oct 13, 2017 at 11:13
7

You can run…

$ sqlite3 <database_file> '<statement_1>' ['<statement_n>']

Example…

$ sqlite3 data.db '.mode json' 'select * from my_table'
4

It seems that you can use the -cmd switch not only once. This also works for me (SQLite 3.8.7.2 on Windows):

sqlite3.exe yourdb.db -cmd ".mode column" -cmd ".headers on" "select * from persons;"

You must log in to answer this question.

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