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.
Creating the addCoffee Method This is how the header file looks like (Complete code not shown) and the method is implemented in Coffee.m file if(addStmt == nil) { sqlite3_bind_text(addStmt, 1, [coffeeName UTF8String], -1, SQLITE_TRANSIENT); if(SQLITE_DONE != sqlite3_step(addStmt)) //Reset the add statement. The “add_Stmt” is declared as static sqlite3_stmt variable. It is finalized in finalizeStatements and this is how the code looks like static sqlite3 *database = nil; @implementation Coffee if(database) sqlite3_close(database); 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)
addCoffee is implemented in SQLAppDelegate.m file and this is the code listing //Add it to the database. //Add it to the coffee array. 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 @class Coffee; @interface AddViewController : UIViewController { IBOutlet UITextField *txtCoffeeName; @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. 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 self.title = @"Add Coffee"; self.navigationItem.leftBarButtonItem = [[[UIBarButtonItem alloc] self.navigationItem.rightBarButtonItem = [[[UIBarButtonItem alloc] 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 //Set the textboxes to empty string. //Make the coffe name textfield to be the first responder. 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 [theTextField resignFirstResponder]; 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 //Dismiss the controller. and this is how the “save_Clicked” method looks like SQLAppDelegate *appDelegate = (SQLAppDelegate *)[[UIApplication sharedApplication] delegate]; //Create a Coffee Object. //Add the object //Dismiss the controller. 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” @class Coffee, AddViewController; @interface RootViewController : UITableViewController { SQLAppDelegate *appDelegate; @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 self.navigationItem.rightBarButtonItem = self.editButtonItem; self.navigationItem.leftBarButtonItem = [[UIBarButtonItem alloc] 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 if(avController == nil) if(addNavigationController == nil) [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 [super setEditing:editing animated:animated]; //Do not let the user add if the app is in edit mode. 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 Happy Programming, Suggested Readings
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”.
First thing we do is create the addCoffee method in the Coffee class which is responsible for inserting data in the database.- (void) addCoffee;- (void) addCoffee {
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_double(addStmt, 2, [price doubleValue]);
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);
sqlite3_reset(addStmt);
}//Complete code listing not shown
#import "Coffee.h"
static sqlite3_stmt *deleteStmt = nil;
static sqlite3_stmt *addStmt = nil;
...+ (void) finalizeStatements {
if(deleteStmt) sqlite3_finalize(deleteStmt);
if(addStmt) sqlite3_finalize(addStmt);
}//FileName: SQLAppDelegate.h
- (void) addCoffee:(Coffee *)coffeeObj;
...- (void) addCoffee:(Coffee *)coffeeObj {
[coffeeObj addCoffee];
[coffeeArray addObject:coffeeObj];
}
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>
IBOutlet UITextField *txtPrice;
}
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.- (void)viewDidLoad {
[super viewDidLoad];
initWithBarButtonSystemItem:UIBarButtonSystemItemCancel
target:self action:@selector(cancel_Clicked:)] autorelease];
initWithBarButtonSystemItem:UIBarButtonSystemItemSave
target:self action:@selector(save_Clicked:)] autorelease];
}- (void) viewWillAppear:(BOOL)animated {
[super viewWillAppear:animated];
txtCoffeeName.text = @"";
txtPrice.text = @"";
[txtCoffeeName becomeFirstResponder];
}- (BOOL)textFieldShouldReturn:(UITextField *)theTextField {
return YES;
}- (void) cancel_Clicked:(id)sender {
[self.navigationController dismissModalViewControllerAnimated:YES];
}- (void) save_Clicked:(id)sender {
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;
[appDelegate addCoffee:coffeeObj];
[self.navigationController dismissModalViewControllerAnimated:YES];
}#import <UIKit/UIKit.h>
AddViewController *avController;
UINavigationController *addNavigationController;
}- (void)viewDidLoad {
[super viewDidLoad];
initWithBarButtonSystemItem:UIBarButtonSystemItemAdd
target:self action:@selector(add_Clicked:)];
}- (void) add_Clicked:(id)sender {
avController = [[AddViewController alloc] initWithNibName:@"AddView" bundle:nil];
addNavigationController = [[UINavigationController alloc] initWithRootViewController:avController];
}- (void)setEditing:(BOOL)editing animated:(BOOL)animated {
[self.tableView setEditing:editing animated:YES];
if(editing)
self.navigationItem.leftBarButtonItem.enabled = NO;
else
self.navigationItem.leftBarButtonItem.enabled = YES;
}
As we can see inserting data in SQLite databases is very easy to do.
iPhone SDK Articles
Attachments
Related posts:
- SQLite Tutorial – Deleting Data In the second part of the SQLite tutorial, I will show how to delete data from UITableView and from the database. This picks up from the first part of the SQLite tutorial, where I show how to select some data from the database and display it to the user. SQLite...
- SQLite Tutorial – Selecting Data Like any other applications you may have the need to store data in some kind of a database. With iPhone applications we can use SQLite for FREE. In the first part of this tutorial, I will show you how to do some basic operations using SQLite database. SQLite Tutorial –...
- SQLite Tutorial – Loading data as required. In this tutorial, I was supposed to show how to update data in a SQLite database, but when I finished writing it, I realized that it is a big one and that is why, I renamed this tutorial to “Loading data as required”. SQLite Tutorial – Displaying data in a...
- 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. SQLite Tutorial – Displaying data in a UITableView. SQLite Tutorial – Deleting data from UITableView. SQLite Tutorial – Inserting...
- Insert Into Sqlite Insert Into Sqlite This will probably only take you a few hours – maybe a day at the most! I want the text file content that I’ve attached insert into sqlite. The text file contains 3 “side by side” Koran (Qur’an) translations. I’ve attached one old sqlite database that I...
This entry was posted on Wednesday, August 11th, 2010 at 4:55 am and is filed under I-phone. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site. You can skip to the end and leave a response. Pinging is currently not allowed.
Recent Comments