How to delete a row in a sqlite database table?

Question

I am using fmdb for managing my database. I could not find any example for deleting a row from a table in fmdb. I tried

  NSString *sqlStat=@"DELETE from tableName WHERE id=3";    
  FMResultSet *rs = [database executeQuery:sqlStat];

but its not working because when I checked the total number of entries in table, I am getting the same number as before executing the above statement. So, what is a proper way to delete a row from a table using fmdb?

Fibroma

How are you determining the number of entries within the table? – Pennyweight

For eg.I am doing FMResultSet *rs = [database executeQuery:@"select * from tableName"]; and then storing the result in NSMutableArray. – Fibroma

Answer 1

FMDB can be a little finicky if you dont pass in the object as an NSNumber. This is the supported, and safe way of formatting queries.

[db executeUpdate:@"DELETE FROM theTable WHERE id = ?", [NSNumber numberWithInt:myObject.id]];

Velours

Answer 2

You should replace:

... [database executeQuery:sqlStat] ...

with:

... [database executeUpdate:sqlStat];

Also, try adding:

[database beginTransaction];

before your CRUD block, and:

[database commit];

after executing an update/delete/insert operation.

;)

Skinner

Not sure if this would help anyone else but I'm using sqlite3 from the command line and I issued a BEGIN TRANSACTION; before and COMMIT; after running my DELETE statement. My first time using the CLI and this is what worked for me. Thanks for the tip, @Florin. – Jaggery

Worked with [database executeUpdate:sqlStat], where sqlStat is "DELETE" – Deviate

Answer 3

i also ran into the same symptom. and my problem was i didnt call (and caused "out of memory" error)

[db open];

be sure to do this to debug your fmdb issues db.traceExecution=YES; db.logsErrors=YES;

Selfdeception

Answer 4

you need to ensure all the processes

NSString *query = @"delete from places where published = 1";
const char *sqlStatement = [query UTF8String];
sqlite3_stmt *compiledStatement;
if(sqlite3_prepare_v2(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 result row
        NSLog(@"result is here");
    }

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

}
int numberOfEffectedRow = sqlite3_changes(database);
return numberOfEffectedRow; // get number of effected rows

Olivenite