iOS: SQLite Insert Query not working

Question

Im trying to insert some values to my sqlite database. The db is already of the doc folder on the phone. I cant figure out what is going wrong. I set trace executing but the db tells me that it does not have any error. Can someone help me?

if([[TRSharedLocalDatabase openDatabase] executeUpdateWithFormat:@"INSERT INTO event (title,date,address,latitude,longitude,location,facebookID,picPath,description) VALUES (%@,%@,%@,%@,%@,%@,%@,%@,%@)",event.title ,event.date, event.address, [NSNumber numberWithDouble:event.geoPoint.longitude], [NSNumber numberWithDouble:event.geoPoint.latitude], event.location.objectId, event.facebookID ,picPath ,event.description]) {    
    NSLog(@"Ok");  
} else {  
    NSLog(@"Not Ok");  
}  

+(FMDatabase *)openDatabase {  
     NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];  
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"4PartyLocalSystem.sqlite"];  
    **FMDatabase *database = [FMDatabase databaseWithPath:writableDBPath];**  
    [database open];  
    [database setTraceExecution:YES];  
    return database;   
}

2013-08-06 13:21:42.499 4Party[13018:907] executeUpdate: INSERT INTO event (title,date,address,latitude,longitude,location,facebookID,picPath,description) VALUES (?,?,?,?,?,@,?,?,?)

Rafael, it would be helpful if you provided more information about the TRSharedLocalDatabase. Can you share the code for the method openDatabase? It will be difficult to help you without knowing more about what that code is doing, exactly. — Aaron
– Aaron, Commented Aug 6, 2013 at 16:01
The openDatabase method is just for opening the DB. Here it is:NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); NSString *documentsDirectory = [paths objectAtIndex:0]; NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"4PartyLocalSystem.sqlite"]; FMDatabase *database = [FMDatabase databaseWithPath:writableDBPath]; [database open]; [database setTraceExecution:YES]; return database; — Rafael Gonçalves
– Rafael Gonçalves, Commented Aug 6, 2013 at 16:04
Please add the code to the question by editing it rather than add it in a comment. It is virtually impossible to read your code in the comment — Adam Richardson
– Adam Richardson, Commented Aug 6, 2013 at 16:07
Thanks, can you put that in the original post? Also add the code for the databaseWithPath:. You will also want to consider adding some error handling to your code on the SQLite operations. That will give you more information about specific errors. — Aaron
– Aaron, Commented Aug 6, 2013 at 16:07
I tried logging the sql errors, but the db always return me "not an error". @Aaron — Rafael Gonçalves
– Rafael Gonçalves, Commented Aug 6, 2013 at 16:11

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Two observations:

You should examine the lastErrorMessage of the database if you have an error. It helps if you store the database pointer in a separate variable if you're going to do multiple calls to the database before closing it.

You definitely don't want to call [TRSharedLocalDatabase openDatabase] multiple times for one session with your database. Or you could refactor it to conform to a singleton pattern.
Ideally, you should use ? placeholders in your SQL with executeUpdate method, not printf-style placeholders with executeUpdateWithFormat (see the warning in the executeUpdateWithFormat documentation). If not, your text fields with characters that need to be escaped (e.g. quotation mark) won't be. (This also protects you against SQL injection attacks.)

Thus:

FMDatabase *database = [TRSharedLocalDatabase openDatabase];
if (!database) {
    NSLog(@"Unable to open database");
    return;
}

if([database executeUpdate:@"INSERT INTO event (title,date,address,latitude,longitude,location,facebookID,picPath,description) VALUES (?,?,?,?,?,?,?,?,?)",event.title ,event.date, event.address, [NSNumber numberWithDouble:event.geoPoint.longitude], [NSNumber numberWithDouble:event.geoPoint.latitude], event.location.objectId, event.facebookID ,picPath ,event.description]) {    
    NSLog(@"Ok");  
} else {  
    NSLog(@"Not Ok: %@", [database lastErrorMessage]);  
}  

[database close];

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Aug 6, 2013 at 16:46

Rob

441k74 gold badges851 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rafael Gonçalves Over a year ago

THANKSSS!!! I stored the FMDatabase in a variable and then the DB could log the lastErrorMessage!! Now i fixed the query!!

Collectives™ on Stack Overflow

iOS: SQLite Insert Query not working

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related