Problem in retriving data from sqlite

Question

Hi i am working with sqlite and i have added the data into the database successfully, but the problem is that when i delete some data in some other table view and navigate back to my main view and i press the UIBarButton to call a function which again checks for the data in the database.

Now my problem is that the data which is newly displayed in the table is the one which got deleted so i tried to print my mutable array and in the log i see that their is no such data but then too in the table view cell i see that data. given below is my code to read the data in the db

NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentPaths objectAtIndex:0];
   NSString *databasePath =  [documentsDir stringByAppendingPathComponent:@"PotsDB.sqlite"];

    sqlite3 *database;

    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
        // Setup the SQL Statement and compile it for faster access
        const char *sqlStatement = "SELECT potsName FROM potsDetail;";
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while(sqlite3_step(compiledStatement) == SQLITE_ROW) {


                NSString *Potname=[NSString stringWithUTF8String:(char *) sqlite3_column_text(compiledStatement, 0)];
                NSString * pname = [[NSString alloc]initWithFormat:@"%@",Potname];
                [potsArray addObject:pname];
                 NSLog(@"The data in the array is %@",potsArray);

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

Also heres the code that i am calling on the refresh button

-(IBAction)RefreshButtonTouched
{
    [potsArray removeAllObjects];
    [self Read_Potname_FromDB];
    [potsTableView reloadData];
}

The delete query that i am writing, i think i am doing some mistake with delete here

NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentPaths objectAtIndex:0];
    NSString *databasePath =  [documentsDir stringByAppendingPathComponent:@"PotsDB.sqlite"];

    sqlite3 *database;

    if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
        // Setup the SQL Statement and compile it for faster access
        NSString *deleteStatement = [NSString stringWithFormat:@"delete from potsDetail where potsName = '%@'",sptr];
        const char *sqlStatement = [deleteStatement cStringUsingEncoding:NSASCIIStringEncoding];
        sqlite3_stmt *compiledStatement;

        if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
            while(sqlite3_step(compiledStatement) == SQLITE_ROW) {

                NSString *Potname=[NSString stringWithUTF8String:(char *) sqlite3_column_text(compiledStatement, 0)];
                NSString * pname = [[NSString alloc]initWithFormat:@"%@",Potname];
                [potsArray addObject:pname];
                //[pname release];
            }
        }
        // Release the compiled statement from memory
        sqlite3_finalize(compiledStatement);
    }
    sqlite3_close(database);

Please help me out am pretty messed up with this. Thanks in advance

iMOBDEV iMOBDEV · Accepted Answer · 2011-06-27T15:42:29

There are two cases that can happen here:

1) Data is actually not deleted because You might have not called "sqlite3_finalize(compiledStatement);" statement, which is responsible to commit any transaction.

2) UITableView is not reloaded, did you try using "[tableView reloadData];" on click of UIBarbuttonItem?

Problem in retriving data from sqlite

2 Answers