SQLite Tutorial - Adding data

Inserting data into SQLite database is a breeze with iPhone applications. In the third part of SQLite tutorials, I will show how to insert data into the database.

• SQLite Tutorial - Displaying data in a UITableView.
• SQLite Tutorial - Deleting data from UITableView.
• SQLite Tutorial - Inserting data into SQLite database.
• SQLite Tutorial - Loading data as required.
• SQLite Tutorial - Updating data in SQLite database.
• SQLite Tutorial - Saving images in the database

Introduction
Adding data into the database is very simple using the SQLite library, although there are some tweaks we have to perform if we want the application to behave in a certain way. This tutorial picks up its source code from the last tutorial in this series. This is how the "AddView" looks like

The work flow is as following, a user will click on the "Add" button on the left hand bar button item and the "AddView" will be presented to him. The user will enter the coffee name and price and click on "Save" on the navigation item on the right hand side to save it. Internally, it will call "save_Clicked" method which will create a "Coffee" object and set all the right properties. We then call "add_Coffee" method on "SQLAppDelegate" which will call "add_Coffee" method on the "Coffee" class and then it will add the object to the array. We then dismiss the add view controller and reload data on the table view in "viewWillAppear" method implemented in "RootViewController.m".

Creating the addCoffee Method
First thing we do is create the addCoffee method in the Coffee class which is responsible for inserting data in the database.

This is how the header file looks like (Complete code not shown)

- (void) addCoffee;

and the method is implemented in Coffee.m file

- (void) addCoffee {

if(addStmt == nil) {
const char *sql = "insert into Coffee(CoffeeName, Price) Values(?, ?)";
if(sqlite3_prepare_v2(database, sql, -1, &addStmt, NULL) != SQLITE_OK)
NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
}

sqlite3_bind_text(addStmt, 1, [coffeeName UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_double(addStmt, 2, [price doubleValue]);

if(SQLITE_DONE != sqlite3_step(addStmt))
NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
else
//SQLite provides a method to get the last primary key inserted by using sqlite3_last_insert_rowid
coffeeID = sqlite3_last_insert_rowid(database);

//Reset the add statement.
sqlite3_reset(addStmt);
}

The "add_Stmt" is declared as static sqlite3_stmt variable. It is finalized in finalizeStatements and this is how the code looks like

//Complete code listing not shown
#import "Coffee.h"

static sqlite3 *database = nil;
static sqlite3_stmt *deleteStmt = nil;
static sqlite3_stmt *addStmt = nil;

@implementation Coffee
...


+ (void) finalizeStatements {

if(database) sqlite3_close(database);
if(deleteStmt) sqlite3_finalize(deleteStmt);
if(addStmt) sqlite3_finalize(addStmt);
}

Coming back to addCoffee method, "add_Stmt" is built using the appropriate insert SQL code. To bind the coffee name the following method sqlite3_bind_text is used and sqlite3_bind_double is used to bind the price variable to the insert statement. Since the method only accepts a value of datatype double, we send doubleValue message to the receiver. Execute the statement using sqlite_step method and if it returns SQLITE_DONE then the row was successfully added to the database. We still do not have the primary key for the row which was inserted, which we can get by calling sqlite3_last_insert_rowid method and passing the database object. The "rowid" is only returned on column of type INTEGER PRIMARY KEY.

After adding the data in the database, we have to add the coffee object to the coffeeArray declared in SQLAppDelegate class. To do this we will declare a method called "add_Coffee" which will take a parameter of type Coffee and this method is called from "save_Clicked" method, which is called when the user clicks on the save button.

This is how the header file changes (Full code not shown)

//FileName: SQLAppDelegate.h
- (void) addCoffee:(Coffee *)coffeeObj;
...

addCoffee is implemented in SQLAppDelegate.m file and this is the code listing

- (void) addCoffee:(Coffee *)coffeeObj {

//Add it to the database.
[coffeeObj addCoffee];

//Add it to the coffee array.
[coffeeArray addObject:coffeeObj];
}

The first line calls the "addCoffee" method on the coffee object which we just created. The second line adds the object in the array.

Now we have to work with the Add View and its associated view controller.

Adding new UIView
Create a new view using the Interface Builder, I have named the view "AddView". Add two labels and two text boxes as shown in the figure below. For the text fields, Capitalize is set to "Words", Return Key is set to "Done" and set the Placeholder as "Coffee Name" and "Price" for the two respective text fields. You would set the properties in "Text Field Attributes" (Tools -> Inspector) using IB. Open "Connections Inspector" and create a connection from the delegate property to "File's Owner" object, do the same for both the text boxes. I find it hard to explain what goes in IB, so here is a screen shot of how it should look like. The "Text Field Connections" applies to both the text boxes.



Creating a UIViewController
Create a new view controller (using Xcode), the name of my file is "AddViewController". Create two variables of type UITextField with "IBOutlet" attribute so the variables show up in IB and create two methods called "save_Clicked" and "cancel_Clicked". This is how the header file should look like

#import <UIKit/UIKit.h>

@class Coffee;

@interface AddViewController : UIViewController {

IBOutlet UITextField *txtCoffeeName;
IBOutlet UITextField *txtPrice;
}

@end

Now that we have defined "AddViewController" set the File's Owner class as "AddViewController" in "Controller Identity", below is a screen shot of that.

Also link the text fields declared in the view controller to the objects on the "AddView", below is a screen shot of that

We are done with using Interface builder, feels good now that we can concentrate on code.

We now have to add two buttons "Cancel" and "Save" on the "UINavigationItem" of the "AddView". Let us do this in "viewDidLoad" method and this how the code looks like

- (void)viewDidLoad {
[super viewDidLoad];

self.title = @"Add Coffee";

self.navigationItem.leftBarButtonItem = [[[UIBarButtonItem alloc]
initWithBarButtonSystemItem:UIBarButtonSystemItemCancel
target:self action:@selector(cancel_Clicked:)] autorelease];

self.navigationItem.rightBarButtonItem = [[[UIBarButtonItem alloc]
initWithBarButtonSystemItem:UIBarButtonSystemItemSave
target:self action:@selector(save_Clicked:)] autorelease];

self.view.backgroundColor = [UIColor groupTableViewBackgroundColor];
}

Everything is self explanatory, we set the title, add two buttons and set the background of the view by passing groupTableViewBackgroundColor message to UIColor.

Since the view has only two text fields, it will be easier if the keypad is presented to the user when the view is loaded. Lets do this in "viewWillAppear" method and this is how the code looks like

- (void) viewWillAppear:(BOOL)animated {
[super viewWillAppear:animated];

//Set the textboxes to empty string.
txtCoffeeName.text = @"";
txtPrice.text = @"";

//Make the coffe name textfield to be the first responder.
[txtCoffeeName becomeFirstResponder];
}

Here we always set the text property of the text fields to empty string and we make the keypad show up for the coffeeName text field by passing becomeFirstResponder message.

Since the user can click "Done" the keyboard should be hidden and the method which gets called is textFieldShouldReturn and this is how the code looks like

- (BOOL)textFieldShouldReturn:(UITextField *)theTextField {

[theTextField resignFirstResponder];
return YES;
}

Please note that, I send "resignFirstResponder" message to the text field without finding out which textbox should be hidden. I do this because, the actual save happens in "save_clicked" method.

Before we look at "save_Clicked" method, this is how "cancel_Clicked" method looks like

- (void) cancel_Clicked:(id)sender {

//Dismiss the controller.
[self.navigationController dismissModalViewControllerAnimated:YES];
}

and this is how the "save_Clicked" method looks like

- (void) save_Clicked:(id)sender {

SQLAppDelegate *appDelegate = (SQLAppDelegate *)[[UIApplication sharedApplication] delegate];

//Create a Coffee Object.
Coffee *coffeeObj = [[Coffee alloc] initWithPrimaryKey:0];
coffeeObj.coffeeName = txtCoffeeName.text;
NSDecimalNumber *temp = [[NSDecimalNumber alloc] initWithString:txtPrice.text];
coffeeObj.price = temp;
[temp release];
coffeeObj.isDirty = NO;
coffeeObj.isDetailViewHydrated = YES;

//Add the object
[appDelegate addCoffee:coffeeObj];

//Dismiss the controller.
[self.navigationController dismissModalViewControllerAnimated:YES];
}

We create a new coffee class and set all the properties, isDetailViewHydrated is set as YES because all the data is in memory and isDirty is set as NO because the row will be inserted in the database after setting all the properties. The view is dismissed by passing "dismissModalViewControllerAnimated" message to the receiver.

We still have to add the "Add" button to the "RootViewController" and add the code to show the "AddView". This is how the header file changes for "RootViewController"

#import <UIKit/UIKit.h>

@class Coffee, AddViewController;

@interface RootViewController : UITableViewController {

SQLAppDelegate *appDelegate;
AddViewController *avController;
UINavigationController *addNavigationController;
}

@end

Do not forget to import "AddViewController.h" in "RootViewController.m" file. The "Add" button is added in the "viewDidLoad" method and this is how the code changes

- (void)viewDidLoad {
[super viewDidLoad];

self.navigationItem.rightBarButtonItem = self.editButtonItem;

self.navigationItem.leftBarButtonItem = [[UIBarButtonItem alloc]
initWithBarButtonSystemItem:UIBarButtonSystemItemAdd
target:self action:@selector(add_Clicked:)];

appDelegate = (SQLAppDelegate *)[[UIApplication sharedApplication] delegate];

self.title = @"Coffee List";
}

When "Add" is clicked "add_Clicked" method is called and this is how the code looks like

- (void) add_Clicked:(id)sender {

if(avController == nil)
avController = [[AddViewController alloc] initWithNibName:@"AddView" bundle:nil];

if(addNavigationController == nil)
addNavigationController = [[UINavigationController alloc] initWithRootViewController:avController];

[self.navigationController presentModalViewController:addNavigationController animated:YES];
}

The reason we present the "AddView" using "addNavigationController" because when we want a custom UINavigationItem to show up on the AddView and that is why we initialize "addNavigationController" with "avController" and present it using "presentModalViewController".

Run your application to insert data in the database.

There is however a problem with the design here, a User can click on Edit and nothing restricts him/her from clicking the add button. The code below fixes the issue

- (void)setEditing:(BOOL)editing animated:(BOOL)animated {

[super setEditing:editing animated:animated];
[self.tableView setEditing:editing animated:YES];

//Do not let the user add if the app is in edit mode.
if(editing)
self.navigationItem.leftBarButtonItem.enabled = NO;
else
self.navigationItem.leftBarButtonItem.enabled = YES;
}


The method setEditing is called when the edit button is clicked. We send the same message to the parent class and the tableview, also disable or enable the leftBarButtonItem if the tableview is in edit mode.

Conclusion
As we can see inserting data in SQLite databases is very easy to do.

Happy Programming,
iPhone SDK Articles

---

Attachments

• Download the source code used in this article

Suggested Readings

• SQLite Tutorial - Deleting data
• SQLite Tutorial - Inserting data into SQLite database
• SQLite Tutorial - Saving images in the SQLite database

• SQLite Tutorial - Displaying data in a UITableView.
• SQLite Tutorial - Deleting data from UITableView.
• SQLite Tutorial - Inserting data into SQLite database.
• SQLite Tutorial - Loading data as required.
• SQLite Tutorial - Updating data in SQLite database.
• SQLite Tutorial - Saving images in the database