Objective C - iPhone - Inserting record into sqlite database not working as expected

Question

I use the below code to create and read my database in the app delegate:

- (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];

}

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

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

    NSLog(@"%@", m_DatabasePath);


    //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)];
                NSLog(@"%@",[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)];

                //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 ];

                //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);
}

The above code is called as follows:

- (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..

    return YES;
}

I then populate a table view using the following code:

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
    NSLog(@"Cell for row");
        static NSString *CellIdentifier = @"Cell";

        UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
        if (cell == nil) {
            cell = [[UITableViewCell alloc] initWithStyle:UITableViewCellStyleDefault reuseIdentifier:CellIdentifier];
        }

    if([self.productType isEqualToString:@"ALL"]){
        Product *product = (Product *) [appDelegate.m_Products objectAtIndex:indexPath.row];

        cell.textLabel.text = product.name;

        // Configure the cell.
        return cell;
    }
    else {

            //Instead of using appDelegate.products use the new array that will be filled
            //by the numberOfReowsInSection method
            Product *product = (Product *)[[m_AlphabetDictionary objectAtIndex:indexPath.section] objectAtIndex:indexPath.row];

            cell.textLabel.text = product.name;
            // Configure the cell.
            return cell;
    }
}

My problem arises when I try to insert some test data as follows:

-(void)insertData{

    sqlite3 *database;
    sqlite3_stmt *statement;

    //Get the path to the documents directory and append the databaseName
    AppDelegate *appDelegate = (AppDelegate *) [[UIApplication sharedApplication] delegate];
    NSString *databasePath = [appDelegate m_DatabasePath];
    NSLog(@"%@", [appDelegate m_DatabasePath]);
    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
    {

        NSString *insertSQL = @"INSERT INTO products (name,category,calories, saturates,fat,fibre,sugar,salt, image) VALUES ('Snickers',' Confectionary','300','55','55','55','55','55', 'http://upload.wikimedia.org/wikipedia/en/8/88/Snickers_wrapped.jpg');";
        const char *insert_stmt = [insertSQL UTF8String];
        if(sqlite3_prepare_v2(database, insert_stmt, -1, &statement, NULL)== SQLITE_OK)
        {
            NSLog(@"Here 1");
            if(sqlite3_step(statement)==SQLITE_DONE) 
            {
                UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Product Added" message:@"" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];    
                [alert show];

            }
            else 
            {
                UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Product Not Added" message:@"An error has occured" delegate:self cancelButtonTitle:@"OK" otherButtonTitles:nil];   
                [alert show];
                alert=nil;
            }   
        }
        // Release the compiled statement from memory
        sqlite3_finalize(statement);    
        sqlite3_close(database);
    }
}

After the above code is called I would expect to see a new cell in the table view representing the new record, however this doesn't happen. Does anyone know what I'm doing wrong? I re-read in the database in the viewDidAppear method of the table view.

Thanks,

Jack

EDIT:

Performing an NSLog outputting the path to the database in both the insertData method and readProductsFromDatabase gives:

/Users/me/Library/Application Support/iPhone Simulator/5.0/Applications/4868830F-E02A-4A81-93AD-2BEAF8B85971/Library/ProductDatabase.sql

Answer 1

It seems like you are inserting data properly, but then you're not re-querying the data nor telling your table view to reload itself with new data.

After your insertData, I would either also call m_Products addObject: with your new Product for better performance (since you know that you inserted, you're just updating your in-memory version of the objects that are in your SQLite databse) or readProductsFromDatabase if you want to do a full SQL re-query.

I'm not quite sure what is populating m_AlphabetDictionary, but I will assume that you can figure out how to update that after using either of the two methods.

Then to tell your table view to reload itself with the new data, call:

[tableView reloadData];