SQLite Forum

SQLite3.exe - How to use arguments AND remain in interactive mode?
Login

SQLite3.exe - How to use arguments AND remain in interactive mode?

(1) By MBL (RoboManni) on 2021-08-06 08:50:36 [link] [source]

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] [source]

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] [source]

Or an .sqliterc file ... in the home directory ...

(4) By RandomCoder on 2021-08-06 21:13:00 in reply to 1 [link] [source]

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.

(5) By Harald Hanche-Olsen (hanche) on 2021-08-07 06:07:57 in reply to 3 [link] [source]

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] [source]

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] [source]

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] [source]

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.

(9) By Keith Medcalf (kmedcalf) on 2021-08-07 20:34:12 in reply to 6 [link] [source]

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.

(10) By Keith Medcalf (kmedcalf) on 2021-08-07 20:45:23 in reply to 7 [source]

-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.