SQLite Tutorial – Updating data

In this tutorial, I will show you how to edit data and update its contents in the SQLite database. We will look at how to edit one field at a time.

In the detail view, the user will be able to edit the fields by clicking the edit button and selecting a row at a time to load the edit view. When the data is updated in the edit view, we will mark the object as dirty. Data will only be saved in the database when the application is being terminated or when it receives a memory warning.

Editing Data
First thing we have to do is, place the edit button on the left bar. We add the button in viewDidLoad method and this is how the source code looks like for DetailViewController.m

- (void)viewDidLoad {
[super viewDidLoad];

self.navigationItem.rightBarButtonItem = self.editButtonItem;

When the edit button is clicked, we will hide the back button and let the user know that the row can be selected to edit. This is how the code is going to look like

- (void)setEditing:(BOOL)editing animated:(BOOL)animated {
[super setEditing:editing animated:animated];
[self.navigationItem setHidesBackButton:editing animated:animated];

[tableView reloadData];

The method setEditing is called when the user clicks on the edit button and it is where we hide or show the back button. The table view is also refreshed, so we can set the accessory type. To hide the back button use setHidesBackButton message. This is how the code looks like

- (UITableViewCellAccessoryType)tableView:(UITableView *)tv accessoryTypeForRowWithIndexPath:(NSIndexPath *)indexPath {
// Show the disclosure indicator if editing.
return (self.editing) ? UITableViewCellAccessoryDisclosureIndicator : UITableViewCellAccessoryNone;

accessoryTypeForRowWithIndexPath gets called to use a disclosure control for the specified row. Based on the editing variable status, an indicator is set. You may have noticed that, the row can be selected even if the user did not click on the edit view. The following code takes care of that

- (NSIndexPath *)tableView:(UITableView *)tv willSelectRowAtIndexPath:(NSIndexPath *)indexPath {
// Only allow selection if editing.
return (self.editing) ? indexPath : nil;

willSelectRowAtIndexPath tells the delegate that a specified row is about to be selected and is called before didSelectRowAtIndexPath. Return the indexPath selected if the user clicks on edit and nil if the done button is clicked.

Now we handle the event didSelectRowAtIndexPath, which is called when a row is selected. The user will select a row to load the edit view where a value can be changed.

Creating the edit view

Create a new view in Interface Builder and name it “EditView”. Place a text field on the view and set the property capitalize to Words and uncheck “Clear when editing begings” field. leave all the other properties as default. Then add UINavigationBar from the library to the EditView nib file and place two bar button items on the right and left bar button items. Set the style of the left button item to Cancel and the right one to Save.

Creating the edit view controller
In Xcode create a new UIViewController and name it “EditViewController”. This is how the header file looks like

#import <UIKit/UIKit.h>
@interface EditViewController : UIViewController {

IBOutlet UITextField *txtField;
NSString *keyOfTheFieldToEdit;
NSString *editValue;
id objectToEdit;

@property (nonatomic, retain) id objectToEdit;
@property (nonatomic, retain) NSString *keyOfTheFieldToEdit;
@property (nonatomic, retain) NSString *editValue;

- (IBAction) save_Clicked:(id)sender;
- (IBAction) cancel_Clicked:(id)sender;


Let’s finish working with the Interface Builder first, by making all the right connections.

Text Field Properties

File’s Owner Class Identity

Edit View Nib File

Navigation Bar with two buttons

File’s Owner Connections

Coming back to the EditViewController header file, notice that we do not have a reference to the Coffee class any where, the edit view controller does not know anything about what object it is going to edit and we want to keep it this way. Let’s look at the properties defined in the header file and see what they are meant to to. txtField is a reference to the text field placed on the view, keyOfTheFieldToEdit is the key of the field we are going to edit or the property name as string, editValue is going to be the value we are about to edit (this is the value which will be displayed on the text field) and objectToEdit declared as type id which is going to be the Coffee object itself. save_Clicked and cancel_Clicked handle the events when save or cancel are clicked.

Selecting a row to edit
Let’s go back to the Detail view controller and look at what goes on in didSelectRowAtIndexPath method. This is how the code looks like

- (void)tableView:(UITableView *)tblView didSelectRowAtIndexPath:(NSIndexPath *)indexPath {

//Keep track of the row selected.
selectedIndexPath = indexPath;

if(evController == nil)
evController = [[EditViewController alloc] initWithNibName:@"EditView" bundle:nil];

//Find out which field is being edited.
case 0:
evController.keyOfTheFieldToEdit = @"coffeeName";
evController.editValue = coffeeObj.coffeeName;
case 1:
evController.keyOfTheFieldToEdit = @"price";
evController.editValue = [coffeeObj.price stringValue];

//Object being edited.
evController.objectToEdit = coffeeObj;

//Push the edit view controller on top of the stack.
[self.navigationController pushViewController:evController animated:YES];


selectedIndexPath is a new variable declared in the Coffee header file, which is of type NSIndexPath. The variable is used to keep track of which row was selected, so we can deselect it when the detail view is shown. evController is declared in the header file and is of type EditViewController. This is how the code looks like in the DetailViewController header file

@class Coffee, EditViewController;

@interface DetailViewController : UIViewController <UITableViewDataSource, UITableViewDelegate> {

IBOutlet UITableView *tableView;
Coffee *coffeeObj;
NSIndexPath *selectedIndexPath;
EditViewController *evController;

@property (nonatomic, retain) Coffee *coffeeObj;


Coming back to didSelectRowAtIndexPath, first thing we do is keep track of the row selected then initialize the controller if it is nil. We then find out which row to edit and assign “keyOfTheFieldToEdit”, “editValue” and objectToEdit properties. keyOfTheFieldToEdit takes the name of the property, edit value takes the value to edit and objectToEdit takes the coffee object. Since objectToEdit is declared as id and not Coffee, this view can be used to edit any object. We will see how to edit the coffee object soon. At last, the edit view controller is pushed to the top of the stack.

Display data in edit view
To make the edit view look pretty, let’s give it a background color. In viewDidLoad method, set the view’s background color to groupTableViewBackgroundColor, and this is how the code looks like

- (void)viewDidLoad {
[super viewDidLoad];

self.view.backgroundColor = [UIColor groupTableViewBackgroundColor];

Back to EditViewController, in viewWillAppear method, we are going to set the text of the text field and also the title of the view. The title of the view is going to be the property we are about to edit, which we get it from keyOfTheFieldToEdit variable, this also becomes the placeholder text for the text field. Since, we are only going to edit one field in this view, it makes sense to have the keyboard show up when the view is loaded. This way the user does not have to tap the text field. We do this by passing the message becomeFirstResponder to text field. This is how the code looks like

- (void)viewWillAppear:(BOOL)animated {

[super viewWillAppear:YES];

self.title = [self.keyOfTheFieldToEdit capitalizedString];
txtField.placeholder = [self.keyOfTheFieldToEdit capitalizedString];

txtField.text = self.editValue;

[txtField becomeFirstResponder];

The code is pretty self explanatory, we get the property name as capitalized string by passing the message capitalizedString and show the text field by passing becomeFirstResponder to the text field.

Now we have to handle save_Clicked and cancel_Clicked methods. This is how the code looks like for the cancel method

- (IBAction) cancel_Clicked:(id)sender {

[self.navigationController popViewControllerAnimated:YES];

We simply pop the present view controller from the stack to show the detail view controller, by passing popViewControllerAnimated message.

This is how the save_Clicked method looks like

- (IBAction) save_Clicked:(id)sender {

//Update the value.
//Invokes the set<key> method defined in the Coffee Class.
[objectToEdit setValue:txtField.text forKey:self.keyOfTheFieldToEdit];

//Pop back to the detail view.
[self.navigationController popViewControllerAnimated:YES];

Two lines, without any comments. This is what I love about iPhone programming, we can update any object by just passing a message to the object and asking it to change some properties. setValue does all the tricks, which will set the property of the receiver specified by a given key to a given value (text borrowed from documentation).

However, I found out that I was not able to set the value of boolean property isDirty to YES using setValue:forKey and that is why I had to write my own version of setCoffeeName and setPrice. According to the design of this app, we will only save data which has been changed in memory, to the database when the application is terminated or the app receives a memory warning. This way we reduce the round trip from the app to the database, making it a little faster.

This is how the setCoffeeName and setPrice methods look like

- (void) setCoffeeName:(NSString *)newValue {

self.isDirty = YES;
[coffeeName release];
coffeeName = [newValue copy];

- (void) setPrice:(NSDecimalNumber *)newNumber {

self.isDirty = YES;
[price release];
price = [newNumber copy];

Since the property is declared with copy attribute, we get the copy of the newValue and assign it to the local variable. The isDirty property is also set to YES.

When the user clicks on save, the detail view is shown but the row which was selected is still highlighted. To deselect the row, pass deselectRowAtIndexPath message to the table view, the message is passed in viewWillDisappear method and this how the code looks like. You can call this method in viewWillAppear method too

- (void)viewWillDisappear:(BOOL)animated {

[tableView deselectRowAtIndexPath:selectedIndexPath animated:YES];

We still have to update the data in the SQLite database and write code for it. Data is only updated when the app is about to be terminated or when a memory warning is received. applicationWillTerminate method is called when the app is shutting down, which is declared in SQLAppDelegate.m file. This is how the code will change

- (void)applicationWillTerminate:(UIApplication *)application {
// Save data if appropriate

//Save all the dirty coffee objects and free memory.
[self.coffeeArray makeObjectsPerformSelector:@selector(saveAllData)];

[Coffee finalizeStatements];

The method makeObjectsPerformSelector sends saveAllData message to each object in the array. So we are going to declare a method in the Coffee class header file called “saveAllData” and write the implementation in the implementation file (Coffee.m file)

//Complete code listing not shown
- (void) hydrateDetailViewData;
- (void) saveAllData;

Code in Coffee.m file looks like this

- (void) saveAllData {

if(isDirty) {

if(updateStmt == nil) {
const char *sql = "update Coffee Set CoffeeName = ?, Price = ? Where CoffeeID = ?";
if(sqlite3_prepare_v2(database, sql, -1, &updateStmt, NULL) != SQLITE_OK)
NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(database));

sqlite3_bind_text(updateStmt, 1, [coffeeName UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_double(updateStmt, 2, [price doubleValue]);
sqlite3_bind_int(updateStmt, 3, coffeeID);

if(SQLITE_DONE != sqlite3_step(updateStmt))
NSAssert1(0, @"Error while updating. '%s'", sqlite3_errmsg(database));


isDirty = NO;

//Reclaim all memory here.
[coffeeName release];
coffeeName = nil;
[price release];
price = nil;

isDetailViewHydrated = NO;

We only save the data of the objects which has been changed in memory. updatestmt is declared as static and is of type sqlite3_stmt.

//Complete code listing now shown.
static sqlite3_stmt *detailStmt = nil;
static sqlite3_stmt *updateStmt = nil;

@implementation Coffee

We prepare the update statement if it is nil, then assign the variables to the update statement where the index starts from 1 and not 0. The method sqlite3_step is called and if it returns SQLITE_DONE then we know that the update was a success. Finally we reset the updatestmt so it can be reused later. The update statement like any other statement is finalized in finalizeStatements method which looks like this, which is implemented in Coffee.m file.

+ (void) finalizeStatements {

if (database) sqlite3_close(database);
if (deleteStmt) sqlite3_finalize(deleteStmt);
if (addStmt) sqlite3_finalize(addStmt);
if (detailStmt) sqlite3_finalize(detailStmt);
if (updateStmt) sqlite3_finalize(updateStmt);

When the application receives a memory warning, “applicationDidReceiveMemoryWarning” is called which is implemented in SQLAppDelegate.m file and this is how the source code looks like

- (void)applicationDidReceiveMemoryWarning:(UIApplication *)application {

//Save all the dirty coffee objects and free memory.
[self.coffeeArray makeObjectsPerformSelector:@selector(saveAllData)];

Here the message finalizeStatements is not passed to the Coffee class because the application is not shutting down.

This tutorial shows how to save data in a edit view by passing the setValue message, shows how to save dirty data to the database when the application is terminated or when it receives a memory warning. This also concludes the SQLite tutorial series.

Happy Programming,
iPhone SDK Articles


Suggested Readings

Leave a Reply

Your email address will not be published.