sqlite iPhone sdk help?

Question

This is the code I've written to access a SQLite database on the iPhone. The application is crashing for some reason. Could any one help me with this?

-(void)openDB{
    //create database 
    if (sqlite3_open([[self filPath] UTF8String],&db) != SQLITE_OK) {
        sqlite3_close(db);
        NSAssert(0, @"database failed to open");
    }
}


-(void)insertRecordIntoTableNamed:(NSString *)tableName withField1:(NSString *)field1 field1Value:(NSString*)field1Value andField2:(NSString *)field2 field2Value:(NSString *)field2Value {

    // [self insertRecordIntoTableNamed:@"Contacts" withFiled1:@"email" field1Value:email andField2:@"name" field2Value:name];
    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.");
    }
}

-(void)createTableNamed:(NSString *) tableName withField1:(NSString *)field1 withField2:(NSString *)field2 {
    char *err;
    NSString *sql = [NSString stringWithFormat:@"Creat table if not exist '%@' ('%@' text prmaru key,  '%@' TEXT",tableName,field1,field2];
    if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK) {
        sqlite3_close(db);
        NSAssert (0,@"tabel failed to create");
    }
}       

-(void)getAllRowsFromTableNamed:(NSString *)tableName{
    NSString *qsql = [NSString stringWithFormat:@"select * from %@",tableName];
    sqlite3_stmt *statment;

    if (sqlite3_prepare_v2(db, [qsql UTF8String], -1, &statment, nil) == SQLITE_OK) {
        while (sqlite3_step(statment) == SQLITE_ROW) {
            char *field1 = (char *)sqlite3_column_text(statment, 0); 
            NSString *field1Str = [[NSString alloc] initWithUTF8String: field1];
            char *field2 = (char *)sqlite3_column_text(statment, 1);
            NSString *field2Str = [[NSString alloc] initWithUTF8String: field2]; 
            NSString *str = [[NSString alloc] initWithFormat:@"%@ - %@", field1Str, field2Str];
            NSLog(@"%@", str);
            //      [field1Str release];
            //      [field2Str release];
            //      [str release];
        }       
        sqlite3_finalize(statment);
    }
}

- (void)viewDidLoad {
    [self openDB];
    NSLog(@"%@",[self filPath]);
    [self createTableNamed:@"Contacts" withField1:@"email" withField2:@"name"];

    for (int i = 0; i<= 2;i++) {
        NSString *email = [[NSString alloc]initWithFormat:@"user%i@yahoo.com",i];       
        NSString *name = [[NSString alloc]initWithFormat:@"user %i",i];
        [self insertRecordIntoTableNamed:@"Contacts" withField1:@"email" field1Value:email andField2:@"name" field2Value:name];
        [email release];
        [name release];
    }

    [self getAllRowsFromTableNamed:@"Contacts"];
    sqlite3_close(db);
    [super viewDidLoad];
}

Answer 1

Problems that I see:

• [NSString stringWithFormat:@"Creat table if not exist '%@' ('%@' text prmaru key, '%@' TEXT",tableName,field1,field2];

  should be

  [NSString stringWithFormat:@"Create table if not exists %@ ('%@' text primary key, '%@' TEXT)",tableName,field1,field2];

  Like, did you actually read the documentation on SQLite syntax? Spelling matters.

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

  should be

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

  You don't put single quotes around stuff unless you're intending it to be interpreted as a string literal.

• You've commented out these lines:

  //      [field1Str release];
  //      [field2Str release];
  //      [str release];
  

  They shouldn't be.

I don't know if fixing these things will actually fix your crash, but they're definitely wrong.

---

Beyond this, using the SQLite API directly in Objective-C is totally silly. There's no reason to, because there are much easier ways to do it.

1. CoreData. This is an object graph framework that Apple has created to make it really easy for you to create and save objects. It's not a database per-say, but it often behaves like one. If you can use this, I highly recommend it. It's definitely not simple stuff to understand, but it makes some things harder with the advantage of making many things easier.

2. If for some reason you have to interact with an existing SQLite database, you should do so via FMDB. It's a wrapper around the SQLite C API. In other words, Gus has gone to all the trouble of learning the SQLite API so that you don't have to. You'd do something like:

   FMDatabase *db = [[FMDatabase alloc] initWithPath:[self filePath]];
   if (![db open]) {
     [db release];
     db = nil;
   }
   
   //create a table:
   [db executeUpdate:@"create table if not exists Customers (email text primary key, name text)"];
   
   //insert into a table:
   [db executeUpdate:@"insert into Customers (email, name) values (?, ?)", @"test@example.com", @"user1"];
   
   //select from a table:
   FMResultSet *results = [db executeQuery:@"select * from Customers"];
   while ([results next]) {
     NSLog(@"%@ - %@", [results stringForColumn:@"email"], [results stringForColumn:@"name"]);
   }
   
   //close the database:
   [db close];
   [db release];
   

Isn't that about a bajillion times easier than what you're doing?