FMDB: add a new column and insert data

Question

In my application, I am using FMDatabase . I am trying to add new column to existing table and then insert data in this column. When I added the Alter Table column, I had no errors, but when I try to insert data into the table, I got an error related to a newly added column that does not 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 is the problem? how to find out why a new column is not added?

Answer 1

I don't see anything in the simplified code example above to explain the error message. The problem may be in another place (or, perhaps, in the process of simplifying the selection of code, you have eliminated the source of the problem).

What was said, I had several reactions:

• You said:

  I tried to set the result of this query to "BOOL Success" and print the results, and I got YES

  I do not know how this is possible, because executeQuery returns FMResultSet not a BOOL . This is a moot point, though, because your ALTER TABLE statement must be executed with executeUpdate not executeQuery . (The latter should work, but the former is more logical.)

• I do not understand why you removed the error check. This is the best way to identify problems.

• Totally unrelated, but you should never use stringWithFormat when building SQL. You open yourself up for SQL crashes as a result of unexpected input (for example, what if one of your values ​​has double quotes inside it). Even worse, you are subjecting yourself to SQL injection attacks. Use instead ? placeholders in SQL, and then just pass the values ​​as parameters to the executeUpdate (or executeQuery ) method.

So the following code worked fine for me:

 BOOL success; FMDatabase *db = [FMDatabase databaseWithPath:databasePath]; if (![db open]) { NSLog(@"open failed"); return; } if (![db columnExists:@"placeName" inTableWithName:@"MYPLACES"]) { success = [db executeUpdate:@"ALTER TABLE MYPLACES ADD COLUMN placeName TEXT"]; NSAssert(success, @"alter table failed: %@", [db lastErrorMessage]); } NSString *insertSQL = @"INSERT INTO MYPLACES (placeID, placeName) VALUES (?, ?)"; success = [db executeUpdate:insertSQL, placeIDValue, placeNameValue]; NSAssert(success, @"insert failed: %@", [db lastErrorMessage]); [db close]; 

Since I don’t see what might cause the problem you are describing, I can only offer you one step through this code in your debugger and make sure that it matches your path.

Answer 2

Swift 3 Code:

  let db = FMDatabase(path: Util.getPath(databasePath) guard db != nil else { return } db!.open() if !(db!.columnExists("tableName", columnName: "USERID")) { let alterTable = "ALTER TABLE tableName ADD COLUMN USERID TEXT" if db!.executeUpdate(alterTable, withArgumentsIn: nil) { printf("new column added") } else { printf("issue in operation") } db!.close() 

May help someone. Continue coding ..