Beginner: keep getting ...>
I'm using Linux (Ubuntu 19.10) and just installed sqlite3 (version 3.29). I'm having trouble being a beginner and all. I often get this ...> prompt and the only ways I found to get out of it is either ctrl-D or ctrl-Z both of which close the app and I have to restart. Is there a way to get back to the prompt in sqlite without having to exit out of the app itself?
You get that prompt when you tap ENTER after an incomplete SQL statement. It's waiting for you to finish inputting the statement (including the trailing semicolon). To complete or (depending on the context) cancel it, tap the semicolon and tap enter.
The OP might have also fogotten to close a quote. Quotation marks must appear in pairs. If you "forget to close the quote you can put all the ; you wish and nothing will do any ; good whatsoever ; until you close the quote" first;
This applies to 'text' quotes as well as "identifier" quotes.
Note that this applies to other "identifier quotes" which must occur in opening and closing pairs, such as square brackets
 and tick-quotes (grave accent marks) ``
Note that this does not apply to syntactic elements such as parenthesis (), which only must appear in pairs to create a syntactically valid statement but do not need to appear in proper-syntax-pairs to form a statement.
That is, quotes are semantic elements and must be closed in order for the statement to be complete. Semantic quotes are single-quote, double-quote, square brackets, and tick-quotes (grave accent symbol).
I can understand why the shell would want to remain agnostic about the syntax of SQL, but since it's told when a command it incomplete, it could be made to check for unbalanced quotes and issue a warning. Maybe the check could only be made if the last character is a semi-colon.
(7) By Larry Brasfield (LarryBrasfield) on 2020-06-29 15:17:35 in reply to 6 [source]
But the shell is not told when a command is complete. When accepting input that is not a meta-command's single line, it relies on the lexer and parser to help spot the end of the arbitrary SQL construct.
Distinguishing between a semicolon that happens to be embedded in one of the quoted token types and a semicolon that is properly at the end of a line outside of any (other) token would require replicating the lexical analyzer's logic. This is not really the shell's job. True, it could be done, but separation of concerns and modularity considerations weigh against doing it.
That's the job of https://www.sqlite.org/c3ref/complete.html.
The real issue is that it's not enough. Most people like to indent
their WHERE clause on the next line, and
select * from t is a complete
statement, yet when I press return, I don't want it to run, but continue
typing in my WHERE or GROUP-BY or whatever else needs typing.
That's why the semi-column is mandatory. Yes sometimes I forget,
but then I just type the semi-colon on its own line, and that's it.
(9) By Larry Brasfield (LarryBrasfield) on 2020-06-29 21:02:47 in reply to 8 [link] [source]
Good points. I believe the lexer is put to work behind sqlite3_complete()'s operation.
The shell presently relies on sqlite3_complete() to help determine when to process the input instead of merely continuing to collect it. And it demands a terminating semicolon. So Mr. Jones' suggestion is already effected as much as it reasonably can be (in my opinion).
Well, actually, no, it does not.
sqlite> select ...> 1 ...> ; select ...> 2 ...> ; ┌───┐ │ 1 │ ├───┤ │ 1 │ └───┘ ┌───┐ │ 2 │ ├───┤ │ 2 │ └───┘
The last line is a ; followed by a buggyfull of spaces, so the ; is not a "terminating" symbol. It is the last symbol of significance, but it is not a "trailing semicolon".
(12) By Larry Brasfield (LarryBrasfield) on 2020-06-29 22:24:44 in reply to 11 [link] [source]
As far as I can see, your example does not contradict my assertion. I said "[the shell] demands a terminating semicolon", which your example provides.
As it turns out, for non-meta-commands, the shell demands termination by either a semicolon or the word 'go'. For example, modifying your example:
sqlite> select ...> 1 ...> ; select ...> 2 ...> go 1 1 2 2 sqlite>
Yes, apparently the Sybase CLI go (later co-opted by others including Microsoft) which means "go process the batch of commands", is recognized as is apparently an Oraclesque /
Who knew (I didn't)?
sqlite> select ...> 1 ...> ; select ...> 2 ...> / ┌───┐ │ 1 │ ├───┤ │ 1 │ └───┘ ┌───┐ │ 2 │ ├───┤ │ 2 │ └───┘
Of course, the semicolon is the statement separator recognized by the parser internally whereas go and / are unique to the shell.
Rather than prematurely running a looks-complete-but-missing-the-semicolon statement, I wonder if there's scope for the shell's continuation prompt to simply offer a hint about what it's waiting for.
By way of example, PHP's interactive shell handles this quite nicely by changing the
> of the prompt to one of
( whenever there's a string, block or parenthesis left open at the end of the previous line. It still waits for everything to be closed properly before any code will run, but the user gets a clue that they've miscounted their brackets.
But I know very little about SQLite's internals, so I've got no idea how easy/practical it would be implement something like this for the shell; I'm just throwing the idea out there in case anyone's interested.