View source for An Example SQLite based iPhone Application

<table border="0" cellspacing="0">
<tr>
<td width="20%">[[iPhone Database Implementation using SQLite|Previous]]<td align="center">[[iPhone App Development Essentials|Table of Contents]]<td width="20%" align="right">[[Working with iPhone Databases using Core Data|Next]]</td>
<tr>
<td width="20%">iPhone Database Implementation using SQLite<td align="center"><td width="20%" align="right">Working with iPhone Databases using Core Data</td>
</table>
<hr>


<htmlet>ios9_upgrade</htmlet>


In the chapter entitled iPhone Database Implementation using SQLite the basic concepts of integrating a SQLite based database into iPhone applications were discussed. In this chapter we put this knowledge to use by creating a simple example application that demonstrates some of concepts involved in SQLite based database implementation and management on the iPhone.

== About the Example SQLite iPhone Application ==

The focus of this chapter is the creation of a somewhat rudimentary iPhone 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.

== Creating and Preparing the SQLite Application Project ==

Begin by launching the Xcode environment and creating a new 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.

== Importing sqlite3.h and Declaring the Database Reference ==

Before we can create a database we need to declare a variable pointer to a structure of type sqlite3 that will act as the reference to our database. Since we will be working with the database in the view controller for our application the best place to declare this variable is in the ''databaseViewController.h'' file. Since we also need to import the sqlite3.h header file into any files where we make use of SQLite this is also an ideal place to include the file. Within the main Xcode project window, select the ''databaseViewController.h'' file and modify it as follows:

<pre>
#import <UIKit/UIKit.h>
#import "/usr/include/sqlite3.h"

@interface databaseViewController : UIViewController {
        sqlite3 *contactDB;
}
</pre>

== Creating the Outlets and Actions ==

The application user interface is going to consist of three text fields (for the name, address and phone number), a label object to display the status of the database activities, a Save button and a Find button. When clicked, the save button will write the data entered into the text fields into a database table. The find button, on the other hand, will search the database for a row that matches the currently entered contact name and display the address and phone number for the matching record in the address and phone text fields.

Select the ''databaseViewController.h'' file once again and further modify the interface file to add the appropriate outlets and actions:

<pre>
#import <UIKit/UIKit.h>
#import "/usr/include/sqlite3.h"

@interface databaseViewController : UIViewController {
        UITextField     *name;
        UITextField *address;
        UITextField *phone;
        UILabel *status;
        NSString        *databasePath;

        sqlite3 *contactDB;
}
@property (retain, nonatomic) IBOutlet UITextField *name;
@property (retain, nonatomic) IBOutlet UITextField *address;
@property (retain, nonatomic) IBOutlet UITextField *phone;
@property (retain, nonatomic) IBOutlet UILabel *status;
- (IBAction) saveData;
- (IBAction) findContact;
@end
</pre>

With the outlets and actions declared in the interface file, edit the databaseViewController.m file and add the @synthesize directives for the outlets and the template methods for the saveData and findContact actions:

<pre>
#import "databaseViewController.h"

@implementation databaseViewController
@synthesize name, address, phone, status;

- (void) saveData
{
}

-(void) findContact
{
}
.
.
.
@end
</pre>

== Releasing Memory ==

Having allocted memory in the implementing the above outlets, it is important that we add code to free up any resources that were allocated during execution of the application. To do so, edit the coreDataViewController.m file again and modify the viewDidUnload and dealloc methods as follows:

<pre>
- (void)viewDidUnload {
        // Release any retained subviews of the main view.
        // e.g. self.myOutlet = nil;
        self.name = nil;
        self.address = nil;
        self.phone = nil;
        self.status = nil;
}

- (void)dealloc {
        [name release];
        [address release];
        [phone release];
        [status release];
        [super dealloc];
}
</pre>

== Creating the Database and Table ==

When the application is launched it will need to check whether the database file already exists and, if not, create both the database file and a table within the database in which to store the contact information entered by the user. The code to perform this task can be placed in the viewDidLoad method of our view controller class. Select the databaseViewController.m file, scroll down to the viewDidLoad method and remove the comment markers from around it (/* and */). Edit the code for this method as follows:

<pre>
- (void)viewDidLoad {
        NSString *docsDir;
        NSArray *dirPaths;

        // Get the documents directory
        dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

        docsDir = [dirPaths objectAtIndex:0];

        // Build the path to the database file
        databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"contacts.db"]];

        NSFileManager *filemgr = [NSFileManager defaultManager];

        if ([filemgr fileExistsAtPath: databasePath ] == NO)
        {
		const char *dbpath = [databasePath UTF8String];

                if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
                {
                        char *errMsg;
                        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)
                        {
                                status.text = @"Failed to create table";
                        }

                        sqlite3_close(contactDB);

                } else {
                        status.text = @"Failed to open/create database";
                }
        }

        [filemgr release];
        [super viewDidLoad];
}
</pre>
<htmlet>adsdaqbox_flow</htmlet>
The code in the above method performs the following tasks:

* Identifies the application’s Documents directory and constructs a path to the contacts.db database file.
* Creates an NSFileManager instance and uses it to detect if the database file already exists.
* If the file does not yet exists it converts the path to a UTF-8 string and creates the database via a call to the SQLite sqlite3_open() function, passing through a reference to the contactDB variable declared previously in the interface file.
* Prepares a SQL statement to create the contacts table in the database.
* Reports the success or failure of the operation via the status label.
* Closes the database and performs a memory clean up.

== Implementing the Code to Save Data to the SQLite Database ==

The saving of contact data to the database is the responsibility of the saveData action method. This method will need to open the database file, extract the text from the three text fields and construct and execute a SQL INSERT statement to add this data as a record to the database. Having done this, the method will then need to close the database. 

In addition, the code will need to clear the text fields ready for the next contact to be entered, and update the status label to reflect the success or otherwise of the operation.

In order to implement this behavior, therefore, we need to modify the template method created previously as follows:

<pre>
- (void) saveData
{
        sqlite3_stmt    *statement;

        const char *dbpath = [databasePath UTF8String];

        if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
        {
                NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO CONTACTS (name, address, phone) VALUES (\"%@\", \"%@\", \"%@\")", name.text, address.text, phone.text];

                const char *insert_stmt = [insertSQL UTF8String];

                sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL);
                if (sqlite3_step(statement) == SQLITE_DONE)
                {
                        status.text = @"Contact added";
                        name.text = @"";
                        address.text = @"";
                        phone.text = @"";
                } else {
                        status.text = @"Failed to add contact";
                }
                sqlite3_finalize(statement);
                sqlite3_close(contactDB);
        }
}
</pre>

The next step in our application development process is to implement the action for the find button.

== Implementing Code to Extract Data from the SQLite Database ==

As previously indicated, the user will be able to extract the address and phone number for a contact by entering the name and touching the find button. The Touch Up event of the find button will be connected to the findContact method, the code for which is outlined below:

<pre>
- (void) findContact
{
     const char *dbpath = [databasePath UTF8String];
     sqlite3_stmt    *statement;

     if (sqlite3_open(dbpath, &contactDB) == SQLITE_OK)
     {
             NSString *querySQL = [NSString stringWithFormat: @"SELECT address, phone FROM contacts WHERE name=\"%@\"", name.text];

             const char *query_stmt = [querySQL UTF8String];

             if (sqlite3_prepare_v2(contactDB, query_stmt, -1, &statement, NULL) == SQLITE_OK)
             {
                     if (sqlite3_step(statement) == SQLITE_ROW)
                     {
                             NSString *addressField = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 0)];
                             address.text = addressField;

                             NSString *phoneField = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
                             phone.text = phoneField;

                             status.text = @"Match found";

                             [addressField release];
                             [phoneField release];
                     } else {
                             status.text = @"Match not found";
                             address.text = @"";
                             phone.text = @"";
                     }
                     sqlite3_finalize(statement);
             }
             sqlite3_close(contactDB);
     }
}
</pre>

This code opens the database and constructs a SQL SELECT statement to extract any records in the database that match the name entered by the user into the name text field. The SQL statement is then executed. A return value of SQLITE_ROW indicates that at least one match has been located. In this case the first matching result data is extracted, assigned to NSString objects and displayed in the appropriate text fields. As an alternative, a while loop could have been constructed to display all matching results. For the purposes of keeping this example simple, however, we will display only the first match. The code then updates the status label to indicate whether a match was found, releases memory where necessary and closes the database.

== Designing the User Interface ==
<google>IOSBOX</google>
The final step in developing our example SQLite iPhone application involves the design of the user interface. In the main Xcode project window, double click on the ''databaseViewController.xib'' file to launch the Interface Builder tool. If the Library window is not visible, invoke it by selecting ''Tools -> Library'' and then drag and drop components onto the canvas and edit properties so that the layout appears as illustrated in the following figure:
 

[[Image:iphone_sqlite_example_layout.jpg|The user interface of the iPhone SQLite example applications]]


Before saving the user interface design, stretch the status label (located above the two buttons) so that it covers most of the width of the view as show above. Finally, edit the label and remove the word “Label” so that it is blank.

Next, connect the three text fields and status label to the name, address, phone and status outlets respectively by holding down the Ctrl key and clicking and dragging from the components in view to the ''File’s Owner'' entry in the documents window. From the resulting menu select the outlet corresponding to the selected view object.

The last step involves connecting the two buttons to the corresponding actions. First, display the Connections Inspector (''Tools -> Connections Inspector'') then select the Save button. Click inside the small circle next to the ''Touch Up Inside'' event in the Connections Inspector window and drag the blue line to the ''File’s Owner''. To establish the connection, select saveData from the resulting menu. Repeat these steps to connect the Find button to the findContact action before saving the design and exiting Interface Builder.

== Building and Running the Application ==

The final step is to build and run the application. Click on the ''Build and Run'' button located in the toolbar of the main Xcode project window. Assuming an absence of compilation errors, the application should load into the iPhone Simulator environment. Enter details for a few contacts, pressing the Save button after each entry. Be sure to check the status label to ensure the data is being saved successfully. Finally, enter the name of one your contacts and click on the Find button. Assuming the name matches a previously entered record, the address and phone number for that contact should be displayed and the status label updated with the message “Match found”:
 

[[Image:iphone_sqlite_example_running.jpg|The example iPhone SQLite application running in the iPhone Simulator]]


<htmlet>ios9_upgrade</htmlet>


<htmlet>ezoicbottom</htmlet>
<hr>
<table border="0" cellspacing="0">
<tr>
<td width="20%">[[iPhone Database Implementation using SQLite|Previous]]<td align="center">[[iPhone App Development Essentials|Table of Contents]]<td width="20%" align="right">[[Working with iPhone Databases using Core Data|Next]]</td>
<tr>
<td width="20%">iPhone Database Implementation using SQLite<td align="center"><td width="20%" align="right">Working with iPhone Databases using Core Data</td>
</table>