6

I have a couple basic scripts that kick out a little information while my sqlite3 DB is being populated, but about half the time the command instantly fails:

$ sqlite3 outgoing.db "select * from edges where worker is not null;"
Error: database is locked
$ sqlite3 outgoing.db "select * from edges where worker is not null;"
Error: database is locked
$ sqlite3 outgoing.db "select * from edges where worker is not null;"
1014->9000|1014|9000||-1.0|2
1014->9001|1014|9001||-1.0|2
...

If I add .timeout 1; to the beginning of the command I just get a syntax error; how do I pass sqlite the special . parameters non-interactively via the command-line?

4 Answers 4

6

You can do this by using an init file.

init.sql (note that the timeout value is in milliseconds - 1 is rather short):

.timeout 1000

At the prompt:

$ sqlite3 -init init.sql outgoing.db "select * from edges where worker is not null;"
Loading resources from init.sql
# 1 second pause
Error: database is locked

With some shells (on Linux at least, not very portable), you can avoid the need for a proper file with process substitution if that's a problem:

$ sqlite3 -init <(echo .timeout 1000) your.db "your sql;"

The extra output line ("Loading resources from ...") doesn't get printed if the output is redirected to a file or pipe, or to the .output file if you specified one in your init file.

8

Just use:

$ sqlite3 -cmd ".timeout 1000" outgoing.db "SELECT * FROM edges"
3
  • 1
    This looks very similar to an already provided answer.
    – Alexei
    Mar 9, 2018 at 11:53
  • 2
    No, because I am providing dedicated sqlite3 param '-cmd'
    – luzik
    Mar 9, 2018 at 20:17
  • 5
    This is actually the most elegant answer because it avoids process substitutions and heredocs. Dec 29, 2018 at 11:21
4

In the sqlite3 command-line tool, dot commands are not SQL commands, so you cannot mix them with queries in the same line (and you should not delimit them with a semicolon).

sqlite3 can read commands from its standard input, so you can simply use a here document to give it multiple lines:

$ sqlite3 outgoing.db <<EOF
.timeout 1000
select * from edges where worker is not null;
EOF
0

I made this little bash function for it, so that I wouldn't need to make a file:

function patient_sqlite {
  DB_FILE="$1"
  QUERY="$2"
  echo $'.timeout 1000\n'"$QUERY" | sqlite3 "$DB_FILE"
}
patient_sqlite db.sqlite 'SELECT * FROM users LIMIT 1;'

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.