Order of results of multiple SELECT statements separated by ";"
(1.1) By Thiago Padilha (tpadilha) on 2021-04-01 11:24:47 edited from 1.0 [link] [source]
I'm writing a long-running script that interacts with SQLite via its command-line shell. To avoid spawning the "sqlite3" command every time I need to interact with SQLite, I'm keeping a single "sqlite3" process open for the duration of the script and sending queries to its stdin on-demand.
When sqlite3 stdin is connected to a pipe instead of a tty, it will not display a command prompt like "sqlite> ", so by default there's no way for the parent program to know when a query output has finished processing.
To work around that, I'm appending "; SELECT 1;" to the end of every statement sent to sqlite3's stdin, which will result in "1" being printed on stdout after each query.
So far in my tests this has worked well. My question: Is this is a guaranteed way of being notified when a statement has been processed or am I making some false assumptions?
(2) By Gunter Hick (gunter_hick) on 2021-04-01 11:32:49 in reply to 1.0 [link] [source]
The SQLite shell is a single threaded process, so it will return all of the rows for each query in full before even running the next query. Using a "guard query" to separate results form adjacent queries and to determine if the result set is complete and not just taking more time is a valid idea. I would only suggest you use something more distinctive than the constant 1, e.g. a string that contains the timestamp when the query was sent and maybe a name. And also to put the guard query on both sides of the actual query. That way, the script can - detect that the sqlite shell has started processing the query (it receives the guard value immediately) - ascertain that the results belong to a specific query (the guard value matches the expected value) - detect when the sqlite shell has completed processing the query (it receives a guard value instead of row data)
(3) By Thiago Padilha (tpadilha) on 2021-04-01 12:23:32 in reply to 2 [link] [source]
That's perfect, thanks for the extra suggestions. I can probably use "SELECT '$(uuid -1)'" as the "guard query".
(4) By Larry Brasfield (larrybr) on 2021-04-01 13:00:12 in reply to 3 [source]
One issue that could come up with your approach is that you are depending on the output stream from the SQLite shell to be flushed (rather than sometimes sitting in a not-full buffer) at the completion of each result output.
As now coded, the shell happens to do this, but I do not see that as something you can count on. The fflush is done as part of the logic related to getting a prompt out, whether or not a prompt is issued. That could easily change.
(5) By Thiago Padilha (tpadilha) on 2021-04-01 14:03:41 in reply to 4 [link] [source]
If that did happen, I would expect an option would be added to control this behavior, considering how SQLite dev team is concerned with backwards compatibility :)
(6) By anonymous on 2021-04-01 23:47:13 in reply to 1.1 [link] [source]
What I have done is to add
.print * (on a separate line) after the SQL statement(s). I used the
-quote option, so that results are quoted, so that the program can identify the type, and so that
.print * will not match the result of the query.