Working with Multi-Line SQL Statements in SQLite

When you're working with SQLite's command-line interface (CLI), you typically see a prompt that says sqlite>. This prompt indicates that SQLite is ready for you to enter SQL commands. However, in certain situations, you might encounter a different prompt: ...>.

Why does the prompt change to ...>?

The ...> prompt signifies that SQLite is expecting you to continue a multi-line SQL statement. This happens when your SQL statement is too long to fit on a single line or requires continuation characters like backticks (`).

Here are some common scenarios that trigger the ...> prompt:

• Long SQL statements: If your SQL statement spans multiple lines, SQLite will display ...> on subsequent lines, waiting for you to complete the entire statement before executing it.
• **Backticks ():** Backticks are used to enclose identifiers (like table or column names) that might otherwise conflict with reserved keywords in SQLite. If you start an identifier with a backtick but don't close it on the same line, SQLite will enter the...>` prompt to wait for the closing backtick.
• String literals: String literals (text enclosed in single or double quotes) can also cause the ...> prompt if you don't close them on the same line.

How to exit the ...> prompt:

There are two ways to exit the ...> prompt and complete your SQL statement:

1. Complete the statement: Enter the remaining characters of your SQL statement, ensuring proper closure of backticks and string literals, and then press Enter to execute the entire statement.
2. Semicolon (;): If you don't want to execute the incomplete statement, simply type a semicolon (;) and press Enter. This will discard the unfinished statement and return you to the regular sqlite> prompt.

Example:

sqlite> CREATE TABLE users (
...     id INTEGER PRIMARY KEY,
...     name TEXT NOT NULL,
...     email TEXT UNIQUE
... );

In this example, the CREATE TABLE statement spans multiple lines, so SQLite displays ...> on subsequent lines until the closing parenthesis is entered.

sqlite> SELECT * FROM products
... WHERE category = 'Electronics'
... AND price > 100;

This example shows a SELECT statement that retrieves products from the products table where the category is Electronics and the price is greater than 100. Since it spans multiple lines, SQLite prompts with ...> on subsequent lines.

Unclosed Backtick:

sqlite> SELECT * FROM `user`
...  -- This line doesn't have the closing backtick

Here, the table name user is enclosed in backticks, but the backtick isn't closed on the same line. SQLite enters the ...> prompt, waiting for the closing backtick.

Unclosed String Literal:

sqlite> INSERT INTO messages (sender, message) VALUES ('John Doe',
... 'This is a long message that continues on the next line.');

In this example, the message string starts on one line but isn't closed with a single quote (') on the same line. SQLite enters the ...> prompt to wait for the closing quote.

Remember:

• To complete the statement, enter the missing characters on the ...> lines and press Enter.
• To discard the unfinished statement, type a semicolon (;) on a ...> line and press Enter.

1. sqlite> SELECT * FROM products WHERE category = 'Electronics' AND price > 100;
   

2. sqlite> SELECT * FROM products;
   sqlite> WHERE category = 'Electronics' AND price > 100;