sqlite3 '-batch' option appears undocumented
(1) By Nicolas Girard (ngirard) on 2020-11-21 12:13:02 [link]
I've had a hard time looking for a more substantial description of sqlite3's '-batch' option than what the command line says ("force batch I/O"), which doesn't mean much to me. Naively I'd expected to find something in the cli.html page. In a [recent forum thread](https://sqlite.org/forum/forumpost/8bd3fab3d8), someone complained that he also was "unclear" about this option, to which Larry Brasfield replied: > How 'sqlite3 -batch ...' differs from 'sqlite3 ...' invoked with redirected input is not a simple subject. (Anybody who doubts this should study shell.c, paying attention to sets/uses of the variable stdin_is_interactive and various tests such as 'isatty(0)', 'in!=0' and 'in==stdin'.) It thus seems to me that there is room for improving the documentation here. Cheers!
(2) By John Gelm (gelmjw) on 2020-11-22 16:20:46 in reply to 1 [link]
-batch automatically exits the sqlite command line so you don't leave an active job hanging. I use it like this in cron 0 */6 * * * (sleep 20; cd /var/www/html/ssmppt15l/ && touch sweep.txt && /usr/bin/sqlite3 -batch ':memory:' '.read sweep_ramdisk.sql') If you run /usr/bin/sqlite3 -batch ':memory:' '.read sweep_ramdisk.sql' at the cli, the sqlite will process and then exit as if you had entered the .quit command.
(3.2) By Keith Medcalf (kmedcalf) on 2020-11-22 23:24:13 edited from 3.1 in reply to 2 [link]
It should cause an "exit code" to be returned when the process terminates. An "interactive process" (one that does not have -batch specified) does not return a failure (non-zero) exit code because it is not possible for an interactive process to exit other than successfully (though opinions may vary, it is a fact, though I suppose that EOF on the input stream could constitute an ABEND). A process created using redirection (eg "sqlite3 \<input >output") is an interactive process and as such should always return 0 as the exit code notwithstanding any transient failure other than process ABEND (such as the previously mentioned EOF condition, perhaps). Contrast with "sqlite3 -batch \<input >output" which should return a non-zero exit code only in the event that the process ABENDed -- an EOF on the input stream is NOT an ABEND. An examination of the shell.c code would be necessary to determine if this is indeed what the -batch option does. However, this is the "normal" meaning of such options converting an "interactive" program to "batch" operation. It is what an one would expect such a parameter to mean. Note also -bail which will presumably make a -batch exit immediately when encountering an error. Rudimentary testing indicates that -batch forces "batch" mode even if the input is from a tty (console) (basically, that there is no interactive prompt issued). It may affect the process return code, but I have not tested that. -bail only has effect when in "non-interactive mode", that is the input is not from a tty (console) *or* -batch is specified.
(4) By David Jones (vman59) on 2020-11-22 23:38:37 in reply to 1
Browsing the source for the shell program, the setting of stdin_is_interactive cause numerous miscellaneous effects. Among the things affected are error message generation, whether the input line is saved in the history file, and whether an interrupt signal causes program exit or just aborts the current command.
(5) By Mark Lawrence (mark) on 2022-02-09 10:29:20 in reply to 1 [link]
This thread has not been commented on by the developers yet, and I think this is still a valid documentation/usage issue. I have a related issue which I can't say is a bug or not, because of a lack of documentation: $ sqlite3 --version 3.38.0 2022-02-05 12:39:17 fc4c82e5694cbdae13f2cb90e5368ba9f6b826a220a81de32ead2ffe1bc5f31f $ sqlite3 -batch -cmd 'select 1;' 1 - 1 # hangs waiting for input until .quit or EOF $ sqlite3 -batch -cmd 'select 1;' -cmd '.quit' 1 - 1 # hangs waiting for input until another .quit or EOF Questions: 1. Why does -cmd '.quit' not appear to end the session? 2. How do I do a one-shot call to the sqlite3 CLI without dealing with input/output (not easy from the calling environment in this case).
(6.1) By Larry Brasfield (larrybr) on 2022-02-09 12:31:07 edited from 6.0 in reply to 5 [link]
(Edited to add a final-final answer and clarify future change.) > Why does -cmd '.quit' not appear to end the session? I would suggest reading the code. There is no simple "why" beyond that. > How do I do a one-shot call to the sqlite3 CLI without dealing with input/output (not easy from the calling environment in this case). This has been an annoyance to me also, for quite some time. It was never intended to work that way. (Hence the limited "why".) There is <u>[a fix pending](https://www.sqlite.org/src/file?name=src/shell.c.in&ci=5cf66e89071b619d&ln=12403-12415)</u><sup>a</sup> which, one way or another, in some form, will make it to trunk for the next release. So, one answer is: Wait for that release. Another answer: Apply the code changes yourself and rebuild the shell. A semi-final answer: Grab the repo and build from the shell-tweaks branch. (It is highly compatible with earlier shell releases when run without enabling any new features.) An easy final answer: Today, if you do not want stdin to be read by the CLI (either redirected or interactively), you can just use bare arguments rather than preceding them with '-cmd'. The .quit command is not needed then; the shell will terminate when the last such bare command has been run. The presence of any bare commands will avoid an interactive session. For example:<code> $ sqlite3 :memory: ".print hi" hi $ </code> ---- a. The fix requires changes beyond what is highlighted at the link-view, to get the ".quit" return code routed to that code.
(7) By Larry Brasfield (larrybr) on 2022-02-09 11:28:57 in reply to 5 [link]
> This thread has not been commented on by the developers yet, and I think this is still a valid documentation/usage issue. I agree that it should be documented. Unfortunately, "it" (the behavior in question) is too complicated to describe without code-like language. This behavior will be "regularized"<sup>a</sup> in a future release of the shell. Until then, I am extremely reluctant to describe it<sup>b</sup> because then we are more committed to preserving it. And I am even more reluctant to do that. ---- a. Or, put another way: Made simple enough to be describable with a few sentences. b. It is possible to read the code and figure out what will happen for given inputs. But it takes careful reading and note-taking. The truth-table is longer than it should be.
(8) By Holger J (holgerj) on 2022-02-09 12:47:25 in reply to 5 [link]
This delivers an EOF condition to sqlite3: # sqlite3 :memory: -cmd ".print hi" < /dev/null
(9) By Mark Lawrence (mark) on 2022-02-09 15:04:51 in reply to 8 [link]
Thanks, but shell redirection is something I don't have access to for this particular job. However Larry's reminder about the plain SQL argument (no --cmd) does the job. The plethora of combinations can lead one to miss the basics.