An Example SQLite based iOS 6 iPhone Application

In the chapter entitled iOS 6 iPhone Database Implementation using SQLite the basic concepts of integrating a SQLite based database into iOS 6 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.

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 unfamiliar with these technologies in the context of iPhone application development are encouraged to first read the previous chapter before proceeding.

Begin by launching the Xcode environment and creating a new iOS iPhone Single View Application project with a name and class prefix of Database with Storyboard support and Automatic Reference Counting enabled.

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 panel. Select the Build Phases tab to display the build information. 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 search for, and then select libsqlite3.dylib and click Add.

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. Now is also a good time to declare an NSString property in which to store the path to the database. Within the main Xcode project navigator, select the DatabaseViewController.h file and modify it as follows:

#import <UIKit/UIKit.h>
#import <sqlite3.h>

@interface DatabaseViewController : UIViewController

@property (strong, nonatomic) NSString *databasePath;
@property (nonatomic) sqlite3 *contactDB;
@end

The next step in developing our example SQLite iOS iPhone application involves the design of the user interface. Begin by selecting the MainStoryboard.storyboard file to edit the user interface and drag and drop components from the Object library (View -> Utilities -> Object Library) onto the view canvas and edit properties so that the layout appears as illustrated in Figure 40-1:

Figure 40-1

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

Select the top most text field object in the view canvas, display the Assistant Editor panel and verify that the editor is displaying the contents of the DatabaseViewController.h file. Ctrl-click on the text field object again and drag to a position just below the @interface line in the Assistant Editor. Release the line and in the resulting connection dialog establish an outlet connection named name. Repeat the above steps to establish outlet connections for the remaining text fields and the label object to properties named address and phone respectively and status.

Ctrl-click on the Save button object and drag the line to the area immediately beneath the newly created outlet in the Assistant Editor panel. Release the line and, within the resulting connection dialog, establish an Action method on the Touch Up Inside event configured to call a method named saveData. Repeat this step to create an action connection from the Find button to a method named findContact.

On completion of these steps, the DatabaseViewController.h file should read as follows:

#import <UIKit/UIKit.h>
#import <sqlite3.h>

@interface DatabaseViewController : UIViewController
@property (strong, nonatomic) IBOutlet UITextField *name;
@property (strong, nonatomic) IBOutlet UITextField *address;
@property (strong, nonatomic) IBOutlet UITextField *phone;
@property (strong, nonatomic) IBOutlet UILabel *status;

- (IBAction)saveData:(id)sender;
- (IBAction)findContact:(id)sender;

@property (strong, nonatomic) NSString *databasePath;
@property (nonatomic) sqlite3 *contactDB;
@end

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 modify it as follows:

- (void)viewDidLoad {
    [super viewDidLoad];
    NSString *docsDir;
    NSArray *dirPaths;

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

    docsDir = dirPaths[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";
        }
     }
}

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 otherwise of the operation via the status label.
• Closes the 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 failure of the operation.

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

- (void) saveData:(id)sender
{
     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 (\"%@\", \"%@\", \"%@\")",
             self.name.text, self.address.text, self.phone.text];

           const char *insert_stmt = [insertSQL UTF8String];
           sqlite3_prepare_v2(_contactDB, insert_stmt,
             -1, &statement, NULL);
            if (sqlite3_step(statement) == SQLITE_DONE)
            {
                 self.status.text = @"Contact added";
                 self.name.text = @"";
                 self.address.text = @"";
                 self.phone.text = @"";
            } else {
                  self.status.text = @"Failed to add contact";
            }
            sqlite3_finalize(statement);
            sqlite3_close(_contactDB);
    }
}

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

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. To this end, the Touch Up Inside event of the find button has been connected to the findContact method, the code for which is outlined below:

- (void) findContact:(id)sender
{
     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";
                     } else {
                             _status.text = @"Match not found";
                             _address.text = @"";
                             _phone.text = @"";
                     }
                     sqlite3_finalize(statement);
             }
             sqlite3_close(_contactDB);
     }
}

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 and closes the database.

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”:

Figure 40-2

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 6 iPhone Databases using Core Data.