Shell command parsing bugs with .open
To help someone in another thread, I was trying to get this to work:
.once foo.sql ; .dump
I tried it two different ways: once in the interactive shell and once as parameters to the
sqlite3 command after the DB file name. Either way, it gave a "usage" message, from which I infer that the SQLite shell command processor is not seeing that it's getting a file name parameter. Perhaps it's trying to interpret the semicolon as part of the file name?
Regardless of the "why," it would be useful if this would work because you could then do a dump-to-SQL without any I/O redirection on the command.
Do it like this:
sqlite3 database.db -cmd '.once foo.sql' .dump
(3) By Larry Brasfield (LarryBrasfield) on 2020-08-15 17:17:44 in reply to 2 [source]
It's good to see there is a way to do such one-liners.
When I went to try something similar (after seeing Warren's post), I ran
which produced some tips beginning with:
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database.
. This led me to try a few things which did not work. For example:
sqlite3 -cmd ".once stupid.sql" -cmd .dump -cmd .quit stupid.sdb
sqlite3 -cmd ".once stupid.sql ; .dump ; .quit" stupid.sdb
sqlite3 stupid.sdb -cmd ".once stupid.sql ; .dump ; .quit"
, all of which told me what commands can be used.
Prior to seeing the above tip, I did not realize that 'sqlite3 -help' might have said:
sqlite3 FILENAME [options] [SQL]
. Is semicolon a meta-command separator as Warren hoped? (I see no evidence of it.) Is there a way to specify multiple successive meta-commands on the invocation command tail? Why does "sqlite3 database.db -cmd '.once foo.sql' .dump" work? Is it because [SQL] is really [meta-command or SQL]?
It also works like this (at least under Windows that I tried):
sqlite3 ".once foo.sql" ".dump"
You just need to give each command its own quotes.
Yes, that also works. Thanks!
That allows a simplification of the Perl from the other thread to:
my $dbfile = "$SRCDIR/kba.db"; my $bufile = "$TARDIR/$Today.sql"; die "No such backup directory $TARDIR!\n" unless -d $TARDIR; open my $cmd, '|-', '/usr/bin/sqlite3', $dbfile, ".once $bufile", '.dump' or die "Failed to dump DB $dbfile to $bufile: $!\n"; close $cmd or die "Backup to $dbfile failed: $!\n";
I seem to have stirred things up here. Many thanks for all the extra work you folks are doing. Just tried the above code in my Perl script but it does not create the .sql file
My error log file shows: Usage: .once FILE Backup to /Volumes/femto/FemtoDocuments/My SQL DB/kba/kba.db failed:
You've got spaces in that path. You need to quote them in the command:
... ".once '$bufile'" ...
You're well beyond SQLite questions here.