iOS Sqlite3 Delete Query Not Deleting Row

Question

I am using this code to delete a row from the database for my ipad application,

-(BOOL) removeSegmentWithSegmentId:(NSInteger)sId
{
    AppDelegate *appDelegate = (AppDelegate *) [[UIApplication sharedApplication] delegate];

    sqlite3_stmt *statement;

    NSString *removeKeyword =[NSString stringWithFormat:@"DELETE FROM segment WHERE segment.segment_id = %d",sId];
    const char *query = [removeKeyword UTF8String];
    NSLog(@"%@",removeKeyword);

    //if(sqlite3_prepare_v2(appDelegate->globalConnection,[removeKeyword UTF8String] , -1, &statement, NULL) == SQLITE_OK)
    if(sqlite3_prepare_v2(appDelegate->globalConnection,query , -1, &statement, NULL) == SQLITE_OK)
    {
        if(sqlite3_step(statement) == SQLITE_DONE) {
            sqlite3_finalize(statement);
            return YES;
        }
    }
    return NO;
}

but it is not working, can anyone guide me please?

I bet you're trying to delete from a database residing in the app bundle... — user529758
– user529758, Commented Nov 27, 2012 at 6:47

Rob · Accepted Answer · 2012-11-27 06:42:22Z

Is your method returning YES?

A couple of things:

Always log sqlite3_errmsg on any failures
Right now, you're only doing sqlite3_finalize is sqlite3_step returns SQLITE_DONE, whereas you really should be doing it whenever you successfully did sqlite3_prepare_v2

So, I might suggest, at a minimum:

-(BOOL) removeSegmentWithSegmentId:(NSInteger)sId
{
    BOOL success = NO;

    AppDelegate *appDelegate = (AppDelegate *) [[UIApplication sharedApplication] delegate];

    sqlite3_stmt *statement;

    NSString *removeKeyword = [NSString stringWithFormat:@"DELETE FROM segment WHERE segment.segment_id = %d",sId];

    if (sqlite3_prepare_v2(appDelegate->globalConnection, [removeKeyword UTF8String], -1, &statement, NULL) == SQLITE_OK)
    {
        if(sqlite3_step(statement) == SQLITE_DONE) 
        {
            success = YES;
        }
        else
        {
            NSLog(@"%s: step not ok: %s", __FUNCTION__, sqlite3_errmsg(appDelegate->globalConnection));
        }
        sqlite3_finalize(statement);
    }
    else
    {
        NSLog(@"%s: prepare failure: %s", __FUNCTION__, sqlite3_errmsg(appDelegate->globalConnection));
    }

    return success;
}

Assuming this method was always returning YES, if you're not seeing records deleted, it must be that it's not finding a record to delete. (That is not considered a SQLite failure. The SQL was successfully executed, but the WHERE clause couldn't be satisfied.) You can verify this by defining the following method:

- (NSInteger)countSegmentWithSegmentId:(NSInteger)sId
{
    NSInteger count = 0;

    AppDelegate *appDelegate = (AppDelegate *) [[UIApplication sharedApplication] delegate];

    sqlite3_stmt *statement;

    NSString *sql = [NSString stringWithFormat:@"SELECT segment_id FROM segment WHERE segment.segment_id = %d", sId];

    if (sqlite3_prepare_v2(appDelegate->globalConnection, [sql UTF8String], -1, &statement, NULL) == SQLITE_OK)
    {
        while ((rc = sqlite3_step(statement)) == SQLITE_ROW)
            count++;

        sqlite3_finalize(statement);
    }
    else
    {
        NSLog(@"%s: prepare failure: %s", __FUNCTION__, sqlite3_errmsg(appDelegate->globalConnection));
        return -1;
    }

    return count;
}

And then put the diagnostic message in removeSegmentWithSegmentId:

- (BOOL)removeSegmentWithSegmentId:(NSInteger)sId
{
    BOOL success = NO;
    NSInteger count = [self countSegmentWithSegmentId:sId];

    NSLog(@"%s there are %d records with segment_id of %d", __FUNCTION__, count, sId);

    AppDelegate *appDelegate = (AppDelegate *) [[UIApplication sharedApplication] delegate];

    sqlite3_stmt *statement;

    NSString *removeKeyword = [NSString stringWithFormat:@"DELETE FROM segment WHERE segment.segment_id = %d",sId];

    if (sqlite3_prepare_v2(appDelegate->globalConnection, [removeKeyword UTF8String], -1, &statement, NULL) == SQLITE_OK)
    {
        if(sqlite3_step(statement) == SQLITE_DONE)
        {
            success = YES;
        }
        else
        {
            NSLog(@"%s: step not ok: %s", __FUNCTION__, sqlite3_errmsg(appDelegate->globalConnection));
        }
        sqlite3_finalize(statement);
    }
    else
    {
        NSLog(@"%s: prepare failure: %s", __FUNCTION__, sqlite3_errmsg(appDelegate->globalConnection));
    }

    return success;
}

SARANGA · Accepted Answer · 2012-11-27 06:25:26Z

2

if(sqlite3_step(statement) == SQLITE_DONE) 
{
sqlite3_finalize(statement);
            return YES;
}
else
{
NSLog(@"Failed to delete row %s", sqlite3_errmsg(database));
}

check the error msg.

answered Nov 27, 2012 at 6:25

SARANGA

1721 silver badge8 bronze badges

Comments

Suyog Patil · Accepted Answer · 2012-11-27 07:17:44Z

Please try this

Steps are

1.Open database

2.Delete row from table

3.Close database

Also added NSLog to view the error in console

//------------------------------------------------------------------------
// Method : checkAndCreateDatabase
// Method to Check and Create the database
//------------------------------------------------------------------------
//Function to check & create a database
-(void) checkAndCreateDatabase
{
    //-------------------------------------------
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    success = [fileManager fileExistsAtPath:cDatabasePath];
    //-------------------------------------------
    //databse already there
    if(success)
    {
        return;
    }
    //-------------------------------------------
    //create database
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:cDatabaseName];
    [fileManager copyItemAtPath:databasePathFromApp toPath:cDatabasePath error:nil];

}

//------------------------------------------------------------------------
// Method : checkAndCreateDatabase
// Method to open database
//------------------------------------------------------------------------
-(void) openDatabase
{

        cDatabaseName = @"db.sqlite";

    NSArray *documentsPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentDir = [documentsPaths objectAtIndex:0];
    cDatabasePath = [documentDir stringByAppendingPathComponent:cDatabaseName];
    [self checkAndCreateDatabase];
    //-------------------------------------------
    if(sqlite3_open([cDatabasePath UTF8String],&database) == SQLITE_OK)
    {
      //nothing
    }
}

//------------------------------------------------------------------------
// Method : closeDatabase
// Method to close database
//------------------------------------------------------------------------
- (void)closeDatabase
{

    // Close the database.
    if (sqlite3_close(database) != SQLITE_OK) {
        //NSLog(@"Error: failed to close database with message '%s'.", sqlite3_errmsg(database));

    }


}


-(BOOL) removeSegmentWithSegmentId:(NSInteger)sId
{
    //for sharing variables of appdelegate file
    AppDelegate *appDelegate = (AppDelegate *) [[UIApplication sharedApplication] delegate];

    BOOL isDeleted=NO;
    [self openDatabase];
    const char *sqlStatement;
    sqlStatement = "DELETE FROM segment WHERE segment_id =?";
    sqlite3_stmt *compiledStatement;

    if(sqlite3_prepare_v2(appDelegate.database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK)
    {   
        sqlite3_bind_int(compiledStatement, 1, sId); 

        if(SQLITE_DONE != sqlite3_step(compiledStatement))
        {
            NSLog( @"Error while deleting metadata of  segment '%s'", sqlite3_errmsg(appDelegate.database));         

        }
        else 
        {
            NSLog(@"Deleted chart segment successfully !");
            isDeleted=YES;

        }
        //-------------------------------------------
        sqlite3_reset(compiledStatement);       
    }
    else 
    {
        NSLog( @"Error while deleting segment of  chart '%s'", sqlite3_errmsg(appDelegate.database));

    }
    sqlite3_finalize(compiledStatement);
    [self closeDatabase];
    return isDeleted;
}

yousaf nisar · Accepted Answer · 2019-04-24 09:47:30Z

0

delete rows using name from text box.

(BOOL) deleteRow:(NSString *)name { const char *dbpath = [databasePath UTF8String];

if (sqlite3_open(dbpath, &database) == SQLITE_OK) { NSString *querySQL = [NSString stringWithFormat: @"DELETE FROM person WHERE name='%@'",name]; const char *query_stmt = [querySQL UTF8String];

if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) == SQLITE_OK) {
    if (sqlite3_step(statement) == SQLITE_DONE) {
        sqlite3_finalize(statement);
        sqlite3_close(database);
        return YES;
    }
    else
    {
        NSLog(@"%d",sqlite3_step(statement));
    }
}
sqlite3_finalize(statement);

} sqlite3_close(database); return NO; }

return Yes if deleted successfully and return NO if fail to delete.

edited Apr 24, 2019 at 9:47

answered Jan 10, 2018 at 12:18

yousaf nisar

115 bronze badges

1 Comment

Mogsdad Over a year ago

Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation would greatly improve its long-term value by showing why this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you've made.

Collectives™ on Stack Overflow

iOS Sqlite3 Delete Query Not Deleting Row

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related