SQLite Tutorial - Saving images in the database

There maybe times when you need to save more than just text in the database, like a file or an image. SQLite versions 3.0 and later allows you to store BLOB data in a column. Using the data type BLOB we can store large objects like an image or a file. Read on to learn more.

• 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
Using BLOB data type we can store an image in the SQLite database. The data that actually gets stored in the database is the bytes that make up an image or a file. The is the sixth tutorial in SQLite tutorial series and borrows its source code from the previous one. We will change the "DetailView" by adding a new section which will be used to display or change the image. The image can only be changed when the view is in the edit mode. We will use a UIImagePickerController to display the photo album and to select an image.

Changing the database
Let's start by adding a new column to our "Coffee" database. Name the column "CoffeeImage" and set its data type to BLOB. This is how the database schema looks like in SQLite manager


Changing the Coffee Class
Add a new property to the "Coffee" class which will hold the image from the database. This is how the header and the implementation files look like

//Coffee.h
//Complete code listing not shown
@interface Coffee : NSObject {

NSInteger coffeeID;
NSString *coffeeName;
NSDecimalNumber *price;
UIImage *coffeeImage;
//Complete Code listing not shown ...
}
@property (nonatomic, readonly) NSInteger coffeeID;
@property (nonatomic, copy) NSString *coffeeName;
@property (nonatomic, copy) NSDecimalNumber *price;
@property (nonatomic, retain) UIImage *coffeeImage;
//Complete Code listing not shown ...


The new property is synthesized and released in the dealloc method

//Coffee.m
//Complete code listing now shown
@synthesize coffeeID, coffeeName, price, isDirty, isDetailViewHydrated, coffeeImage;

- (void)setCoffeeImage:(UIImage *)theCoffeeImage {

self.isDirty = YES;
[coffeeImage release];
coffeeImage = [theCoffeeImage retain];
}

- (void) dealloc {

[coffeeImage release];
[price release];
[coffeeName release];
[super dealloc];
}


Changing the Detail View
Let's add a new row to the detail view, which will be used to display the image or to change the image in edit mode. Since the style of the table view placed on the detail view is set to "Grouped", we will return three sections in numberOfSectionsInTableView and this is how the source code looks like

//DetailViewController.h
- (NSInteger)numberOfSectionsInTableView:(UITableView *)tblView {
return 3;
}

Let's also display a title for the new section in tableView:titleForHeaderInSection. The code changes like this

//DetailViewController.m
- (NSString *)tableView:(UITableView *)tblView titleForHeaderInSection:(NSInteger)section {

NSString *sectionName = nil;

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

return sectionName;
}

Displaying the photo album
Now we need to display a photo album when the section is selected in edit mode. From this album we can select a image to be saved in the database. Using a UIImagePickerController we can capture an image from the camera or the photo library on the device. UIImagePickerController class manages all the user interactions and all we have to do is display and dismiss it. Before we can display the view, we need to check if the interface is supported by calling isSourceTypeAvailable class method. The delegate of the UIImagePickerController should also confirm to UINavigationControllerDelegate and UIImagePickerControllerDelegate. In this case we will set the delegate of UIImagePickerController to "DetailViewController" and this is how the header and implementation file changes

//DetailViewController.h
@class Coffee, EditViewController;

@interface DetailViewController : UIViewController <UINavigationControllerDelegate, UIImagePickerControllerDelegate, UITableViewDataSource, UITableViewDelegate> {

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

UIImagePickerController *imagePickerView;
}

@property (nonatomic, retain) Coffee *coffeeObj;

@end

The variable "imagePickerView" is released in the dealloc method and the code listing is now shown here. We will initialize "imagePickerView" in viewDidLoad method and this is how the code changes

//DetailViewController.m
- (void)viewDidLoad {
[super viewDidLoad];

self.navigationItem.rightBarButtonItem = self.editButtonItem;

//Configure the UIImagePickerController
imagePickerView = [[UIImagePickerController alloc] init];
imagePickerView.delegate = self;
}

From the above code, "imagePickerView" is initialized and its delegate is set to self. This way "DetailViewController" will be notified of all the user actions on the picker controller. First, we need to display the view and it is done in tableView:didSelectRowAtIndexPath and this is how the code looks like

//DetailViewController.m
- (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.
switch(indexPath.section)
{
case 0:
evController.keyOfTheFieldToEdit = @"coffeeName";
evController.editValue = coffeeObj.coffeeName;

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

//Push the edit view controller on top of the stack.
[self.navigationController pushViewController:evController animated:YES];
break;
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];
break;
case 2:
if([UIImagePickerController isSourceTypeAvailable:UIImagePickerControllerSourceTypePhotoLibrary])
[self presentModalViewController:imagePickerView animated:YES];
break;
}
}

First confirm if the photo library is available on the device or not, if it is then present the view to the user. The method imagePickerController:didFinishPickingImage:editingInfo is called when an image is selected. This is how the code looks like

//DetailViewController.m
- (void)imagePickerController:(UIImagePickerController *)picker didFinishPickingImage:(UIImage *)image editingInfo:(NSDictionary *)dictionary {

coffeeObj.coffeeImage = image;
[tableView reloadData];
[picker dismissModalViewControllerAnimated:YES];
}

We get the selected image and set the "coffeeImage" property of the selected coffee. The table view is then reloaded and the picker view is dismissed.

Display the coffee image
We will display the coffee image in tableView:cellForRowAtIndexPath and this is how the code looks like

//DetailViewController.m
- (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;
break;
case 1:
cell.text = [NSString stringWithFormat:@"%@", coffeeObj.price];
break;
case 2:
cell.text = @"Change Image";
if(coffeeObj.coffeeImage != nil)
cell.image = coffeeObj.coffeeImage;
break;
}

return cell;
}

If the index of the section is 2 then we set the title of the cell to "Change Image" and the image to the coffee image.

Saving image in the SQLite database
Until now we haven't saved the image in the database because we only save data when the application is being terminated or if a memory warning is received. The data is saved in "saveAllData" method as seen in the previous tutorial. Let's change this method to save the image in the database which now has a new column called "CoffeeImage" to hold the image as bytes. This is how the code looks like

//Coffee.m
- (void) saveAllData {

if(isDirty) {

if(updateStmt == nil) {
const char *sql = "update Coffee Set CoffeeName = ?, Price = ?, CoffeeImage = ? 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]);

NSData *imgData = UIImagePNGRepresentation(self.coffeeImage);

int returnValue = -1;
if(self.coffeeImage != nil)
returnValue = sqlite3_bind_blob(updateStmt, 3, [imgData bytes], [imgData length], NULL);
else
returnValue = sqlite3_bind_blob(updateStmt, 3, nil, -1, NULL);

sqlite3_bind_int(updateStmt, 4, coffeeID);

if(returnValue != SQLITE_OK)
NSLog(@"Not OK!!!");

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

sqlite3_reset(updateStmt);

isDirty = NO;
}

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

isDetailViewHydrated = NO;
}

We first rewrite the update query to include the CoffeeImage column. We also need a way to get the image data as bytes and this is where UIImagePNGRepresentation method helps us. We then bind the BLOB parameter using sqlite3_bind_blob method. The first parameter takes the update statement, the second parameter takes the index of the parameter value, the third one is the actual data itself, the fourth one is the length of the data which is being saved, and a pointer to a method which is responsible to clean up the data. If sqlite3_bind_blob method does not return SQLITE_OK then we display an error in NSLog. If there is no error while saving the data then we have successfully saved the image in the SQLite database on the iPhone/iPod.

Getting an image from the SQLite database
We now have to get the data from the database when the detail view is loaded. This is done in "hydrateDetailViewData" method and this is how the code changes

//DetailViewController.m
- (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, CoffeeImage 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;

NSData *data = [[NSData alloc] initWithBytes:sqlite3_column_blob(detailStmt, 1) length:sqlite3_column_bytes(detailStmt, 1)];

if(data == nil)
NSLog(@"No image found.");
else
self.coffeeImage = [UIImage imageWithData:data];

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

//Reset the detail statement.
sqlite3_reset(detailStmt);

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

The select query is changed to include CoffeeImage column and we use sqlite3_column_blob method to load the BLOB data into variable of type NSData. WE create an image of type NSData from imageWithData class method. The rest of the code works as described above.

This tutorial wasn't designed to display imags anywhere and is a direct result of all the emails I got asking, how to save images in the SQLite database. So if you choose an image which does not fit in the section, it will take up all the space on the view.

Conclusion
This tutorial explains how to save and read images from a SQLite database. We can use the same functionality to save files in the database. I hope you had fun and learnt something new with this tutorial.

Happy Programming,
iPhone SDK Articles

---

Attachments

• Download the source code used in this article

Suggested Readings

• SQLite Tutorial - Selecting Data
• SQLite Tutorial - Updating data

• 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