SQLite "SQL Error or missing database" Objective-C

Question

I'm currently getting an error when trying to insert data into my database.

#import "ReminderDB.h"

@implementation ReminderDB

@synthesize db = _db;

-(id)init{
    _db = [self openDB];
    [self createTable:@"Reminders" withField1:@"Title" withField2:@"Who" withField3:@"Quantity"];
    return self;
}

-(NSString *) filepath{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    return [[paths objectAtIndex:0] stringByAppendingPathComponent:@"reminders.sqlite"];
}

- (sqlite3*)openDB {
    if (_db == NULL) {
        int rc;

        if ((rc = sqlite3_open([[self filepath] UTF8String], &(_db))) != SQLITE_OK) {
            NSLog(@"%s error (%1d)", __FUNCTION__, rc);
            _db = NULL;
        } else {
            NSLog(@"db opened");
        }
    }
    return _db;
}
-(void)createTable: (NSString *) tableName
        withField1: (NSString *) field1
        withField2: (NSString *) field2
        withField3: (NSString *) field3
{
    char *err;
    NSString *sql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('%@' TEXT PRIMARY KEY, '%@' TEXT, '%@' TEXT);", tableName, field1, field2, field3];
    if(sqlite3_exec(_db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK){
        sqlite3_close(_db);
        NSAssert(0, @"Could not create table");
    }
    else{
        NSLog(@"Table Created");
    }
}
-(void)addReminder:(NSString*)title
               who:(NSString*)who
          quantity:(NSString*)quantity{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO Reminders ('Title', 'Who', 'Quantity') VALUES ('%@', '%@', '%@')", title, who, quantity];
    char *err;
    int rc;
    if((rc = sqlite3_exec(_db, [sql UTF8String], NULL, NULL, &err)) != SQLITE_OK){
        NSLog(@"%s error (%1d)", __FUNCTION__, rc);
        sqlite3_close(_db);
        NSAssert(0, @"Could not update table");
    }
    else{
        NSLog(@"Table Update Successful");
    }
}

@end

This code successfully opens the database and creates the table. However when I call addReminder:who:quantity the table will not update and the error I am getting is an SQL Error or Missing Database error. Which doesn't make sense to me because I know the table is created and exists.

EDIT I have updated my addReminder:who:quantity: to use binds instead of exec's. I have also taken out the close calls. I am now getting an error when calling prepare.

-(void)addReminder:(NSString*)title
           who:(NSString*)who
      quantity:(NSString*)quantity{

    const char *sql = "INSERT INTO Reminders ('Title', 'Who', 'Quantity') VALUES (?, ?, ?)";
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(_db, sql, -1, &statement, NULL) == SQLITE_OK) {
        // Bind the parameters (note that these use a 1-based index, not 0).
        sqlite3_bind_text(statement, 1, [title UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 2, [who UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 3, [quantity UTF8String], -1, SQLITE_TRANSIENT);
        NSLog(@"Binding successful");
    }
    int returnCode = sqlite3_step(statement);
    if (returnCode != SQLITE_DONE) {
        // error handling...
        NSLog(@"An error occoured (%d)", returnCode);
    }
    else{
        NSLog(@"Table Updated");
    }
}

I know the problem is in the prepare call because I am not getting "Binding successful" in my log.

Answer 1

A couple of thoughts:

1. I don't see anything in the provided code snippet that would produce a warning that says "SQL Error or Missing Database". Have you identified where that's being produced?

2. If you close the database anywhere, don't forget to set _db to NULL. You might have a close method that looks like:

   - (int)closeDB {
       if (_db) {
           int rc = sqlite3_close(_db);
           _db = NULL;
           return rc;
       }
       return SQLITE_OK;
   }
   

   Having said that, you generally don't open and close databases while an app is running, so this might not be critical. But if you are closing it anywhere, make sure to NULL the pointer, too. Otherwise your openDB method won't work properly if you try to reopen the database.

3. In your addReminder method, make sure to log sqlite3_errmsg:

   -(void)addReminder:(NSString*)title
                  who:(NSString*)who
             quantity:(NSString*)quantity {
   
       const char *sql = "INSERT INTO Reminders ('Title', 'Who', 'Quantity') VALUES (?, ?, ?)";
       sqlite3_stmt *statement;
       int returnCode;
   
       if ((returnCode = sqlite3_prepare_v2(_db, sql, -1, &statement, NULL)) == SQLITE_OK) {
           // Bind the parameters (note that these use a 1-based index, not 0).
           sqlite3_bind_text(statement, 1, [title UTF8String], -1, SQLITE_TRANSIENT);
           sqlite3_bind_text(statement, 2, [who UTF8String], -1, SQLITE_TRANSIENT);
           sqlite3_bind_text(statement, 3, [quantity UTF8String], -1, SQLITE_TRANSIENT);
           NSLog(@"Binding successful");
       } else {
           NSLog(@"Prepare failed: %s (%ld)", sqlite3_errmsg(_db), (long) returnCode);
           return;
       }
   
       returnCode = sqlite3_step(statement);
       if (returnCode != SQLITE_DONE) {
           // error handling...
           NSLog(@"An error occurred: %s (%ld)", sqlite3_errmsg(_db), (long)returnCode);
       }
       else{
           NSLog(@"Table Updated");
       }
   
       sqlite3_finalize(statement);
   }
   

   Note, don't forget to call sqlite3_finalize whenever you call sqlite3_prepare_v2 or else you'll leak memory.

4. As an aside, your CREATE TABLE call is creating Quantity as a TEXT column. You might want to create that as INTEGER if it's an integer value, or REAL if it's a floating point value.

   When you bind values for the Quantity value, you might want to use the type-appropriate bind call, e.g.

   sqlite3_bind_int(statement, 3, [quantity intValue]);
   

   Clearly, though, your createTable method seems to be hard coded to create a table with three TEXT columns, so you might want to refactor that, too (though SQLite ignores the column definition when inserting values, so this isn't absolutely critical).

   But, by storing numeric data types, it means that when you sort by Quantity column or do arithmetic calculations, SQLite will handle this properly. Actually, SQLite is pretty flexible in terms of converting string values to numeric values in many cases, so this isn't critical, but I think it's still appropriate to store numeric values as numbers, rather than strings.

   Note, the above point is completely unrelated to your problem at hand, but it does reflect best practice.

5. You say that your sqlite3_prepare_v2 statement is producing an error that says:

   table Reminders has no column named Who

   Your create statement only creates the table if it doesn't exist. So if you accidentally created it earlier using different columns, then "Who" might not be found, because it won't recreate a table that already exists.

   Open the database from the simulator/device (not from the project folder, if you have one there) using your Mac OS X database tool of choice (or the sqlite3 command line program, if you don't have a nice database tool) and take a look at the table and make sure what the column names are. When dealing with the simulator, the precise location of this folder depends upon what version of Xcode you're using. In Xcode 5, it's in ~/Library/Application Support/iPhone Simulator and then navigate to the appropriate iOS version and application, and look in the appropriate Documents subfolder. In Xcode 6 it's located in ~/Library/Developer/CoreSimulator/Devices (and you have to figure out which cryptic folder the app is in, often simplified if you sort this folder by "date"). And, if you're not seeing ~/Library, make sure to unhide the Library folder, easily shown by using the command chflags nohidden ~/Library from the Terminal command line.

   Or just delete the app from the simulator/device and start over, letting it recreate the database again, so you know you're not dealing with an old, out-of-date database.

6. While I encourage you to finish this up using the SQLite C API (it's a good learning experience), I must say that you might want to investigate FMDB, which is an Objective-C wrapper around the SQLite C API. I wouldn't do that right now (you have too many moving parts and I don't want to confuse the situation further), but after you address your immediate challenges, it's worth considering.