Ok, here we are, already at the point where we're ready to start adding an SQLlite database. As I mentioned in the last post, there is an XMLReader class which parses XML and loads up the questions. Our goal is not to replace that with something which reads an XMLLite database which contains the same information. This database already exists - nice! It was created for the Android App, and which we can now leverage for this new, iOS app.
So, let's go ahead and google it. What else?
How does this sound:
An Example SQLite based iOS 4 iPhone Application
http://www.techotopia.com/index.php/An_Example_SQLite_based_iOS_4_iPhone_Application
About the Example SQLite iPhone Application
The focus of this chapter is the creation of a somewhat rudimentary iPhone iOS application that is designed to store contact information (names, addresses and telephone numbers) in a SQLite database. In addition to data storage, a feature will also be implemented to allow the user to search the database for the address and phone number of a specified contact name. Some knowledge of SQL and SQLite is assumed throughout the course of this tutorial. Those readers who are unfamiliar with these technologies in the context of iPhone application development are encouraged to first read the previous chapter before proceeding.
So far, so good.
Creating and Preparing the SQLite Application Project
Begin by launching the Xcode environment and creating a new iOS iPhone View-based application project called database. Once the project has been created, the next step is to configure the project to include the SQLite dynamic library (libsqlite3.dylib) during the link phase of the build process. Failure to include this library will result in build errors.
To include this library in the build directives, hold down the Ctrl key and click with the mouse on the Frameworks entry of the Groups and Files panel located on the left hand side of the Xcode project window. From the resulting menu, select the Add -> Existing Frameworks… option. Within the list of framework options, scroll down to the libsqlite3.dylib entry, select it and click on the Add button.
Let's give it a try...
Hmm...no add option.
It might be worth it to sort of just do a tutorial application which includes a database before modifying one. This will give us a little extra practice with creating an iOS app, and sandbox the app development.
Well, everything still seems to be Xcode3
Ok, here's a way to add sqlite using Xcode4.
http://www.ajaxapp.com/2011/08/30/how-to-add-sqlite-framework-into-xcode-4-for-your-ios-app/
Unfortunately, the above-said Xcode 3 way of doing it is no more available on Xcode 4. On Xcode 4, you’ll have to do the following:
1. Click on your project name in your left pane of Xcode 4. You’ll be shown with the above screen.
2. Then click on “Build Phases”, then get down to “Link Binary With Libraries”, and click the + button and add your SQlite framework (libsqlite3.0.dylib) as shown in the next screen. Voila, then you’re good to go to compile some code using SQLite functions.
Ok, great. That seems to work.
How to proceed now?
Let's try this tutorial:
http://www.icodeblog.com/2008/08/19/iphone-programming-tutorial-creating-a-todo-list-using-sqlite-part-1/#nav-based
If you have been following my tutorials, you know that we have been working primarily with UITableViews. This is mostly because SO many applications can be developed using this simple control. This final UITableView tutorial will be taking all of the skills learned from previous tutorials, putting them all together, and adding SQLite to create a prioritized To-Do list. I will also be showing you how to add multiple columns to your table cells and we will be exploring some of the other controls that the iPhone has to offer. What good would the tutorials be if we didn’t use them to create something useful.
I will move a little faster in this tutorial while still explaining the new stuff in detail. I will assume that you have completed the fruits tutorial and it’s prerequisites.
This tutorial will be a multipart series as it will be a little longer than my previous ones. In this first tutorial, you will learn:
Create a NavigationBased Application
Create a Database
Add the Database to Your Project
Add the SQLite3 Framework
Create a Todo Class Object
Initialize the Database
So let’s get started…
Open up X-Code and Select File->New Project… Select Navigation-Based Application and click Choose…
Hmm...navigation based. That's cool. I think I've started out with view-based in the past.
Name your project todo. Now let’s create the todo database that we will be using.
That's ok, I just had to put it in my Appdev/iphone folder. The project name is todo, and the whole thing with the package name is now called com.mycompany.todo.todo.
Open up the Terminal application on your Mac. This is located in Applications > Utilities.
Ok, no problem. I've done this about a million times.
If you have installed XCode, you should have mysqlite3 already on your computer. To check this, type:
sqlite3 into the Terminal and sqlite3 should start. Type .quit to exit. If sqlite3 is not installed, install all of the XTools from your Mac Installation Disk.
".q" will get you out, too.
Now that the terminal is open let’s create the database. This is done with the command:
sqlite3 todo.sqlite
SQLite3 will now start and load the todo.sqlite database. By default the database is empty and contains no tables. If you need a refresher on the basics of SQL databases Google It.
It's a good idea to pull your head up and explore a touch, every once in a while. If I do a ".help", it shows me all the commands available. That shows that if you do a ".databases" command, you'll see all the available databases. And sure enough, our "todo.sqlite" shows up. Nice! It's fun to learn.
S
ince our application is fairly simple, we only need to create one table. We will create a table called todo by typing the following statement:
CREATE TABLE todo(pk INTEGER PRIMARY KEY, text VARCHAR(25), priority INTEGER, complete BOOLEAN);
One thing to note here is the pk field. It is the primary key of the table. This adds functionality such that every time a row is added to the database, it auto-increments this field. This will be a unique identifier to identify each row. All of the other fields should be fairly self explanitory.
Ok, great. Cut an paste the command. And, a ".schema" command shows the record of the create statement:
.schema
CREATE TABLE todo(pk INTEGER PRIMARY KEY, text VARCHAR(25), priority INTEGER, complete BOOLEAN);
Now that our table has been created, let’s add some data. We will eventually be adding todo items within our app, but for now we will add some defaults. Type the following commands below.
INSERT INTO todo(text,priority,complete) VALUES('Take out the trash',3,0);
INSERT INTO todo(text,priority,complete) VALUES('Do Computer Science homework',1,0);
INSERT INTO todo(text,priority,complete) VALUES('Learn Objective C',1,0);
INSERT INTO todo(text,priority,complete) VALUES('DIGG this tutorial',2,0);
Cool. You can paste them all in at once, and the show what's in the table:
sqlite> INSERT INTO todo(text,priority,complete) VALUES('Take out the trash',3,0);
sqlite> INSERT INTO todo(text,priority,complete) VALUES('Do Computer Science homework',1,0);
sqlite> INSERT INTO todo(text,priority,complete) VALUES('Learn Objective C',1,0);
sqlite> INSERT INTO todo(text,priority,complete) VALUES('DIGG this tutorial',2,0);
sqlite> select * from todo;
1|Take out the trash|3|0
2|Do Computer Science homework|1|0
3|Learn Objective C|1|0
4|DIGG this tutorial|2|0
sqlite>
You can add as many todo items as you would like. For this tutorial, make sure you enter a priority between 1 and 3 (You’ll see why later). Now our database has been created and populated let’s exit out of SQLite3. Do this by typing .quit. Your terminal window should look something like this.
(pic omitted)
Ok, great. Let's keep going.
Now go back to XCode. Do a Control-Click (right click) on the folder named Resources. Click Add -> Existing Files… and browse to your todo.sqlite file and click Add. It will then prompt you with a screen like this.
Oops - no resources on this new app. Back to google!
Ok, here's another link - it looks like the same one we started out with, but I think it's right on the adding the library this time. Anyway, it gives an intro to sqlite similar to what we just walked through. Then it introduces the iOS code for dealing with sqlite databases:
http://www.techotopia.com/index.php/IOS_4_iPhone_Database_Implementation_using_SQLite
When running an iPhone application in the iOS Simulator environment, any database files will be created on the file system of the computer on which the simulator is running. This has the advantage that you can navigate to the location of the database file, load it into the sqlite3 interactive tool and perform tasks on the data to identify possible problems occurring in the application code. If, for example, an application creates a database file named contacts.db in its documents directory, the file will be located on the host system in the following folder:
/Users//Library/Application Support/iPhone Simulator//Applications//Documents
Where is the login name of the user running the iOS Simulator session, is the version of the iOS SDK used to build the application and is the unique ID of the application.
Ok, good to know. I'll most likely use finder anyway.
Preparing an iOS 4 Application Project for SQLite Integration
By default, the Xcode environment does not assume that you will be including SQLite in your application. When developing SQLite based applications a few additional steps are required to ensure the code will compile when the application is built. Firstly, the project needs to be configured to include the libsqlite3.dylib dynamic library during the link phase of the build process. To achieve this, hold down the Ctrl key and click with the mouse on the Frameworks entry of the Groups and Files panel located on the left hand side of the Xcode project window. From the resulting menu, select the Add -> Existing Frameworks… option. From the list of framework options, scroll down to the libsqlite3.dylib entry, select it and click on the Add button.
Secondly, the sqlite3.h include file must be imported into any files where references are made to SQLite definitions, declarations or functions. This file in located in the /usr/include directory and may be imported when needed as follows:
#import "/usr/include/sqlite3.h"
Well, it's still showing the wrong way to add the dyanlib, so this is probably useless to. But, we're gradually gaining knowledge. Let's continue.
Key SQLite Functions
When implementing a database using SQLite it will be necessary to utilize a number of C functions contained within the libsqlite3.dylib library. A summary of the most commonly used functions is as follows:
sqlite3_open() - Opens specified database file. If the database file does not already exist, it is created.
sqlite3_close() - Closes a previously opened database file.
sqlite3_prepare_v2() - Prepares a SQL statement ready for execution.
sqlite3_step() - Executes a SQL statement previously prepared by the sqlite3_prepare_v2() function.
sqlite3_column_() - Returns a data field from the results of a SQL retrieval operation where is replaced by the data type of the data to be extracted (text, blob, bytes, int, int16 etc).
sqlite3_finalize() - Deletes a previously prepared SQL statement from memory.
sqlite3_exec() - Combines the functionality of sqlite3_prepare_v2(), sqlite3_step() and sqlite3_finalize() into a single function call.
This, of course, represents only a small subset of the complete range of functions available with SQLite. A full list can be found at http://www.sqlite.org/c3ref/funclist.html.
Ok, a bit of an overview of the available statements. exec will certainly come in handy.
Declaring a SQLite Database
Before any tasks can be performed on database, it must first be declared. To do so it is necessary to declare a variable that points to an instance of a structure of type sqlite3 (the sqlite3 structure is defined in the sqlite3.h include file). For example:
sqlite3 *contactDB; //Declare a pointer to sqlite database structure
Ok, good. Nothing crazy about that.
Opening or Creating a Database
Once declared, a database file may be opened using the sqlite3_open() function. If the specified database file does not already exist it is first created before being opened. The syntax for this function is as follows:
int sqlite3_open(const char *filename, sqlite3 **database);
In the above syntax, filename is the path to the database file in the form of a UTF-8 character string and database is the reference to the database structure. The result of the operation is returned as an int. Various definitions for result values are defined in the include file such as SQLITE_OK for a successful operation.
For example, the code to open a database file named contacts.db in the Documents directory of an iPhone application might read as follows. Note that the code assumes an NSString variable named databasePath contains the path to the database file:
sqlite3 *contactDB; //Declare a pointer to sqlite database structure
const char *dbpath = [databasePath UTF8String]; // Convert NSString to UTF-8
if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
{
//Database opened successfully
} else {
//Failed to open database
}
The key point to note in the above example is that the string contained in the NSString object was converted to a UTF-8 string before being passed to the function to open the database. This is a common activity that you will see performed frequently when working with SQLite in Objective-C
It would be better to show the declaration of databasePath, but ok.
Preparing and Executing a SQL Statement
SQL statements are prepared and stored in a structure of type
sqlite3_stmt using the
sqlite3_prepare_v2() function.
For example:
sqlite3_stmt *statement;
NSString *querySQL = @"SELECT address, phone FROM contacts”;
const char *query_stmt = [querySQL UTF8String];
if (sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
{
//Statement prepared successfully
} else {
//Statement preparation failed
}
A prepared SQL statement may subsequently be executed using a call to the sqlite3_step() function, passing through the sqlite3_stmt variable as the sole argument:
sqlite3_step(statement);
sqlite3_finalize(statement);
Note that the sqlite3_step() function also returns a result value. The value returned, however, will depend on the nature of the statement being executed. For example, a successful insertion of data into a database table will return a SQLITE_OK result, whilst the successful retrieval of data will return SQLITE_ROW.
Alternatively, the same results may be achieved with a single call to the sqlite3_exec() function as illustrated in the following section.
This is all good info. Let's keep going.
Creating a Database Table
Database data is organized into tables. Before data can be stored into a database, therefore, a table must first be created. This is achieved using the SQL CREATE TABLE statement. The following code example illustrates the creation of a table named contacts, the preparation and execution of which is performed using the sqlite3_exec() function:
const char *sql_stmt = "CREATE TABLE IF NOT EXISTS CONTACTS (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, ADDRESS TEXT, PHONE TEXT)";
if (sqlite3_exec(contactDB, sql_stmt, NULL, NULL, &errMsg) == SQLITE_OK)
{
// SQL statement execution succeeded
}
Ok, although we don't really need to create a table.
Extracting Data from a Database Table
Those familiar with SQL will be aware that data is retrieved from databases using the SELECT statement. Depending on the criteria defined in the statement, it is typical for more than one data row to be returned. It is important, therefore, to learn how to retrieve data from a database using the SQLite C function calls.
As with previous examples, the SQL statement must first be prepared. In the following code excerpt, a SQL statement to extract the address and phone fields from all the rows of a database table named contacts is prepared:
sqlite3_stmt *statement;
NSString *querySQL = @"SELECT address, phone FROM contacts”;
const char *query_stmt = [querySQL UTF8String];
sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL);
Ok, I'm liking this. These statements are the key to using sqlite in an iOS app.
The statement subsequently needs to be executed. If a row of data matching the selection criteria is found in the database table, the sqlite3_step() function returns a SQLITE_ROW result. The data for the matching row is stored in the sqlite3_stmt structure and may be extracted using the sqlite3_column_() function call, where is replaced by the type of data being extracted from the corresponding field of data. Through the implementation of a while loop, the sqlite3_step() function can be called repeatedly to cycle through multiple matching rows of data until all the matches have been extracted. Note that the sqlite_column_() function takes as its second argument the number of the column to be extracted. In the case of our example, column 0 contains the address and column 1 the phone number:
while (sqlite3_step(statement) == SQLITE_ROW)
{
NSString *addressField = [[NSString alloc] initWithUTF8String:
(const char *) sqlite3_column_text(statement, 0)];
NSString *phoneField = [[NSString alloc] initWithUTF8String:
(const char *) sqlite3_column_text(statement, 1)];
// Code to do something with extracted data here
[phoneField release];
[addressField release];
}
sqlite3_finalize(statement);
Ok. I would be interested in how to handle numeric data.
Closing a SQLite Database
When an application has finished working on a database it is important that the database be closed. This is achieved with a call to the sqlite3_close() function, passing through a pointer to the database to be closed: sqlite3_close(contactDB);
Summary
In this chapter we have looked at the basics of implementing a database within an iPhone application using the embedded SQLite relational database management system. In the next chapter we will put this theory into practice and work through an example that creates a functional iPhone application that is designed to store data in a database.
Ok. Good. We've learned something. We'll tackle the next chapter in the next post. Let's take quick break, and get right back to it.
