delete row from SQLite database with FMDB

Question

i feel like i have read the docs for FMDB a million times. i am not sure why this code is not working - i have tried the executeUpdate method and the executeMethodWithFormat and their corresponding formats, neither seem to work. _dbArray is an array of dictionarys. these lines return the correct values - if i copy and paste the results and run the query in a terminal, it removes the record fine. am i missing something obvious?

- (IBAction)deleteDbEntry:(id)sender {

    // get selected row
    NSInteger row = [_dataTable selectedRow];

    // get the entry number
    NSString *idToDelete = [[_dbArray objectAtIndex:row] valueForKey:@"entryColumn"];

    // open DB for writing
    if (![db open]) {
        [db open];
    }

    [db executeUpdate:@"DELETE FROM tbl1 WHERE entry = ?", idToDelete];

    // close DB
    if ([db open]) {
        [db close];
    }

    [_dataTable reloadData];

    return;
}

UPDATE: i have taken your suggestions and it is still not working, and I am not sure why. the opening and closing of the DB is happening elsewhere now (thank you @ccgus), and I am converting a string to an NSNumber (thanks @bryanmac). the NSLog(@"myNumber is %@", myNumber); call is returning the correct number, and i can confirm that the deletion is not happening. the NSLog(@"Error %d: %@", [db lastErrorCode], [db lastErrorMessage]); is reporting no errors (Error 0: (null)). i have also tried:

[db executeUpdateWithFormat:@"DELETE FROM tbl1 WHERE entry = %@", myNumber];

and

[db executeUpdate:@"DELETE FROM tbl1 WHERE id = ?", [NSNumber numberWithInt:myNumber]];

the first seems like it should work, but i have confirmed it does not delete the row. the second errors at me with "incompatible integer to pointer conversion". what am I missing?

- (IBAction)deleteDbEntry:(id)sender {

    // get selected row
    NSInteger row = [_dataTable selectedRow];

    // get the entry number
    NSString *idToDelete = [[_dbArray objectAtIndex:row] valueForKey:@"entryColumn"];

    NSNumberFormatter * f = [[NSNumberFormatter alloc] init];
    [f setNumberStyle:NSNumberFormatterDecimalStyle];
    NSNumber *myNumber = [f numberFromString:idToDelete];
    NSLog(@"myNumber is %@", myNumber);

    [db executeUpdate:@"DELETE FROM tbl1 WHERE entry = ?", myNumber];
    NSLog(@"Error %d: %@", [db lastErrorCode], [db lastErrorMessage]);

    [_dataTable reloadData];

    return;
}

http://novasys.in/2013/11/22/fmdb-implimentation-ios-applications/ — sohil, Nov 30 '16 at 17:52
http://www.brianjcoleman.com/framework-using-fmdb-to-communicate-with-sqlite-databases/ — sohil, Nov 30 '16 at 17:52

score 6 · Accepted Answer · edited May 23 '17 at 12:30

6

Is your id an int?

If so, try to pass in an NSNumber to the formatted update statement.

This post can convert NSString to NSNumber.

How to convert an NSString into an NSNumber

 [db executeUpdate:@"DELETE FROM tbl1 WHERE entry = ?", idNumber];

Here's a related post:

How to delete a row in a sqlite database table?

Also, make sure you check for errors after your statements:

NSLog(@"Error %d: %@", [db lastErrorCode], [db lastErrorMessage]);

edited May 23 '17 at 12:30

Community

1
1

answered Feb 28 '12 at 04:26

bryanmac

38,941
11
91
99

the ID is a string, and i have tried converting to an NSInteger now, but still no dice. errors return everything is good. – rick Feb 28 '12 at 14:35

score 2 · Answer 2 · answered Feb 28 '12 at 06:24

2

Try printing out your values before the query to make sure that you're getting what you expect.

Also, opening and closing your database so often isn't a good idea- just open it once and assign it to an ivar in your instance, then then close it when you dealloc (or really need to close it).

-gus (the guy who wrote fmdb)

answered Feb 28 '12 at 06:24

ccgus

2,906
23
18

thanks @ccgus, i was printing the values and they were right. also, i will start opening and closing once each while the program runs - thank you for your tip (and thanks a TON for FMDB (and acorn))! – rick Feb 28 '12 at 14:34

score 0 · Answer 3 · answered Feb 28 '12 at 03:30

0

try adding:

[database beginTransaction];

before your CRUD block, and:

[database commit];

after executing an update/delete/insert operation. Plus executeUpdate is correct-

[database executeUpdate:sqlStat];

answered Feb 28 '12 at 03:30

Srikar Appalaraju

71,928
54
216
264

each statement has an implicit transaction right? you should only need that if you want a transaction to span calls ... – bryanmac Feb 28 '12 at 04:20
not that, I was under the impression that sqlite is transactional. So the `beginTransaction` & `commit` is needed for any update, insert or delete. correct me if I am wrong... – Srikar Appalaraju Feb 28 '12 at 04:50
http://sqlite.org/lang_transaction.html :: Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. – bryanmac Feb 28 '12 at 04:55

delete row from SQLite database with FMDB

3 Answers3