2009-09-08

Code snippet to create a table and insert values to the table in a sqlite database connection in an iPhone application

         NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
         NSString *documentsDirectory = [paths objectAtIndex:0];
         // Dimo.sqlite3 is the name of the database
         NSString *path = [documentsDirectory stringByAppendingPathComponent:@"Dimo.sqlite3"];
             
            finalTry=[self dataFilePath];
       
        //  Open the database if already exist or create the database
        if (sqlite3_open([finalTry UTF8String], &database)!= SQLITE_OK) {
            sqlite3_close(database);
            NSAssert(0, @"Failed to open database");
        }

        char *errorMsg;

        // SQL statement
        NSString *createSQL = @"CREATE TABLE IF NOT EXISTS UserAuth (UserId TEXT PRIMARY KEY, Name TEXT, Password TEXT);";

        // Execute the SQL statement
        if (sqlite3_exec (database, [createSQL  UTF8String],
                          NULL, NULL, &errorMsg) != SQLITE_OK) {
            sqlite3_close(database);
            NSAssert1(0, @"Error creating table: %s", errorMsg);
        }
        else{
            char *errorMsg;
            char *update = "INSERT OR REPLACE INTO UserAuth (UserId, Name, Password) VALUES (?, ?, ?);";
            sqlite3_stmt *stmt;
            if (sqlite3_prepare_v2(database, update, -1, &stmt, nil) == SQLITE_OK) {
               
                tempUserId=[tempArray objectAtIndex:0];
                tempUserName=[tempArray objectAtIndex:1];
                tempPassword=[tempArray objectAtIndex:2];
               
                // Question marks of the INSERT SQL queries are replaced with those
                sqlite3_bind_text(stmt, 1, [tempUserId UTF8String], -1, NULL);
                sqlite3_bind_text(stmt, 2, [tempUserName UTF8String], -1, NULL);
                sqlite3_bind_text(stmt, 3, [tempPassword UTF8String], -1, NULL);
            }
            if (sqlite3_step(stmt) != SQLITE_DONE)
                NSAssert1(0, @"Error updating table: %s", errorMsg);
            sqlite3_finalize(stmt);
        }
       
    }
   
    // Close the database
    sqlite3_close(database);

No comments: