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”.

In this tutorial, I will show you how to select a row and load the Coffee class details in a detail view. According to the design of this app, we only load data when we need it. Now we load the price of coffee from the SQLite database, when a row is selected. This is the fourth tutorial in SQLite tutorial series and it borrows its source code from the previous tutorials. This is how the detail view looks like

Selecting data from database
Since we need to get the price of a coffee from the database and show it on the detail view, let’s declare a method called “hydrateDetailViewData” in Coffee class and this is how the method deceleration looks like in Coffee.h File

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

and the method is implemented in Coffee.m, this is how the source code looks like

- (void) hydrateDetailViewData {

//If the detail view is hydrated then do not get it from the database.
if(isDetailViewHydrated) return;

if(detailStmt == nil) {
const char *sql = "Select price from Coffee Where CoffeeID = ?";
if(sqlite3_prepare_v2(database, sql, -1, &detailStmt, NULL) != SQLITE_OK)
NSAssert1(0, @"Error while creating detail view statement. '%s'", sqlite3_errmsg(database));

sqlite3_bind_int(detailStmt, 1, coffeeID);

if(SQLITE_DONE != sqlite3_step(detailStmt)) {

//Get the price in a temporary variable.
NSDecimalNumber *priceDN = [[NSDecimalNumber alloc] initWithDouble:sqlite3_column_double(detailStmt, 0)];

//Assign the price. The price value will be copied, since the property is declared with "copy" attribute.
self.price = priceDN;

//Release the temporary variable. Since we created it using alloc, we have own it.
[priceDN release];
NSAssert1(0, @"Error while getting the price of coffee. '%s'", sqlite3_errmsg(database));

//Reset the detail statement.

//Set isDetailViewHydrated as YES, so we do not get it again from the database.
isDetailViewHydrated = YES;

As always, detailStmt is declared as static and is of type sqlite3_stmt, this is the code

static sqlite3_stmt *addStmt = nil;
static sqlite3_stmt *detailStmt = nil;

@implementation Coffee
//Complete code listing not shown.

Let’s see what happens in hydrateDetailViewData, the first thing we check is if detailStmt is nil or not, if it is then we prepare the statement using sqlite3_prepare_v2 function with the correct SQL query. The query “Select price from Coffee Where CoffeeID = ?” takes one parameter represented by one ‘?’. Parameters are assigned to the statement using the function sqlite3_bind_int, since the parameter is going to be the type of int. Notice, that the index is represented as 1 and not 0, this is because when assigning parameters the index starts from 1. The statement is executed using sqlite3_step method and SQLITE_DONE is returned on success. We get the price in a temporary variable priceDN, using the function sqlite3_column_double, then our temporary variable is initialized using initWithDouble method. We assign the temporary variable to the price variable and then release it, which we have to do since the object was created using the alloc method. Remember price property is declared in the Coffee class using the copy attribute and not retain, this way we do not increment the count on priceDN but copy it to the price variable. The statement is reset using sqlite3_reset, so it can be used again without preparing the statement again.

Creating the detail view
Now that we have a routine to get the price of a coffee, let’s create the detail view and the detail view controller. First let’s create the UIViewController, in Xcode (Select the Classes Folder) create a new file which inherits from UIViewController (File -> New File -> Select UIViewController SubClass) and name it “DetailViewController”. Lets add some variables and properties to it, coffeeObj to hold the Coffee object and tableView which will be placed on the Detail View (to be created in IB). This is how the header file looks like

@class Coffee;

@interface DetailViewController : UIViewController <UITableViewDataSource, UITableViewDelegate> {

IBOutlet UITableView *tableView;
Coffee *coffeeObj;

@property (nonatomic, retain) Coffee *coffeeObj;


In the implementation file of the DetailViewController, the property is synthesized and both the variables are released in the dealloc method.

Let’s see what happens in IB. Create a new view in IB and name it “DetailView”. Place a UITableView on the view and change the style property to “Grouped”. Since I always have a hard time explaining what goes in IB, here are the screen shots of the connections.

Class Identity for File’s Owner Object

Controller Connections for File’s Owner

Table View Connections

Selecting a row
We are doing all this work to display the detail view when a row is selected, lets look at the code on how to do that

- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath {
// Navigation logic -- create and push a new view controller

if(dvController == nil)
dvController = [[DetailViewController alloc] initWithNibName:@"DetailView" bundle:nil];

Coffee *coffeeObj = [appDelegate.coffeeArray objectAtIndex:indexPath.row];

//Get the detail view data if it does not exists.
//We only load the data we initially want and keep on loading as we need.
[coffeeObj hydrateDetailViewData];

dvController.coffeeObj = coffeeObj;

[self.navigationController pushViewController:dvController animated:YES];


We have a new variable defined here called “dvController” and it is defined in the header file like this and it is also released in the dealloc method.

//Complete code listing not shown
DetailViewController *dvController;

Coming back to “didSelectRowAtIndexPath” method, the first thing we do is check if dvController is nil or not, if it is we initialize it. We then get the coffee object from the array and send “hydrateDetailViewData” message to get the price of the coffee. The coffee object is assigned to the detail view controller so it is available to the detail view. The detail view is shown to the user using pushViewController method.

Now that a row can be selected, we have to let the user know that it can be done. We can do that by setting the accessoryType property of the cell in cellForRowAtIndexPath, this is how the code looks like now

- (UITableViewCell *)tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath {

static NSString *CellIdentifier = @"Cell";

UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
if (cell == nil) {
cell = [[[UITableViewCell alloc] initWithFrame:CGRectZero reuseIdentifier:CellIdentifier] autorelease];

//Get the object from the array.
Coffee *coffeeObj = [appDelegate.coffeeArray objectAtIndex:indexPath.row];

//Set the coffename.
cell.text = coffeeObj.coffeeName;

//Set the accessory type.
cell.accessoryType = UITableViewCellAccessoryDisclosureIndicator;

// Set up the cell
return cell;

Displaying data in the detail view

Let’s see what we have to do in the detail view. From the detail view image (at the top), we can tell that coffeeName and price shows up in a table view with “Coffee Name” and “Price” as the title of the section name. The first thing we will do is, set the title of the view with the coffee name and we do this in viewWillAppear method, this is how the source code looks like, which is pretty self explanatory.

- (void) viewWillAppear:(BOOL)animated {

[super viewWillAppear:animated];

self.title = coffeeObj.coffeeName;

[tableView reloadData];

Since we only have to show two fields on the view, we will return 2 in numberOfSectionsInTableView method and as only one coffee data will be displayed, we return 1 in numberOfRowsInSection method. This is how the source code looks like

- (NSInteger)numberOfSectionsInTableView:(UITableView *)tblView {
return 2;

- (NSInteger)tableView:(UITableView *)tblView numberOfRowsInSection:(NSInteger)section {
return 1;

Next thing we have to do is give names to our section, which is set in titleForHeaderInSection method. It gets called n number of times, where n is the number returned in numberOfSectionsInTableView. This is how the source code looks like

- (NSString *)tableView:(UITableView *)tblView titleForHeaderInSection:(NSInteger)section {

NSString *sectionName = nil;

switch (section) {
case 0:
sectionName = [NSString stringWithFormat:@"Coffee Name"];
case 1:
sectionName = [NSString stringWithFormat:@"Price"];

return sectionName;

Now we set the text of the cell which is returned in cellForRowAtIndexPath, which gets called n number of times where n is the number returned in numberOfRowsInSection. I would like to mention one thing here, for example if your table view can only show 4 rows and we return 10 in numberOfRowsInSection then, cellForRowAtIndexPath is only called 4 times because that is what the user sees. When the user scrolls to see more data, it is then cellForRowAtIndexPath is called again to display additional data.

This is how cellForRowAtIndexPath method looks like

- (UITableViewCell *)tableView:(UITableView *)tblView cellForRowAtIndexPath:(NSIndexPath *)indexPath {
static NSString *CellIdentifier = @"Cell";

UITableViewCell *cell = [tableView dequeueReusableCellWithIdentifier:CellIdentifier];
if (cell == nil) {
cell = [[[UITableViewCell alloc] initWithFrame:CGRectZero reuseIdentifier:CellIdentifier] autorelease];

switch(indexPath.section) {
case 0:
cell.text = coffeeObj.coffeeName;
case 1:
cell.text = [NSString stringWithFormat:@"%@", coffeeObj.price];

return cell;

NOTE: When this post was published, I had a error in case 1. I had earlier set the price of the coffee by passing stringValue message to the receiver, which works now but gives an error in the next tutorial.

In this method, after getting UITableViewCell, we find out the index of the section from indexPath object. If it is 0 then we set the coffeeName property, if it is 1 then we set the price property. Price of the coffee is set as string to the text property of the cell.

By following this design pattern, where we only load the data we need to show to the user and loading additional data as required, we adhere to the Apple’s recommended way of accessing data.

Happy Programming,
iPhone SDK Articles


Suggested Readings

Leave a Reply

Your email address will not be published.