An Example SQLite based iPhone Application

Link：http://www.techotopia.com/index.php/An_Example_SQLite_based_iPhone_Application

1. Creating the Database and Table

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

}

2. Implementing the Code to Save Data to the SQLite Database

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

}

3. Implementing Code to Extract Data from the SQLite Database 

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

}