EX1: Creating Tables


#1

Hi all,

When I do

sqlite3 ex1.db -init ex1.sql

it doesn’t do anything. I can create the table just fine - if I run .schema, the layout pops up and everything, but when I type

sqlite3 ex1.db -init ex1.sql

It just creates a new row, like it’s prompting me to put in a semicolon:
>sqlite3 ex1.db -init ex1.sql (I hit enter)
>…>

then if I do enter a semicolon, I get:
>ERROR: near “ex1”: syntax error

So, two questions:

  1. What is sqlite3 ex1.db -init ex1.sql even doing? That was not really explained in the chapter.
  2. How do I execute the statement?

here’s the full statement:

PS C:\sqlite> .\sqlite3
SQLite version 3.24.0 2018-06-04 19:24:41
Enter “.help” for usage hints.
Connected to a transient in-memory database.
Use “.open FILENAME” to reopen on a persistent database.
sqlite> CREATE TABLE person(
…> id INTEGER PRIMARY KEY,
…> first_name TEXT,
…> last_name TEXT,
…> age INTEGER
…> );
sqlite> ex1.db -init ex1.sql
…> (I hit enter and nothing happens)
…> ;
Error: near “ex1”: syntax error
sqlite>


#2

Alright, you actually did it correctly, but since you’re on Windows you have to do it this way with the -init ex1.sql option. All it does is tell sqlite3 to load ex1.sql and then drop you into the sqlite3 shell. Notice how it says “sqlite>” after you do this?

Normally your shell says “PS C:\sqlite>” and you type commands there, but those commands are going to windows. When you run sqlite3 like this, it runs, loads the .sql file, and then gives you it’s command line with “sqlite>” that means anything you type is being sent to sqlite and not windows.

The mistake you made is to then type ex1.db -init ex1.sql, but that’s easy to make since it looks like you can type more Windows commands into there. What you can actually type there is SQL. Try this out:

sqlite> select * from person; // should show you nothing
sqlite> .schema
sqlite> .quit

Don’t type the “sqlite>” part, that’s just where the sqlite3.exe is prompting you for more input. You type the stuff after it. Remember also that my first command there shouldn’t show you anything yet, but as you continue in the book it will have you doing stuff.


#3

Wait so am I supposed to be starting with the -init command before I build my table, instead of starting with .\sqlite like I did above? Once the table is built I can do .schema .quit just fine, it just doesn’t save the table anywhere so I can’t reference again when I move on to the next section.

Edit: Yes, that seemed to work! Thank you. I got confused by the order of operations - I thought the -init command was something that you had to do after you built the table, not when you were entering the sqlite shell. I see ex1.db in my drive now.

PS C:> cd sqlite
PS C:\sqlite> .\sqlite3 ex1.db -init ex1.sql
SQLite version 3.24.0 2018-06-04 19:24:41
Enter “.help” for usage hints.
sqlite> CREATE TABLE person(
…> id INTEGER PRIMARY KEY,
…> first_name TEXT,
…> last_name TEXT,
…> age INTEGER);
sqlite> .schema
CREATE TABLE person(
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER);
sqlite> .quit
PS C:\sqlite>

Edit 2: OK now I feel really dumb…I realized that what the -init command was doing was running a saved script, I wasn’t supposed to be typing the damn thing directly into powershell (this is probably obvious to everyone else in the world, but I’m just going to document my stupidity for any other total newbies out there).

Anyway, I downloaded the Code Writer app for Windows 10 and created a .sql document called ex1a.sql. I put my person () table creation statements into there, then saved it in my sqlite3 folder with the other things I’ve created. Then I ran .\sqlite3 ex1a.db -init ex1a.sql, and it built the person () table.

In other news, I built and inputted a 12-line table of data directly through power shell before I realized I was supposed to do it this way…so that was an adventure.


#4

Looks like you got it. The -init is called an “option” or command line argument. They’re typically of two flavors:

  • setting a value equal to something, in which case you’ll see a word after it, in this case the name of your script. You can also see these like --init=“some thing”, depending on the program.
  • turning a flag on or off. In that case there’s nothing after it, so if you see -foo -bar athing then -foo is most likely turning an option on and off while -bar is setting the option bar to athing.