IPhone - sqlite does not work after updating a table

Question

I have two ways to get 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 is the line:

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

Does not evaluate SQLITE_OK.

This is 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 application delegation 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 can shed light on this, it will be very appreciated.

Jack

EDIT; After running the select statement in the database along the m_DatabasePath path, it did not return anything - the file is empty - any idea why the copy of the file I refer to in the 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 an error message:

 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.)'.' 

I'm starting to pluck my hair over it.

EDIT 3; I tried:

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

And it returns nil, I do not understand, because the file is in the application directory.

Answer 1

Noob error, I did not check the box under "Target Membership" for the database. Thanks for all the time.

Answer 2

I recommend that you activate some of the debugging tools that you have been given: 1) Add an NSError parameter to the copyItemAtPath call. 2) Move the sqlite3_prepare_v2 statement (l_Database, sqlStatement, -1, & compiledStatement, NULL) to your own line and check the return value. If you do not copy the file correctly, open will open you an empty database, which, as it seems to you, has.

 NSError * error; BOOL copiedIt = [l_FileManager copyItemAtPath:l_DatabasePathFromApp toPath:m_DatabasePath error:&error]; 

The above will at least tell you if it worked. If this does not work, you know where your problem lies.

Answer 3

Shouldn't "choose * from products" really "choose" from products "?