IOS and SQLite - Part 2

Ok, we're going to jump right into tackling part 2 of incorporating SQlite into our iOS app. We'll tackle the next chapter in an online tutorial, and see how far we can get. Note that though the heading states it's using Xcode4, so far all we've seen is Xcode 3.

Here's the URL again:

http://www.techotopia.com/index.php/An_Example_SQLite_based_iOS_4_iPhone_Application#About_the_Example_SQLite_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.

Hmm...I wonder if this covers an insert? Doesn't sound like it. That's ok - we don't need it yet.

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.

Good, created.

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.

Ok, there's the first problem. We know the new way, except there were are libraries to choose from. One had a "0" in it somewhere. I wonder if Xcode 3 gives the same choice? Ah here we go:

http://stackoverflow.com/questions/4314866/what-is-the-difference-between-libsqlite3-dylib-and-libsqlite3-0-dylib

shows it's just a symlink. I could've gotten that - use finder to find the file, then ls -l, I think. Still - sometimes it's just faster to google.

Ok, added the one without the 0. (See previous post for how to do it).

Next?

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:

#import
#import "/usr/include/sqlite3.h"

@interface databaseViewController : UIViewController {
sqlite3 *contactDB;
}

Done. I feel more comfortable with the whole view controller concept since going through the exercise of replacing the default view controller.

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.

Sounds good...

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

#import
#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

No problem!

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:

#import "databaseViewController.h"

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

- (void) saveData
{
}

-(void) findContact
{
}
.
.
.
@end

So far so good.

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:

- (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];
}

Ok. Keep on moving...

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:

- (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];
}


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.

Ok, looks good.

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:

- (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);
}
}

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

Good, good. Keep on moving.

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:

- (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);
}
}

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. Having implemented the appropriate methods be sure to save both the databaseViewController.h and databaseViewController.m files before moving on to the user interface design phase.

Ok. Save them all. Now, onto the dreaded construction of the user interface.

Designing the User Interface

The final step in developing our example SQLite iOS iPhone application involves the design of the user interface. In the main Xcode project window, begin by double clicking 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:

Ok, we know how to use the interface builder to add labels, text boxes and buttons. No problem here. Ok, actually, it's pretty straightforward by now to connect the text labels and buttons to the corresponding UILabels and methods. Ok. Not bad. Are we good to go?

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


iPhone iOS 4
Development
Essentials eBook
$12.99

eBookFrenzy.com

Cool. Let's try it...


Uh..oh. Can't get at it due to the fact that the keyboard control won't go away. There's a solution here:

http://stackoverflow.com/questions/804563/how-to-hide-the-keyboard-when-empty-area-is-touched-on-iphone

But I think I'll just moves the buttons higher for now.

Sugar. It's not working. I save and the status says saved, but the find button is just returning blanks.

Doh! I didn't fill in the find method. Don't you love it when it's just an obvious error?

And voila - here's the "finished" app: