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

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 2

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 3

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 4

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.