iPhone - sqlite not working after updating table

Question

I have the following two methods for getting data from my database:

- (void) checkAndCreateDatabase {
    //Check if the database has been saved to the users phone, if not then copy it over
    BOOL l_Success;

    //Create a file manager object, we will use this to check the status
    //of the databse and to copy it over if required
    NSFileManager *l_FileManager = [NSFileManager defaultManager];

    //Check if the database has already been created in the users filesystem
    l_Success = [l_FileManager fileExistsAtPath:m_DatabasePath];

    //If the database already exists then return without doing anything

    if(l_Success)
        return;

    //If not then proceed to copy the database from the application to the usrs filesystem

    //Get the path to the database in the application package
    NSString *l_DatabasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:m_DatabaseName];

    //Copy the database from the package to the usrrs filesystem
    [l_FileManager copyItemAtPath:l_DatabasePathFromApp toPath:m_DatabasePath error:nil];

}

And:

- (void) readProductsFromDatabase {
    //Setup the database object
    sqlite3 *l_Database;

    //Init the products array
    m_Products = [[NSMutableArray alloc] init];

    //Open the database from the users filessystem
    if(sqlite3_open([m_DatabasePath UTF8String], &l_Database) == SQLITE_OK) {
        //Set-up the SQL statement and compile it for faster access
        const char *sqlStatement = "select *from products";
        sqlite3_stmt *compiledStatement;

        if(sqlite3_prepare_v2(l_Database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
        {
            //Loop through the results and add them to the feeds array
            while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
                //Read the data from the results row
                NSString *aName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
                NSString *aCategory = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
                NSString *aCalories = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];
                NSString *aFat = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 4)];
                NSString *aSaturates = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 5)];
                NSString *aSugar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 6)];
                NSString *aFibre = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 7)];
                NSString *aSalt = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 8)];
                NSString *aImageURL = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 9)];
                NSLog(@"Delegate");
                NSString *aNote = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 10)];
                NSUInteger myInt = sqlite3_column_int(compiledStatement, 11);
                NSString *aServes = [NSString stringWithFormat:@"%d", myInt];

                //Create a new animal object with the data from the database
                Product *l_Product = [[Product alloc] initWithName:aName category:aCategory calories:aCalories fat:aFat saturates:aSaturates sugar:aSugar fibre:aFibre salt:aSalt imageURL:aImageURL note:aNote serves:aServes];

                //Add the animal object to the animals array
                [m_Products addObject:l_Product];

            }
        }
        //Release the compiled statement from memory
        sqlite3_finalize(compiledStatement);

    }
    sqlite3_close(l_Database);
}

My problem appears to be that the line:

if(sqlite3_prepare_v2(l_Database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)

Is not evaluating to SQLITE_OK.

This my database path:

m_DatabasePath = /Users/jacknutkins/Library/Application Support/iPhone Simulator/5.0/Applications/6F723583-9A25-4F17-AF75-911DE9CB7BD8/Library/ProductDatabase.sql

This is my app delegate method containing related code:

- (BOOL)application:(UIApplication *)application didFinishLaunchingWithOptions:(NSDictionary *)launchOptions
{
    //Set-up some globals
    m_DatabaseName = @"ProductDatabase.sql";

    //Get the path to the documents directory and append the databaseName
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    m_DatabasePath = [documentsDirectory stringByAppendingPathComponent:@"ProductDatabase.sql"];

    //Execute the "checkAndCreateDatabase" function
    [self checkAndCreateDatabase];

    //Query the databse for all animal records and construct the "animals" array
    [self readProductsFromDatabase];
..unrelated code..

If anyone could shed any light on this it would be much appreciated.

Jack

EDIT; After running a select statement on the database at the m_DatabasePath path it returned nothing - the file is empty - any idea why the copy of the file I'm accessing in code is empty?

EDIT 2; This is my new code:

- (void) checkAndCreateDatabase {
    // First, test for existence.
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSString *dbPath = [self getDBPath];
    BOOL success = [fileManager fileExistsAtPath:dbPath]; 

    if(!success) {

        NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"ProductDatabase.sql"];
        success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];

        if (!success) 
            NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }   
}

- (NSString *) getDBPath {
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    return [documentsDir stringByAppendingPathComponent:@"ProductDatabase.sql"];

}

- (void) readProductsFromDatabase {

    NSMutableArray *products = [[NSMutableArray alloc] init];
    self.m_Products = products;
    // The database is stored in the application bundle. 
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"ProductDatabase.sql"];
    // Open the database. The database was prepared outside the application.
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
        // Get the primary key for all books.
        const char *sql = "SELECT * FROM products";
        sqlite3_stmt *statement;
        // Preparing a statement compiles the SQL query into a byte-code program in the SQLite library.
        // The third parameter is either the length of the SQL string or -1 to read up to the first null terminator.        
        if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) == SQLITE_OK) {
            // We "step" through the results - once for each row.
            while (sqlite3_step(statement) == SQLITE_ROW) {
                //                //Read the data from the results row
                                NSString *aName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
                                NSString *aCategory = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
                                NSString *aCalories = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
                                NSString *aFat = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
                                NSString *aSaturates = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 5)];
                                NSString *aSugar = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 6)];
                                NSString *aFibre = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
                                NSString *aSalt = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
                                NSString *aImageURL = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 9)];
                                NSLog(@"Delegate");
                                NSString *aNote = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];
                                NSUInteger myInt = sqlite3_column_int(statement, 11);
                                NSString *aServes = [NSString stringWithFormat:@"%d", myInt];

                                //Create a new animal object with the data from the database
                                Product *l_Product = [[Product alloc] initWithName:aName category:aCategory calories:aCalories fat:aFat saturates:aSaturates sugar:aSugar fibre:aFibre salt:aSalt imageURL:aImageURL note:aNote serves:aServes];
                //                
                //                //Add the animal object to the animals array
                                [m_Products addObject:l_Product];
            }
        }
        // "Finalize" the statement - releases the resources associated with the statement.
        sqlite3_finalize(statement);
    } else {
        // Even though the open failed, call close to properly clean up resources.
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }

}

When I run this I get the error:

2012-03-22 13:52:29.551 TabbedDietApp[2040:fb03] *** Assertion failure in -[AppDelegate checkAndCreateDatabase], /Users/jacknutkins/Documents/TabbedDietApp/TabbedDietApp/AppDelegate.m:504
2012-03-22 13:52:29.552 TabbedDietApp[2040:fb03] *** Terminating app due to uncaught exception 'NSInternalInconsistencyException', reason: 'Failed to create writable database file with message 'The operation couldn’t be completed. (Cocoa error 260.)'.'

Starting to tear my hair out over this..

EDIT 3; I have tried:

NSString *filePath = [[NSBundle mainBundle] pathForResource:@"ProductDatabase" ofType:@"sql"];

And it returns nil, I don't understand because the file is in the applications directory..

Are you sure that your m_DatabasePath is correct? just reset the simulator and clean build and all targets and run the app and then NSLog the dB path. — Praveen-K
@Praveen-K Yea, I did a clean and build and the NSLog returned: /Users/jacknutkins/Library/Application Support/iPhone Simulator/5.0/Applications/71E460D4-ED11-4C8D-9BDC-AF115CFF50D6/Library/ProductDatabase.sql and it still doesn't work. — Jack Nutkins
Can you run sqlite3 in the terminal with the given path and run the query in terminal. — Praveen-K
I can't run one on the path used by the application, but I can from the place I coped it from, and it works. — Jack Nutkins
So you mean to say its working when you run in terminal like sqllite3 /Users/jacknutkins/Library/Application Support/iPhone Simulator/5.0/Applications/71E460D4-ED11-4C8D-9BDC-AF115CFF50D6/Library/ProductDatabase.sql — Praveen-K

Jack Nutkins Jack Nutkins · Accepted Answer · 2012-03-23T14:40:17

Noob error, I hadn't checked the check box in the 'Target Membership' section for the database. Thanks for everyones time.

iPhone - sqlite not working after updating table

3 Answers