SQLite Forum

Shell command parsing bugs with .open

Shell command parsing bugs with .open

(1) By Warren Young (wyoung) on 2020-08-15 16:24:05 [link] [source]

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.

(2) By Richard Hipp (drh) on 2020-08-15 16:54:15 in reply to 1 [link] [source]

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 [link] [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 sqlite3 -help 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]?

(4) By anonymous on 2020-08-15 20:32:22 in reply to 2 [source]

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.

(5) By Warren Young (wyoung) on 2020-08-15 22:09:04 in reply to 4 [link] [source]

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

(6) By Trudge on 2020-08-22 02:04:28 in reply to 5 [link] [source]

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:

(7) By Warren Young (wyoung) on 2020-08-22 03:21:11 in reply to 6 [link] [source]

You've got spaces in that path. You need to quote them in the command:

 ... ".once '$bufile'" ...

You're well beyond SQLite questions here.

(8) By Trudge on 2020-08-22 03:49:38 in reply to 7 [link] [source]

Apologies for going off track. Your 'adjustment' is right on the money. Obviously a lack of knowledge on my part. Thank you everyone for spending the time on this.