SQLite Tutorial – Deleting Data

Sometimes we do need to delete data and this is what we will learn to do in this tutorial. If you haven’t read the part 1 of this tutorial you can read it here.
Deleting a row from UITableView
To delete data, we first need to delete the row from the table and then delete it from the UITableView, it is not necessary to do it in that order. Data can be deleted by using the edit button or by swiping your finger across the UITableViewCell.
When a row is deleted from the UITableView “commitEditingStyle” method is called which can be found in RootViewController.m.
This is how the source code looks like
- (void)tableView:(UITableView *)tv commitEditingStyle:(UITableViewCellEditingStyle)editingStyle
forRowAtIndexPath:(NSIndexPath *)indexPath {
if(editingStyle == UITableViewCellEditingStyleDelete) {
//Get the object to delete from the array.
Coffee *coffeeObj = [appDelegate.coffeeArray objectAtIndex:indexPath.row];
[appDelegate removeCoffee:coffeeObj];
//Delete the object from the table.
[self.tableView deleteRowsAtIndexPaths:[NSArray arrayWithObject:indexPath] withRowAnimation:UITableViewRowAnimationFade];

We first check if the editingStyle is “UITableViewCellEditingStyleDelete” or not, if it is then we first delete the row from the database and then from the UITableView using “deleteRowsAtIndexPaths” method.
Since we have not yet implemented “removeCoffee” method, let’s do that now.
Deleting rows from the database
Declare “removeCoffee” in “SQLAppDelegate.h” file which takes a “Coffee” object as a parameter and returns void, the code looks like this

//Complete code listing not shown
- (void) removeCoffee:(Coffee *)coffeeObj;

The method does two things, sends a message to the Coffee Object to delete itself from the database and removes the object from the coffeeArray. The method is implemented in SQLAppDelegate.m file and looks like this

- (void) removeCoffee:(Coffee *)coffeeObj { //Delete it from the database.
[coffeeObj deleteCoffee];
//Remove it from the array.
[coffeeArray removeObject:coffeeObj];

The first line, it sends a “deleteCoffee” message to the coffee object, let’s see how the code for that looks like

//Method is declared in the header file.
//Full code listing not shown
- (void) deleteCoffee;

“deleteCoffee” is implemented in SQLAppDelegate.m file.

- (void) deleteCoffee { if(deleteStmt == nil) {
const char *sql = “delete from Coffee where coffeeID = ?”;
if(sqlite3_prepare_v2(database, sql, -1, &deleteStmt, NULL) != SQLITE_OK)
NSAssert1(0, @”Error while creating delete statement. ‘%s'”, sqlite3_errmsg(database));
//When binding parameters, index starts from 1 and not zero.
sqlite3_bind_int(deleteStmt, 1, coffeeID);
if (SQLITE_DONE != sqlite3_step(deleteStmt))
NSAssert1(0, @”Error while deleting. ‘%s'”, sqlite3_errmsg(database));

The “deleteStmt” as a static variable whose type is sqlite3_stmt. Deceleration shown below

#import "Coffee.h" static sqlite3 *database = nil;
static sqlite3_stmt *deleteStmt = nil;
@implementation Coffee

We first prepare the delete statement with the sql query “delete from Coffee where CoffeeID = ?”. The ‘?’ specifies that we have to pass a parameter to the statement, we do this using sqlite3_bind_int method since the parameter we have to pass is an int. Note that the index is specified as 1 and not 0 because when assigining parameters the index starts from 1 and not 0. sqlite_step is called to execute the delete statement and if it returns “SQLITE_DONE” it means that the row is deleted successfully. Click here to get a list of complete return codes by SQLite. We then reset the delete statement so it can be reused later, without having the need to build it.
Run your application and delete a row to test.
Deleting rows is very easy and straight forward. 


Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>