iPhone SDK Tutorial - Using SQL Lite Part I

IMPORTANT:
The following tutorial has been deprecated and should no longer be used for any purposes. To learn more on how to use SQLite click here

In this tutorial I will talk about how to access data using the SQL Lite database. In my last iPhone SDK tutorial, we learned how to create a table view where the data comes from an array. In this tutorial, we will access the data from the database and display it using a table view.

In this tutorial I will talk about how to access data using the SQL Lite database. In my last iPhone SDK tutorial, we learned how to create a table view where the data comes from an array. In this tutorial, we will access the data from the database and display it using a table view.

This is how the application will look like, I know it is not much but we will learn many things and I wanted to keep this tutorial a little short.

In this tutorial, you will learn how to

1. Create a database.
   
2. Package an existing database with the application.
3. Copy the database
4. Execute SQL commands
5. Close the database.

Create a Database
Let us start by creating a SQL Lite database with the following schema. To Learn more about SQL Lite visit www.sqlite.org.

Open up the command line interface and create a database by typing sqlite3 SQLNavigation.sql. The database file will be created in the PWD (Present Working Directory). Now we can execute SQL statements at the command prompt. In the database schema above we have two tables, parent table denoted by CoffeeHouse and a child table Coffee. Coffee table contains general information about the coffee that the coffee house sells.

Package an existing database with the application
Create a "Navigation-Based Application" and name it "SQLNavigation". To add this database in our project, click on Project -> Add to Project and select your database file and add it to the Resources folder. We have successfully added the database to the project.

The first we have to check for when the application finishes loading is, whether the database is present in the user's iPhone or not. If it is not then we have to copy it.

Copy the database
In applicationDidFinishLaunching method, before we make the window visible , we have to check if the database exists or not and then get the information from the database and display it in the table view. We only copy the database once, when the application is first launched. This happens in createDatabaseIfNeeded method.

We first get an object of type FileManager which we will use to access the file system. Then we will search for the user's document directory in a given domain. The third parameter is a "YES" because we want the complete path to the directory, which means if a ~ is found in the string it will replace it with the complete document path. As the function returns an array, we will get the first string in the array. We then create a complete path to the database file and check if the database exists or not. If the database does not exists then we copy the database from the resource bundle to the document directory.

Execute SQL Commands
To execute sql commands, first add the libsqlite3.0.dylib file to your project. This file is located under /Developer/Platforms/iPhoneOS.platform/Developer/SDKs/iPhoneOS2.0.sdk/usr/lib/. We also need to import the following header file sqlite3.h in the application delegate header file.

Now we will get a list of all the coffee shops and display it on the first screen. The user will be able to select a coffee shop and see all the coffees that a shop has. The user further can select a coffee and see its details. Let us first get a list of all the coffee shops and siaply it in our table view. We will get the data as soon as we have finished calling createDatabaseIfNeeded method.

We first get the path to where the database is located and open it using sqlite3_open method. This method returns 0 (or SQLITE_OK) if it is successful in opening the database. The connection object here is the variable database and like any other language we also have a command object of type sqlite3_stmt. sqlite3_prepare_v2 is a method by which we initialize the sql statement. After the method is executed the compiled statement is stored in the selectStatement variable. Just like we have some kind of an execute method, we will call sqlite3_step method to execute the sql statement and get a forward-read only reader. sqlite3_step returns SQLITE_ROW if there are no errors. It is then we get the data from the columns using either sqlite3_column_int or sqlite3_column_text methods.

We also have a class created called Coffee to hold the information we get from the database. We also have an array created in the header file of the delegate to keep track of the coffee objects. The coffee class contains two variables (for now) coffeeHouseID and coffeeHouseName, it also contains a method called initWithCoffeeHouseData which returns itself. This method is used by the application delegate to create an object of type coffee, later this object is added to the array.

In the implementation file of the Root View Controller, we need to tell the table view how many rows it should expect. We do this by getting a reference to the application delegate and then returning the count of the array.

We then get the same refernce again in cellForRowAtIndexPath to write the coffee house name to the text of the cell. We get a coffee object from the array and use the coffeeHouseName property.

Closing the Database
Application delegate receives an applicationWillTerminate message when the applicaton is closing. To release the resources appropriately, we should close the database connection here. We call the sqlite3_close messase to close the database and release its resources.

This concludes the part 1 of using SQL Lite databases in the iPhone applications. Read Part II of the tutorial here.

You can download the source code by clicking here and please let me know what you thought about this tutorial.