View source for An Example SQLite based iOS 4 iPhone Application (Xcode 4)

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


<htmlet>ios9_upgrade</htmlet>


In the chapter entitled [[iOS 4 iPhone Database Implementation using SQLite (Xcode 4)|iOS 4 iPhone Database Implementation using SQLite]] the basic concepts of integrating a SQLite based database into iOS 4 iPhone-based applications were discussed. In this chapter we will put this knowledge to use by creating a simple example application that demonstrates 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 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.

== 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 add this library select the target entry in the Xcode project navigator (the top entry with the product name) to display the summary information. Select the Build Phases tab to display the build information:
 

[[Image:xcode_4_add_existing_framework.jpg|Adding a new framework]]


The Link Binary with Libraries section lists the libraries and frameworks already included in the project. To add another library or framework click on the ‘+’ button to display the full list. From this list, select ''libsqlite3.dylib'' and click ''Add''.

== 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 navigator, 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 allocated memory in 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 {
        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 */) before editing 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>

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 subsequently uses it to detect if the database file already exists.
* If the file does not yet exist the code 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. Ultimately, the Touch Up Inside 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 ==

The final step in developing our example SQLite iOS iPhone application involves the design of the user interface. Begin by selecting the databaseViewController.xib file to edit the user interface and drag and drop components from the Object library (''View -> Utilities -> Object Library'') onto the canvas and edit properties so that the layout appears as illustrated in the following figure:
<htmlet>adsdaqbox_flow</htmlet>

[[Image:xcode_4_database_ui.jpg|The UI for the iPhone database example in Xcode 4]]

 
Before proceeding, stretch the status label (located above the two buttons) so that it covers most of the width of the view as shown 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 File’s Owner object to the component in the view. 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 (''View -> Utilities -> Connections Inspector'') then select the Save button in the editing canvas. 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 method.

== Building and Running the Application ==

The final step is to build and run the application. Click on the ''Run'' button located in the toolbar of the main Xcode project window. Assuming an absence of compilation errors, the application should load into the iOS 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”:
 
<google>IOSBOX</google>
[[Image:ios_4_iphone_sqlite_app_running.jpg|An iOS 4 iPhone SQLite based application running in the iOS Simulator]]


== Summary ==

In this chapter we have looked at the basics of storing data on an iPhone using the SQLite database environment. For developers unfamiliar with SQL and reluctant to learn it, an alternative method for storing data in a database involves the use of the Core Data framework. This topic will be covered in detail in the next chapter entitled [[Working with iOS 4 iPhone Databases using Core Data (Xcode 4)|Working with iOS 4 iPhone Databases using Core Data]].


<htmlet>ios9_upgrade</htmlet>



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