SQLite3.exe - How to use arguments AND remain in interactive mode?
(1) By MBL (RoboManni) on 2021-08-06 08:50:36 [link]
I am using often SQLite3.exe (Windows 10) and would like to start with command line parameters but then remain in interactive mode. I only get either or.... but is there any trick to start with parameters AND remain in interactive command line mode? ~~~ D:\Debug>sqlite3.exe :memory: .load SQLite3.dll Usage: .load FILE ?ENTRYPOINT? D:\Debug>sqlite3.exe :memory: ".load SQLite3.dll" D:\Debug>sqlite3.exe :memory: ".load SQLite3.dll" - sqlite3.exe: Error: unknown option: - Use -help for a list of options. D:\Debug>sqlite3.exe :memory: ".load SQLite3.dll" .interactive Error: unknown command or invalid arguments: "interactive". Enter ".help" for help D:\Debug>sqlite3.exe .help SQLite version 3.36.0 2021-06-18 18:36:39 Enter ".help" for usage hints. sqlite> .exit D:\Debug>sqlite3.exe :memory: ".load SQLite3.dll" -- sqlite3.exe: Error: unknown option: - Use -help for a list of options. D:\Debug>sqlite3.exe -help Usage: sqlite3.exe [OPTIONS] FILENAME [SQL] FILENAME is the name of an SQLite database. A new database is created if the file does not previously exist. OPTIONS include: -A ARGS... run ".archive ARGS" and exit -append append the database to the end of the file -ascii set output mode to 'ascii' -bail stop after hitting an error -batch force batch I/O -box set output mode to 'box' -column set output mode to 'column' -cmd COMMAND run "COMMAND" before reading stdin -csv set output mode to 'csv' -deserialize open the database using sqlite3_deserialize() -echo print commands before execution -init FILENAME read/process named file -[no]header turn headers on or off -help show this message -html set output mode to HTML -interactive force interactive I/O -json set output mode to 'json' -line set output mode to 'line' -list set output mode to 'list' -lookaside SIZE N use N entries of SZ bytes for lookaside memory -markdown set output mode to 'markdown' -maxsize N maximum size for a --deserialize database -memtrace trace all memory allocations and deallocations -mmap N default mmap size set to N -newline SEP set output row separator. Default: '\n' -nofollow refuse to open symbolic links to database files -nullvalue TEXT set text string for NULL values. Default '' -pagecache SIZE N use N slots of SZ bytes each for page cache memory -quote set output mode to 'quote' -readonly open the database read-only -separator SEP set output column separator. Default: '|' -stats print memory stats before each finalize -table set output mode to 'table' -tabs set output mode to 'tabs' -version show SQLite version -vfs NAME use NAME as the default VFS -zip open the file as a ZIP Archive D:\Debug>sqlite3.exe -interactive :memory: ".load SQLite3.dll" D:\Debug> ~~~ In this example the SQLite3.dll contains my loadable extension functions and virtual table extensions, it is based on the 3.36 amalgamation. The .load parameter was executed successfully but then the executable terminated immediately - without remaining in interactive mode. Some arguments which I tried did not help, they are unknown. The .help command does not show me any option which would be good to remain in interactive mode when using command line parameters. Is there a trick which I do not know yet? ~~~ Usage: sqlite3.exe [OPTIONS] FILENAME [SQL] -interactive force interactive I/O ~~~ ## How to use this option together with the other dot-arguments like .load, which the usage hint does not even show as optional?
(2) By Harald Hanche-Olsen (hanche) on 2021-08-06 10:26:37 in reply to 1 [link]
That can't be done, as far as I know. One workaround is to put the sql commands (and/or dot commands) into a file, then specify that file with the `-init` flag to the command shell.
(3) By Keith Medcalf (kmedcalf) on 2021-08-06 20:57:00 in reply to 2 [link]
Or an .sqliterc file ... in the home directory ...
(5) By Harald Hanche-Olsen (hanche) on 2021-08-07 06:07:57 in reply to 3
Indeed, but since that is intended for *all* your SQLite sessions, it could be undesirable to use it for ad hoc purposes.
(6) By MBL (RoboManni) on 2021-08-07 08:52:32 in reply to 3 [link]
As it is my intention to always automatically load my extension functions and virtual tables into the standard SQLite3.exe when it starts, the trick with a hidden file **.sqliterc** would be a good solution for me. However, I **cannot find** that file on my HD partitions at all. Where on **Windows 10** is that file stored? Do I need to prepare something to make it created the first time? I scanned the whole tree `C:/Users/MBL/` but there is no such file to find.
(7) By MBL (RoboManni) on 2021-08-07 09:52:04 in reply to 6 [link]
# I found the solution which works fine for me and better than before: I am calling the command line interface (CLI) tool SQLite3.exe now per batch file and added loading of the extension dll into an additional file. The location of the batch file is on the environmental path configuration for me as user. The command parameter **`-interactive`** does **NOT** make any difference if I supply it or not. As soon as there is at least one SQL command provided the tool will not go into interactive mode but exits after execution: ## D:\\UTL\\sql3.bat ~~~ @D:\UTL\SQLite3.exe -echo -interactive -init D:\UTL\.SQLiteRC %* ~~~ ## D:\\UTL\\.SQLiteRC ~~~ .load D:\UTL\SQLite3.dll .mode box ~~~ ## Command line input ~~~ D:\UTL>sql3.bat -version -- Loading resources from D:\UTL\.SQLiteRC 3.36.0 2021-06-18 18:36:39 5c9a6c06871cb9fe42814af9c039eb6da5427a6ec28f187af7ebfb62eafa66e5 D:\UTL>sql3.bat :memory: "select nv('name1|value1|name2|value2|name3|value3','name2|name1') as result" -- Loading resources from D:\UTL\.SQLiteRC select nv('name1|value1|name2|value2|name3|value3','name2|name1') as result ┌───────────────┐ │ result │ ├───────────────┤ │ value2|value1 │ └───────────────┘ D:\UTL>sql3.bat :memory: -- Loading resources from D:\UTL\.SQLiteRC SQLite version 3.36.0 2021-06-18 18:36:39 Enter ".help" for usage hints. sqlite> .exit .exit D:\UTL> ~~~ ## question Is this ignorance of the **`-interactive`** command line option intended or is this a buggy behavior? Where can I find the documentatino of this option?
(8) By Scott Robison (casaderobison) on 2021-08-07 19:37:33 in reply to 7 [link]
My understanding is that the SQLite shell will attempt to detect whether it should be in batch mode or interactive mode. If stdin is attached to a tty, it defaults to interactive. If stdin is not attached to a tty, it is batch. The heuristic may not be perfect, so the command line options exist to override the default. This does not change the default "if you issue a command line query, it will exit when complete". This is why someone suggested adding a ".interactive" command to your copy of the shell (along with a diff to show what would need to be done) so that perhaps you could issue .interactive in your script to transition back from batch to interactive mode.
(10) By Keith Medcalf (kmedcalf) on 2021-08-07 20:45:23 in reply to 7 [link]
`-interactive` and `-batch` probably do not do what you think they do. In all cases, the CLI "reads" its input from STDIN `-interactive` means that an ugly-bag-of-mostly-water may be providing the STDIN input stream and hence "prompts" should be provided to awaken the water-bag from time to time. `-batch` means that the STDIN input stream is attached to a source other than an ugly-bag-of-mostly-water and that "prompts" are not required to spur the water-bag to send the next command. ``` 2021-08-07 14:40:38 PY64 3.9 [D:\work\covid] >sqlite3 -interactive cases.db -- Loading resources from C:\Users\KMedcalf/.sqliterc SQLite version 3.37.0 2021-08-06 23:38:02 Enter ".help" for usage hints. sqlite> .mode list sqlite> select * from provcasedays where days == 7 and province == 'ab'; Location|Date|Tests|Cases|Recovered|ChgActive|ChgHosp|ChgICU|ChgDeath|TtlDeath|NetActive|NetHosp|NetICU|NetDeath|SNetActive|SChgActive|SNetHosp|SNetICU|SNetDeath|Rs AB|2021-07-30|8293|187|49|135|1|-1|3|2342|1567|90|20|3|1457|153|88|21|1|1.46 AB|2021-07-31|0|0|0|0|0|0|0|2342|1567|90|20|0|1590|133|89|21|1|1.47 AB|2021-08-01|0|0|0|0|0|0|0|2342|1567|90|20|0|1741|150|91|21|0|1.48 AB|2021-08-02|0|0|0|0|0|0|0|2342|1567|90|20|0|1855|144|93|22|1|1.48 AB|2021-08-03|21609|743|222|521|0|4|0|2342|2088|90|24|0|1971|174|94|23|0|1.48 AB|2021-08-04|5419|206|100|106|7|0|0|2342|2194|97|24|0|2240|290|96|24|0|1.48 AB|2021-08-05|7461|397|152|244|5|1|1|2343|2438|102|25|1|2438|244|102|25|1|1.48 sqlite> ^Z 2021-08-07 14:42:46 PY64 3.9 [D:\work\covid] >sqlite3 -batch cases.db .mode list select * from provcasedays where days == 7 and province == 'ab'; Location|Date|Tests|Cases|Recovered|ChgActive|ChgHosp|ChgICU|ChgDeath|TtlDeath|NetActive|NetHosp|NetICU|NetDeath|SNetActive|SChgActive|SNetHosp|SNetICU|SNetDeath|Rs AB|2021-07-30|8293|187|49|135|1|-1|3|2342|1567|90|20|3|1457|153|88|21|1|1.46 AB|2021-07-31|0|0|0|0|0|0|0|2342|1567|90|20|0|1590|133|89|21|1|1.47 AB|2021-08-01|0|0|0|0|0|0|0|2342|1567|90|20|0|1741|150|91|21|0|1.48 AB|2021-08-02|0|0|0|0|0|0|0|2342|1567|90|20|0|1855|144|93|22|1|1.48 AB|2021-08-03|21609|743|222|521|0|4|0|2342|2088|90|24|0|1971|174|94|23|0|1.48 AB|2021-08-04|5419|206|100|106|7|0|0|2342|2194|97|24|0|2240|290|96|24|0|1.48 AB|2021-08-05|7461|397|152|244|5|1|1|2343|2438|102|25|1|2438|244|102|25|1|1.48 ^Z ``` -batch and -interactive also have an effect on error handling. Obviously, the ugly-bag-of-mostly-water cannot intervene if the bag is not present (-batch), but if it is (-interactive) then the bag can whammo the ^C and ^Z to get out of here.
(9) By Keith Medcalf (kmedcalf) on 2021-08-07 20:34:12 in reply to 6 [link]
The .sqliterc file exists in your home directory AFTER YOU CREATE IT. If you have not yet created it, then it does not yet exist. On Windows 10: It is stored in the directory %USERPROFILE% By default, the UserProfile directory is `C:\Users\<userid>\` By default, the sqliterc file is called `C:\Users\<userid>\.sqliterc` The location of the `USERS` directory is set from the registry when the OS boots. The %USERPROFILE% directory is set by default when a user logs on. Like everything in Windows 10, you can change the base User directory and/or the UserProfile directory on a system by system and user by user basis.
(4) By RandomCoder on 2021-08-06 21:13:00 in reply to 1 [link]
As others have mentioned, there's no direct way to do this, however, you could add an `.interactive` quasi-command to the command line parser if you really want to by modifying shell.c and building it yourself: ``` 22295c22295,22297 < if( azCmd[i][0]=='.' ){ --- > if( strcmp(azCmd[i],".interactive")==0 ){ > readStdin=1; > }else if( azCmd[i][0]=='.' ){ 22316c22318,22319 < }else{ --- > } > if( readStdin ){ ``` Though, I don't know shell.c that well, there may be reasons this is a horrid idea.