SQLite Forum

.read with parameters... or alternative?

.read with parameters... or alternative?

(1) By L Carl (lcarlp) on 2020-06-30 21:00:56 [link] [source]

Back before I retired and I was still using Oracle, I had dozens of little SQL scripts that I used to enter common queries or perform other functions. A typical script, that I used often was this one called “count”:

select &2, count(*) from &1 group by &2;

And, I could use that script by entering something like:

@count employee department

... to see how many employees were in each department. This was a feature of Oracle’s interactive tool called SQL*Plus, which is analogous to the sqlite3 command interface.

I sorely miss the ability to pass parameters to little scripts like this. I’m a terrible typist and the less I need to type, the happier I am.

So, I suppose I could write a shell script that would write the SQLite script to a file, filling in parameter values, and then execute that file. Is that the easiest way to achieve something like this? Or is there an easier way?

(2) By Larry Brasfield (LarryBrasfield) on 2020-06-30 22:53:29 in reply to 1 [link] [source]

The SQLite shell works well [a] with piped input, so no extraneous file is necessary when using it from a more general command-line shell. It is pretty easy, with recent versions, to set parameters that will be substituted into queries that include parameters in their text. So that is likely the easiest way.

It would be equally easy to write a Perl or Python or (... ad infinitum) script to do the same thing, for those who know ... .

[a. The -batch option is useful for this. ]

It would also be relatively easy, for an experienced C programmer, to extend the SQLite shell with a command to act almost like the SQL*Plus tool you miss. Whether that would be easier for you, I cannot say. But I can say I have been tempted to create a similar extension, and so I imagine it would be welcomed here.

(3) By John McMahon (jmcm54ac) on 2020-07-06 07:25:20 in reply to 2 [link] [source]

[a. The -batch option is useful for this. ]

I don't know how this option should be used, could someone give an example please.


(4) By Larry Brasfield (LarryBrasfield) on 2020-07-06 08:40:06 in reply to 3 [link] [source]

If you invoke the SQLite CLI shell thusly: sqlite3 -help , you will see, among other invocation options with tips, this: -batch force batch I/O . What that means is that input/output operations will not be interactive, as they are (or tend to be) without that option, which causes prompts not to be issued and no startup message to be displayed.

Here is a nearly minimal (and maximally useless) example with output [a]: > echo .quit | sqlite3 -batch > . (That is no output.)

[a. This is on one of my machines running bash. Results may vary. ]

Of course, your scripts will usually do more than that.

(5) By John McMahon (jmcm54ac) on 2020-07-06 09:52:16 in reply to 4 [link] [source]

Thanks Larry

. What that means is that input/output operations will not be interactive, as they are (or tend to be) without that option, which causes prompts not to be issued and no startup message to be displayed.

Here is a nearly minimal (and maximally useless) example with output [a]: > echo .quit | sqlite3 -batch > . (That is no output.)

So that something like
cat script | sqlite3 -batch

where script may have lines of .commands and statements inter mixed and output will be clean results ie. without prompt lines etc.

I will have to test that soon.


(9) By anonymous on 2020-07-08 13:56:43 in reply to 5 [link] [source]

cat script sqlite3 -batch

I'm unclear about the -batch option.

Can someone explain how is this different from just

sqlite3 < script

which has no -batch option.

(10) By Larry Brasfield (LarryBrasfield) on 2020-07-08 15:36:26 in reply to 9 [link] [source]

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'.) The final, simplified answer is: On many platforms, not much difference.

My answer is: To be sure the interactive features are disabled, use -batch. It is clear from the code that there has been a series adjustments made to how and how well the shell "does the right thing" without being explicitly told what that is.

Not using -batch, where that forces the shell to do the right thing, is a fine bet for typed one-liners. For scripted use of sqlite3, I still consider it a bad bet.

(11) By Larry Brasfield (LarryBrasfield) on 2020-07-08 18:45:55 in reply to 10 [source]

Here is an example of usage where forcing non-interactive operation works differently than letting the shell (try to) figure out there can be no interaction:

> sqlite3 -batch -cmd "select count(*) from sqlite_master;" <nul

> sqlite3 -cmd "select count(*) from sqlite_master;" <nul
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.


Little gotcha's like this are why I use -batch without attempting to figure out whether it is actually needed. (Sometimes, brain cycles are a scarce resource!)

(6) By L Carl (lcarlp) on 2020-07-06 15:10:45 in reply to 2 [link] [source]

OK, this is encouraging and I’ll definitely be trying this, but consider this scenario:

I often do testing by beginning a transaction and doing a rollback if things don’t work out the way I wanted/expected. If I am already inside of a sqlite3 session is there any way to run a parameterized script (like the count script I used as an example) that is able to see the uncommitted data?

(7) By Larry Brasfield (LarryBrasfield) on 2020-07-06 17:39:07 in reply to 6 [link] [source]

If I am understanding your question as you meant it, in the context of your original question: You want to be able, within a sqlite3 shell session, with a transaction open, enter something like

@count employee department

, (where "@count" stands for running a predefined query (or D{D,M}L statement) having numbered parameters to be bound to "employee" and "department", standing for literals.) Then you want to either abort or commit the transaction.

For that, my suggestions of a scripting language approach are not going to work, at least not in any simple way. (One can imagine using the .shell command, followed by a .read of something the system shell leaves behind, but that defeats the convenience purpose of that nice "@function param1 ..." invocation.)

A simple enhancement of the SQLite shell's .read command would accomplish that convenient nicety. If the FILE parameter to .read could optionally be "-cmd some_doer params ...", where that meant to run some_doer in the system shell with the given params, and pass its output into the SQLite shell as meta-commands and queries (just as .read does now with FILE content), it would provide close to the same convenience of the "@whatever params" construct. Or, a new meta-command doing that would reduce typing a tad.

As I said, such a modification would be relatively easy for an experienced C programmer.

(8) By L Carl (lcarlp) on 2020-07-07 16:31:42 in reply to 7 [link] [source]

Thanks. I haven’t done any C programming since 2001, but if I get up the energy maybe I’ll give it a shot. Meanwhile, being able to work only with committed data is worth a little shell scripting which is something I’ve done more recently, so I’ll definitely try that.

And thanks to the person who asked about the -batch option because I did not fully understand that either.