How to update and delete data from SQLite database in iOS

Hi guys, I’m really sorry that I didn’t complete this SQLite posts series in the last December and had to continue to the 2015 also. Anyway, I’m going to complete this series today by explaining Update and Delete functions as well as uploading source code to GitHub. If someone is directly jumps to this post, here are the links to previous posts which related to SQLite Create, Insert and Retrieve data using iOS application. 

To execute Update and Delete functions, I have created new interface in my storyboard. Here is the screenshot of it.

Figure 1

When you tap on a name which you want to edit or delete from the first UI, you will be redirected to this interface. Update and Delete are most similar to Insert function. So I will directly post the Update function here without any further explanations.

- (BOOL)updateName:(NSString *)name uniId:(NSString *)uniId gpa:(NSString *)gpa {
 if (debugEnable) NSLog(@"%s - %d", __PRETTY_FUNCTION__, __LINE__);
 
 const char *utf8Dbpath = [databasePath UTF8String];
 
 if (sqlite3_open(utf8Dbpath, &database) == SQLITE_OK) {
 
 NSString *updateQuery = [NSString stringWithFormat:@"update undergraduate_details_table set undergraduate_name_column='%@', undergraduate_gpa_column=%f where undergraduate_uni_id_column='%@'", name, [gpa doubleValue], uniId];
 
 const char *utf8UpdateQuery = [updateQuery UTF8String];
 
 sqlite3_prepare_v2(database, utf8UpdateQuery, -1, &statement, NULL);
 
 if (sqlite3_step(statement) == SQLITE_DONE) {
 
 sqlite3_reset(statement);
 return YES;
 } else {
 if (debugEnable) NSLog(@"%s - %d # sqlite3_step != SQLITE_DONE", __PRETTY_FUNCTION__, __LINE__);
 sqlite3_reset(statement);
 return NO;
 }
 } else {
 if (debugEnable) NSLog(@"%s - %d # Fail to open DB", __PRETTY_FUNCTION__, __LINE__);
 sqlite3_reset(statement);
 return NO;
 }
}

Code formatting sucks here. So please copy and paste it in to your Xcode or see the GitHub source for better format. As you can see in the above code snippet, you have nothing new SQLite functions to learn. In the Delete procedure also, only significant change is with the query string. Let’s look at it now.

- (BOOL)deleteUnderGrad:(NSString *)uniId {
 if (debugEnable) NSLog(@"%s - %d", __PRETTY_FUNCTION__, __LINE__);
 
 const char *utf8Dbpath = [databasePath UTF8String];
 
 if (sqlite3_open(utf8Dbpath, &database) == SQLITE_OK) {
 
 NSString *deleteQuery = [NSString stringWithFormat:@"delete from undergraduate_details_table where undergraduate_uni_id_column='%@'", uniId];
 
 const char *utf8DeleteQuery = [deleteQuery UTF8String];
 
 sqlite3_prepare_v2(database, utf8DeleteQuery, -1, &statement, NULL);
 
 if (sqlite3_step(statement) == SQLITE_DONE) {
 
 sqlite3_reset(statement);
 return YES;
 } else {
 if (debugEnable) NSLog(@"%s - %d # sqlite3_step != SQLITE_DONE", __PRETTY_FUNCTION__, __LINE__);
 sqlite3_reset(statement);
 return NO;
 }
 } else {
 if (debugEnable) NSLog(@"%s - %d # Fail to open DB", __PRETTY_FUNCTION__, __LINE__);
 sqlite3_reset(statement);
 return NO;
 }
}

That’s it for today’s lesson as well as for the SQLite database series with iOS. You can find the all necessary source code from my GitHub repository. Wish all of you for Happy Coding in 2015 🙂