FMDB Update query not working

Question

I have an FMDB update query that is not working. Here is my code:

FMDatabase *db = [self openDatabase];

if (![db open]) {
    return;
}

NSString *updateMeeting = [NSString stringWithFormat:@"UPDATE meetings SET MEETING_DESCRIPTION=\"%@\" WHERE MEETING_ID=\"%@\"",meeting.meetingDescription, meeting.meetingId];

[db beginTransaction];
[db executeQuery:updateMeeting];
[db commit];
[db close];

These are the things i've checked:

The properties I pass are objects and are not nil.
Some places online use \"%@\", some use '%@', some use %@ and some use ?. None of which worked for me.

I NSLoged my string and it looks like this:

UPDATE meetings SET MEETING_DESCRIPTION="AAA12" WHERE MEETING_ID="791D8251-2FC4-498B-85B3-C1002C04E329:F1C40061-1308-4179-B72E-7E3EEDB85E1A"

I thought maybe my data base cant find this meetingID so I ran a SELECT query for this messageID and found it.

I'm pretty much hopeless. Can anyone think of something i haven't tried?

Thanks

Glad you found the `executeUpdate` vs `executeQuery` issue. A couple of unrelated observations: 1. Definitely use the `?` placeholders (without quotes) and pass `meetingDescription` and `meetingId` as parameters to `executeUpdate` method. This is critical when performing SQL that is inserting any user input (because `stringWithFormat` will not properly escape quotes in the user input, whereas the `?` placeholder approach bypasses this problem entirely). — Rob, Aug 26 '14 at 22:03
2. The `beginTransaction` and `commit` are unnecessary when performing a single `UPDATE` statement (SQLite automatically commits every update statement as you perform them). If doing many update SQL statement in single transaction, then `beginTransaction` and `commit` are very useful, but when doing a single `UPDATE` statement, it's unnecessary (although no harm is done). 3. I'd suggest you always check the return values from these FMDB methods otherwise, you're flying blind. In the case of an `UPDATE` statement, you might also want to call `[db changes]` to confirm how many rows were updated. — Rob, Aug 26 '14 at 22:04

score 3 · Accepted Answer · answered Aug 26 '14 at 07:53

3

Ok guys,

I found the answer thanks to my colleague.

The method [db executeQuery:updateMeeting] is wrong, instead you should use [db executeUpdate:updateMeeting];

This is annoying cause I thought that Update is also a query but what the hack... It works now :)

answered Aug 26 '14 at 07:53

Nimrod Shai

1,149
13
26

Correct. The `executeUpdate` not only prepares the SQL statement, but also calls `sqlite3_step` to actually perform the SQL. The `executeQuery` method, on the other hand, only prepares the SQL (and binds any values you pass to it), relying upon `FMResultSet` method `next` to actually call `sqlite3_step` as you iterate through the result set. – Rob Aug 26 '14 at 21:55

score 1 · Answer 2 · edited May 23 '17 at 11:52

1

You should use this

[db executeUpdate:@"INSERT INTO myTable VALUES (?)", yourVar];

instead of

stringWithFormat

Also, I use this for transactions

FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:YOUR_PATH];

[queue inTransaction:^(FMDatabase *db, BOOL *rollback) {
    [db executeUpdate:@"UPDATE meetings SET MEETING_DESCRIPTION=? WHERE MEETING_ID=?",meeting.meetingDescription, meeting.meetingId"];

    if (!db) {
        NSLog(@"Some problems... again...")
        *rollback = YES;
        return;
    } }];

If you need global queue here you have solution FMDatabaseQueue Error: database is locked

edited May 23 '17 at 11:52

Community

1
1

answered Aug 26 '14 at 21:24

Quver

1,408
14
21

Good counsel. It is very good practice to use `?` placeholders rather than `stringWithFormat`. It's obviously not the source of the bug, but the OP's code will fail if he happens to have quotes in it! – Rob Aug 26 '14 at 21:33

virus · Answer 3 · 2014-08-27T13:53:06.497

0

Try this may be this can be fix your issue. Happy coding!!!!

FMDatabase *db = [self openDatabase];

    if (![db open]) {
        return;
    }

    NSString *updateMeeting = [NSString stringWithFormat:@"UPDATE meetings SET MEETING_DESCRIPTION = ? WHERE MEETING_ID = ?"];

    NSArray *paramList = @[meeting.meetingDescription, meeting.meetingId];
    [db beginTransaction];
    [db executeQuery:updateMeeting withArgumentsInArray:paramList];
    [db commit];
    [db close];

edited Aug 27 '14 at 13:53

answered Aug 26 '14 at 07:18

virus

1,203
13
21

Yes, this should use `executeUpdate` in order to work. But the idea of using `?` placeholders in the SQL is an excellent suggestion. If you don't want to create the `paramList`, you can also use the variadic rendition of `executeUpdate`, namely, `[db executeUpdate:updateMeeting, meeting.meetingDescription, meeting.meetingId]`. – Rob Aug 26 '14 at 22:19

FMDB Update query not working

3 Answers3