This is a one-step process. Using the same project as that used earlier in the chapter, define the createTableNamed:withField1:withField2: method as follows:

#import "DatabasesViewController.h"

@implementation DatabasesViewController

-(NSString *) filePath {
   //...
}

-(void) openDB {
   //...
}

-(void) createTableNamed:(NSString *) tableName
withField1:(NSString *) field1

withField2:(NSString *) field2 {

    char *err;
    NSString *sql = [NSString stringWithFormat:
        @"CREATE TABLE IF NOT EXISTS "%@" ("%@" TEXT PRIMARY KEY, "%@" TEXT);",
        tableName, field1, field2];

    if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {
        sqlite3_close(db);
        NSAssert(0, @"Tabled failed to create.");
}

}

- (void)viewDidLoad {
    [self openDB];
    [self createTableNamed:@"Contacts" withField1:@"email" withField2:@"name"];
    [super viewDidLoad];
}

@end

How It Works

The createTableNamed:withField1:withField2: method takes in three parameters: tableName, field1, and field2.

Using these three parameters, you first formulate a SQL string and then create a table using the sqlite3_exec() C function, with the important arguments to this function being the sqlite3 object, the SQL query string, and a pointer to a variable for error messages. If an error occurs in creating the database, you will use the NSAssert method to halt the application and close the database connection.

Figure 12-3 shows the Contacts table that is created if the operation is successful.

Figure 12.3. Figure 12-3

How It Works

The code in this example is similar to that of the previous one; you formulate a SQL string and use the sqlite3_exec() C function to insert a record into the database:

NSString *sql = [NSString stringWithFormat:
        @"INSERT OR REPLACE INTO '%@' ('%@', '%@') VALUES ('%@','%@')",
        tableName, field1, field2, field1Value, field2Value];

    char *err;
    if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK)
    {
        sqlite3_close(db);
        NSAssert(0, @"Error updating table.");
    }

In the viewDidLoad method, you insert two records into the database by calling the insertRecordIntoTableNamed:withField1:field1Value:andField2:field2Value: method:

for (int i=0; i<=2; i++)
    {
        NSString *email = [[NSString alloc] initWithFormat:
                              @"user%[email protected]",i];
        NSString *name = [[NSString alloc] initWithFormat: @"user %d",i];

        [self insertRecordIntoTableNamed:@"Contacts"
            withField1:@"email" field1Value:email
            andField2:@"name" field2Value:name];
    }

Figure 12-4 shows the content of the table after the rows are inserted into the table.

Figure 12.4. Figure 12-4

How It Works

To retrieve the records from the table, you first prepare the SQL statement and then use the sqlite3_step() function to execute the prepared statement. The sqlite3_step() function returns a value of 100 (represented by the SQLITE_ROW constant) if another row is ready. In this case, you call the sqlite3_step() function using a while loop, continuing as long as it returns a SQLITE_ROW:

if (sqlite3_prepare_v2( db, [qsql UTF8String], −1, &statement, nil) ==
    SQLITE_OK) {

        while (sqlite3_step(statement) == SQLITE_ROW)
        {
            char *field1 = (char *) sqlite3_column_text(statement, 0);
            NSString *field1Str = [[NSString alloc] initWithUTF8String: field1];

            char *field2 = (char *) sqlite3_column_text(statement, 1);

NSString *field2Str = [[NSString    alloc] initWithUTF8String: field2];

            NSString *str = [[NSString alloc] initWithFormat:@"%@ - %@",
                                field1Str, field2Str];
            NSLog(str);

            [field1Str release];
            [field2Str release];
            [str release];
        }
        //---deletes the compiled statement from memory---
        sqlite3_finalize(statement);
    }

To retrieve the value for the first field in the row, you use the sqlite3_column_text() function by passing it the sqlite3_stmt object as well as the index of the field you are retrieving. For example, to retrieve the first field of the returned row, you use:

char *field1 = (char *) sqlite3_column_text(statement, 0);

To retrieve an integer column (field), use the sqlite3_column_int() function.