Simple SQLite Tutorial

We are going to create a simple app that gets data from an SQLite database. We will be using SQLite's C API to do this. As I said, this tutorial will keep things simple. Our app will just be displaying data, it won't be inserting data in the database, at least not yet in this tutorial.

So let's get started.


SQLite is a lightweight database that is embedded in the iPhone. And, it is already present in our macs, and we can easily use it through the terminal. All we need is a few SQL know how.


Open terminal.


Type this command: sqlite3 players.sql


This runs sqlite3 and creates a database named players.sql.

Now lets create a table named player that has 3 columns, id, name and team. Id is an integer that uniquely identifies the player entry, this is our primary key. The name would be the name of an NBA player and the Team would be the team name he plays for.

Type: CREATE TABLE player (id INTEGER PRIMARY KEY, name VARCHAR, team VARCHAR);


Note: Don't forget the semicolon. SQL statements should have them at the end.
This creates our player table with the corresponding 3 columns I mentioned.


So, its time to put entries into our database table.


Type: INSERT INTO player (name, team) VALUES ('Lebron James', 'Miami Heat');
This adds an entry with name of 'Lebron James' and team 'Miami Heat'.


Add more entries to our table:

What we are doing here is just adding more rows to our table, go ahead and add some more if you like.

After this open xcdoe > start new project > window based application. Name it players.

First thing we do when we use SQLite3 in our project is to add both our database in our resources folder and add the SQLite framework. 

Copy players.sql from the folder you saved it into. I happened to save mine at the Desktop, as seen from the picture above. Copy it into your resources folder, check "Copy items into destination's folder". (I added my database this way, but I actually , as did a lot of other people in some coding forums, had problems, because the database that was added was empty. I will discuss the remedy to this later).

After copying our database file lets add our sqlite3 framework. Go to our frameworks folder, right click >add > existing frameworks...>libsqlite3.0.dylib.

Now lets go to our code. Edit playersViewController.h:

Here, we import sqlite3.h. Declare an sqlite3 object that will be our database handle. We also declare 2 other ivars: an NSMutableDictionary, which we will use to hold a row of players data. The keys of the dictionary would be name and team, and the values would be the player's name and his team. This dictionary would then be stored in an array. Such that every object in the array would correspond to one row in the database. We would need these ivars to populate our table view later on.

And oh, dont forget to make playersViewController a subclass of UITableViewController.

Also, declare a conveniece method - (void)loadDatabase;

Now lets go to our playersViewController.m:

Here we implement the first part of our convenience method. First we get the path to the players.sql we added earlier to our file. We do this by using the NSSearchPathForDirectories function. The first argument means we are looking for our Documents folder. Here are the other Directories in there:

Now, the function would return to us an Array, but it only has one object in it. So we grab that object using objectAtIndex:0, which returns us an NSString. Then we append the path component players.sql so we have the full directory path to players.sql.  

Then we use the next lines of code to see whether our players.sql is exactly in our directory by using

[fileManager fileExistsAtPath:databaseDirectory]. We log our result in the console, with the exact directory of the database. We do this so we can remedy the problem with potentially adding an empty database file. In my experience my players.sql was added to the directory address but it was empty, it didnt have the player table nor the columns and the data I inserted into it. So as a remedy, using finder, navigate to the directory path logged in the console and copy paste our players.sql into that folder. This guarantees that we added a players.sql that is not empty.

Now, lets continue to our loadDatabase method:

First we open our database, using sqlite3_open(). First argument is the databaseDirectory we created earlier, we convert it to a C string using UTF8String. Second argument is our database handle. The  if just checks whether or not we opened the database successfully. If we failed we just close our database (standard procedure accdg. to SQLite docs) and log our error message.

After successfully opening the database, we declare our SQL statement, which will retreive all entries from our table. Next we declare sqlite3_stmt object which is an SQLite3 binary compiled statement.

Next we compile our SQL statement into a byte-code program (our sqlite3_stmt) using sqlite3_prepare_v2.

The first argument is a database connection obtained from a prior successful call to sqlite3_open(). The second argument is the statement to be compiled, encoded as UTF-8. The third argument is the maximum length of sql statement in bytes, when we pass a number less than zero, then sql is read up to the first zero terminator. Next argument is our statement handle, our sqlite3_stmt object. Last argument is the pointer to unused portion of Sql, but since it is read up to the first zero terminator, we will pass in NULL.

After a prepared statement has been prepared using either sqlite3_prepare_v2,  the sqlite3_step() function must be called one or more times to evaluate the statement. If the function returns SQLITE_ROW means that there is another row of data ready.

We then get that row of data, by using the sqlite3_column_text(), where we pass our compiled statement and the column number we want to access. First column is 0 second is 1, much like an array. Our name column is the second column in our table so we use 1. Similarly, team is the third, so in the next line we use 2.

This function returns a C string, so we turn it to NSString using stringWithUTF8String method. Then we store the playerName and playerTeam strings in our dictPlayers dictionary, which holds the data for an entire row. We then add an entire row's data to our arrayPlayers. Which holds all the rows of data from the database. Easy right? We then release the dictPlayers, so that when the while loop starts again for the next row, we will start with a blank dictPlayers dictionary.

We then finalize our statement after stepping thru all our rows to release the resources we used in compiling it.

Now, we have effectively retreived our data from an SQLite database and turned them into objective c objects (our NSMutableArray of NSMutableDictionaries of NSStrings).

Lastly, lets do some basic table view stuff, so our data shows up in a table view.

First we implement the tableView:numberOfRowsInSection: method and give it the number of objects in our arrayPlayers. Then we implement tableView:cellForRowAtIndexPath:, where we get a particular player's data from our arrayPlayer. We then put it in aPlayer. Then we extract the object for key @"name" and @"team", which we set as our cell's textLabel and detailTextLabel respectively. That should take care of our table view. 

Now, its time to add our table view to our view. Go to playersAppDelegate.m:

Thats all there is to it. Build and Run. You should see the name and the team of our players in the table view! Yey! 

For questions and suggestions hit the comments. Thanks!