How to create sqlite database programmatically?

Question

I am new to iPhone development. I have created the database using the terminal in mac os x. Now i am trying to create the database programatically in iPhone application using objective- C. Can somebody tell me a good site where i can view the sample applications which are worked and is there any site for learning the tutorial? Please somebody help me.

Answer 1

From a tutorial on Techtopia:

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

Answer 2

#pragma mark - SQLite

-(void)createDatabase
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); 
    NSString *documentsDirectory = [paths objectAtIndex:0];

    databasePath = [[NSString alloc]initWithString:[documentsDirectory stringByAppendingPathComponent:@"timings.db"]];

    if ([[NSFileManager defaultManager] fileExistsAtPath:databasePath] == FALSE)
    {
        if (sqlite3_open([databasePath UTF8String], &timingsDatabase) == SQLITE_OK)
        {
            const char *sqlStatement = "CREATE TABLE IF NOT EXISTS TIMINGS (ID INTEGER PRIMARY KEY AUTOINCREMENT, TIMESTAMP TEXT, TIMING TEXT)";
            char *error;
            sqlite3_exec(timingsDatabase, sqlStatement, NULL, NULL, &error);
            sqlite3_close(timingsDatabase);
        }
    }
}

-(void)storeTiming
{
    if (sqlite3_open([databasePath UTF8String], &timingsDatabase) == SQLITE_OK)
    {
        NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
        [dateFormatter setDateFormat:@"HH:mm:ss.SSS"];

        NSString *insertStatement = [NSString stringWithFormat:@"INSERT INTO TIMINGS (TIMESTAMP, TIMING) VALUES (\"%@\", \"%@\")", [dateFormatter stringFromDate:startDate], stopWatchLabel.text];

        char *error;
        sqlite3_exec(timingsDatabase, [insertStatement UTF8String], NULL, NULL, &error);
        sqlite3_close(timingsDatabase);

        [dateFormatter release];
    }
}

-(void)getTimings
{
    if (sqlite3_open([databasePath UTF8String], &timingsDatabase) == SQLITE_OK)
    {
        NSString *queryStatement = [NSString stringWithFormat:@"SELECT TIMESTAMP, TIMING FROM TIMINGS"];

        sqlite3_stmt *statement;
        if (sqlite3_prepare_v2(timingsDatabase, [queryStatement UTF8String], -1, &statement, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statement) == SQLITE_ROW) {
                NSLog(@"Timestamp: %s Timing: %s", sqlite3_column_text(statement, 0), sqlite3_column_text(statement, 1));
            }
            sqlite3_finalize(statement);
            sqlite3_close(timingsDatabase);
        }  
    }
}

Check these examples:

1. http://www.techotopia.com/index.php/An_Example_SQLite_based_iOS_4_iPhone_Application
2. http://www.icodeblog.com/2008/09/22/iphone-programming-tutorial-creating-a-todo-list-using-sqlite-part-4/