FMDB: add new column and insert data

Question

In my app I'm using FMDatabase. I'm trying to add new column to existing table and then insert data in that column. When I added the column by Alter Table, I got no errors but when I'm trying to insert data in the table, I got error complaining about the newly added column that doesn't exist.

Here is my code

NSString *databasePath = [myDB getPlacesDBPath];

FMDatabase *db = [FMDatabase databaseWithPath:databasePath];

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

if (![db columnExists:@"placeName" inTableWithName:@"MYPLACES"])
{
    [db executeQuery:@"ALTER TABLE MYPLACES ADD COLUMN placeName TEXT"];
    // I tried to set the result of this query in "BOOL Success" and print the results and I got YES    
}

//Note: I have more than 12 columns but I didn't post all of them here
NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO  MYPLACES  ( placeID ,  placeName)  VALUES (\"%@\", \"%@\")", placeIDValue, placeNameValue ];

[db executeUpdate:insertSQL];

if ([db hadError]) {
    NSLog(@"error : %@",[db lastError]);
}

[db close];

I got the following error:

error : Error Domain=FMDatabase Code=1 "table MYPLACES has no column named placeName" UserInfo=0xe340920 {NSLocalizedDescription=table MYPLACES has no column named placeName}

What's the problem? how to know why the new column is not added?

user_9466697 · Answer

The problem is that -[FMDatabase executeQuery:] does not execute submitted instructions, it only prepares the statement.

To execute them you need to perform a "step".

In FMDB that is done by -[FMResultSet next], internally - by call to sqlite3_step( ).

Since -executeUpdate: (in contrast to -executeQuery:) does that for you and finalizes the statement - it may be more appropriate for this purpose.

So if you really need to use -executeQuery:

FMResultSet *rs = [db executeQuery:@"ALTER TABLE MYPLACES ADD COLUMN placeName TEXT"];
[rs nextWithError:&error]; // error handling is recommended here
[rs close];

But -executeUpdate: is more concise

BOOL success = [db executeUpdate:@"ALTER TABLE MYPLACES ADD COLUMN placeName TEXT"];

FMDB: add new column and insert data

3 Answers