Sqlite3 prompting `...>` instead of `sqlite>`

Question

I'm following a beginners tutorial to sqlite3. The first step is creating a new database. So I enter a name (movies.db).

I'm expecting to get another sqlite> prompt on the next line, and continue with the tutorial, but instead I get a lame ...> after which I can type any gibbersish I want. Clearly, this is not good.

What my command prompt looks like:

SQLite version 3.8.1 2013-10-17 12:57:35
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> $ sqlite3 movies.db
   ...> gibberish
   ...> dsds
   ...> sdada
   ...> gfgys
   ...> a
   ...> Aaaaarrrgh!
   ...>

How do I get sqlite3 to work normally for me?

Pardon my newbie-ness. I hope I've phrased this question in a way that might help other newbs too.

Answer 1

Sqlite is working normally. However, the sqlite movies.db command should be issued from your system command line – not from the Sqlite interactive shell. Start by exiting the Sqlite interactive shell (.exit), then issuing the database creation command.

According to the quickstart documentation:

1. At a shell or DOS prompt, enter: "sqlite3 test.db". This will create a new database named "test.db". (You can use a different name if you like.)

2. Enter SQL commands at the prompt to create and populate the new database.

Once the sqlite movies.db command is properly executed from your system command line, you'll automatically be placed in the Sqlite interactive shell, which will be awaiting commands.

sqlite> create table tbl1(one varchar(10), two smallint);

The ...> shell prompt indicates a continuance from the preceding line. As indicated in the message, you'll need to terminate each database command with a ; semicolon.

sqlite> CREATE TABLE tbl2 (
   ...>   f1 varchar(30) primary key,
   ...>   f2 text,
   ...>   f3 real
   ...> );
sqlite>

Answer 2

Terminate the statement with a ;. So just hit ; then enter and it will go back to normal (after giving an error, because what you've typed here is bad sql).

What's going on is it thinks you are still working on something. It can be useful to break up long queries into lines like this:

sqlite> select title, description
   ...> from mytable
   ...> where id > 10;

And the ...> means it is waiting for you to finish your query, which you signify with the semicolon.

Answer 3

I constantly hit up arrow or left arrow and get into this below. I've found that only control-D works.

sqlite> ^[[A
   ...> '
   ...> ;
   ...> 
   ...> ;
   ...> 
   ...> 
   ...> 
   ...> 
   ...> ^C
   ...> ^X
   ...> 
   ...> 
   ...> 
   ...> 
   ...> 
   ...> 
   ...> ^E
   ...> ^R

   ...> ^T
   ...> ^Y
   ...> ^K
   ...> ^X
   ...> quit
   ...> '
   ...> ;
   ...> /
   ...> g
   ...> 
   ...> 
   ...> .exit
   ...> )
   ...> ;
   ...> /
   ...> ;
   ...> /
   ...> /
   ...> /
   ...> /
   ...> /
   ...> /
   ...> /
   ...> /
   ...> /
   ...> /
   ...> /
   ...> >
   ...> ;
   ...> /
   ...> '/
   ...> ;
   ...> ,
   ...> ;
   ...> ^[[D
   ...> /
   ...> .quit
   ...> ∂
'  ...> Error: incomplete SQL: 

Answer 4

If you enter of any of these by mistake - ^[[A^[[A^[[B, then simply press ];

Closing the bracket followed by a semicolon does the trick.

Answer 5

I got in the same state after hitting some arrow keys

sqlite> ^[[A^[[A^[[B
   ...> ;
   ... 30 more lines of randomly-typed unsuccessful characters ...
   ...> /
sqlite>

The forward-slash character '/' seemed to solve it for me.

Answer 6

(I know this is an old question but this still seems to be an issue with the SQLite command-line interface and I have had the same problem recently.)

...> is called the continuation prompt in the SQLite command-line interface.

There is no standard method to get out of it in all cases without also getting out of the interface itself if you got into it unintentionally. If you don't mind getting out of the interface as well, you can just press Ctrl+D.

If you wish to exit the continuation prompt at once but not the interface, first try simply typing a semicolon ; and pressing enter because all SQL statements are supposed to end with a semicolon in the command-line interface. Without the semicolon or a syntax error, the interface thinks you are continuing to enter a command.

It is also possible that the continuation prompt is triggered by a missing closing apostrophe ', quotation mark ", or a right square bracket ]. So type any of these at the continuation prompt followed by a semicolon ; and press enter.

If none of the above works, try triggering a quick syntax error by typing any character between two apostrophes or quotation marks followed by a semicolon such as 'x'; or "x"; or just a forward slash / and pressing enter.