12

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?

kenn
  • 733
  • 2
  • 11
  • 22
  • 3
    You should either put all of the commands in through `echo` or all of them as an argument to `sqlite3`. – chicks Oct 13 '17 at 02: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 Oct 13 '17 at 11:20

4 Answers4

15

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.

jcomeau_ictx
  • 425
  • 6
  • 11
  • 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 May 18 '20 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 May 18 '20 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. – Ciro Santilli OurBigBook.com Jan 28 '21 at 00: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');"
derobert
  • 107,579
  • 20
  • 231
  • 279
  • 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 Oct 13 '17 at 11:13
6

You can run…

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

Example…

$ sqlite3 data.db '.mode json' 'select * from my_table'
Shammel Lee
  • 201
  • 2
  • 3
3

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

chris
  • 131
  • 3