Using SQLite in your IOS applications part III

In this article we introduce a new feature into the example application that was built in the last article  “Using SQLite in your IOS applications part II” that will allow us to explore different data types found in SQLite.  A photo of the contact will be added to the database.

modify storyboards

the first step is to add a UIimageView to the AddContactViewController, ViewContactViewController, and the UpdateContactViewController panes in the storyboard.

  • Add a new section and change the title to “Photo”.
  • drag a UIImageView onto the new tableViewCell in the photo section and resize to suite your tastes

Add a new UIImageView to the app to display the contacts photo

Once you have modified the storyboards, we need to hook up the UIImageView to a property within each controllers class object.

In each of the controller classes, we add the following property

@property ( nonatomic, weak ) IBOutlet UIImageView *photo;

After adding the property, remember to connect the IBOutlet for each photo property to their UIImageViews on the storyboard.  When that is done, compile the code to fix and build errors or warnings, and we are done here.

Modify database schema

For this sample, we introduce a new data type into the schema.  The new database scheme  introduces a new SQL data types supported by SQLite, the blob.  Blobs are primarily used for storing binary data, like photos and short videos, audio, etc..  Basically, a blob can hold any piece of data that cannot be represented by any of the other SQL data types supported by SQL.  For this sample we are using a blob to story a PNG image of a contact.

         firstname Varchar DEFAULT NULL,
         lastname Varchar DEFAULT NULL,
         phone Varchar DEFAULT NULL,
         photo BLOB DEFAULT NULL);

If you have decided to try and modify the code from the previous sample, you will need to alter the database instead of creating a new one. To do this, we need to add code to the SQLiteManager class to alter the Contacts table to include the Blob for the photo.

    NSString *path = [self databasePath];

    // if the path does not exist, then we need to initialize the database.
    if ([fileManager fileExistsAtPath:path] == YES)
        // check to see if we have attempted to upgrade the database before.
        // we only want to run the ALTER command once after an upgrade.
        if ( [[NSUserDefaults standardUserDefaults] boolForKey:@"FirstRun"] == NO) {
            // the file exists, so all we need to do is alter the existing table.
            sqlite3 *database = [self newConnection];
            // the database file does not exist, so we need to create one.
            if (database)
                // load the SQL commands that will create the database
                NSString *sqlCommands = @"ALTER TABLE Contacts ADD photo BLOB DEFAULT NULL;";

                // any error message that sent back from sqlite when creating the database will go here.
                char *szErrorMessage = NULL;

                // execute the sql commands
                int error = sqlite3_exec(database, [sqlCommands UTF8String], NULL, NULL, &szErrorMessage);

                if (error == SQLITE_OK)
                    NSLog(@"successfully created database");
                    [[NSUserDefaults standardUserDefaults] setBool:YES forKey:@"FirstRun"];
                else {
                    NSLog(@"Failed with error %s", sqlite3_errmsg(database));

                [self closeConnection:database];

All we do here is add a new Blob to the end of the table record. Which brings up an important point. It is safer to Add new records to a table, but deleting columns can be problematic with the underlying flat file system used to store the physical database on the file space in your device. So be careful when modifying your tables.

Code changes for photos

The next step is to modify the Person object to include a UIImage to hold the photo retrieved from the database.  Each of the categories that were implemented are affected and need to be changed to accommodate the photo column retrieved in each SQL query.

Person object modifications

In the person.h header file, add a property for the photo, and declare the overloaded setter selector for the photo property.

@property (nonatomic, copy) UIImage  *photo;

-(void) setPhoto:(UIImage *)value;

In the person.m file we add the implementation for the setPhoto selector.

-(void) setPhoto:(UIImage *)value
    if (photo == nil) {
        photo = value;
    else {
        photo = value;
        self.dirty = YES;

With these changes the person object can now track the photo of the contact in the database.  Now we need to look at the categories that are affected by the changed database schema.

Person (AddNewPerson)

In the last article, we used a SQL string to insert records into the database. This time we use sqlite3_bind functions to bind the parameters in the prepared statement that is ready to execute. The process is really very simple. The column data is filled in left to right, each column is identified numerically.
With simple data types like integers and floating point numbers, the binding is straight forward. Like strings, we need to label the blob data as SQLITE_TRANSIENT to signal the database engine on how to treat the memory we are passing to it. When inserting large pictures, these transactions can take time. this is a good candidate for a background thread or something to be scheduled as a background task on the run loop.

+(id)addNewPersonWithFirstName:(NSString *)firstName LastName:(NSString *)lastName phoneNumber:(NSString *)phoneNumber andPhoto:(UIImage *)photo intoDatbase:(sqlite3 *)database
    Person *contact = nil;

    // here we build a string for the complete SQL query and values to insert.
    NSString *query = @"INSERT INTO Contacts (firstname, lastname, phone, photo) VALUES (?,?,?,?)";
    int success = -1;
    sqlite3_stmt *statement = nil;

    int error = sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, NULL);
    if ( error != SQLITE_OK)
        // error
        NSLog(@"Error failed to prepare sql with err %s", sqlite3_errmsg(database));
        // Now bind the variable to the prepared statement
        sqlite3_bind_text(statement, 1, [firstName UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 2, [lastname UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(statement, 3, [phoneNumber UTF8String], -1, SQLITE_TRANSIENT);

        // now we store the image into the database as a PNG for better compression.
        sqlite3_bind_blob(statement, 4, [UIImagePNGRepresentation(photo) bytes], [UIImagePNGRepresentation(photo) length], SQLITE_TRANSIENT);

        // execute the query
        success = sqlite3_step( statement );
        if ( success == SQLITE_DONE )
            // successful insertion into the database, now create the person object.
            success = (int)sqlite3_last_insert_rowid(database);
            contact = [[Person alloc] initWithPrimaryKey:success AndDatabase:database];
            contact.first = firstName;
            contact.last = lastName;
   = phoneNumber;
   = photo;

    // cleanup
    statement = NULL;

    return contact;


Person (ReadPersons)

The modifications to the read persons category are pretty easy.  We have to modify the SQL SELECT statement to include the photo.

        sqlite3_stmt *statement = NULL;

        int error = sqlite3_prepare_v2(database, "SELECT idx, firstname, lastname, phone, photo FROM Contacts", -1, &statement, NULL);

then we process the bytes retrieved from the query results and put them into a UIImage for local storage.

                UIImage *photo = nil;

                // We need two parameters to get the BLOB data.  the actual blob and the length in bytes.
                NSData *imageData = [NSData dataWithBytes:sqlite3_column_blob(statement, 4) length:sqlite3_column_bytes(statement, 4)];
                if (imageData) {

                    // got it, stuff it into a UIImage
                    photo = [UIImage imageWithData:imageData];

                // insert into an object
                Person *contact = [[Person alloc] initWithPrimaryKey:primaryKey AndDatabase:database];
                if (contact) {
                    contact.first = firstName;
                    contact.last = lastName;
           = phone;
           = photo;

                    [results addObject:contact];

Person (WritePerson)

Writing the photo out is just as easy.  We need to get the photo represented in a byte sequence for storage in a blob.  To do this on IOS we need to get access to the raw bytes stored within the UIImage.  The IOS SDK provides a pair of functions UIIMagePNGRepresentation() and UIImageJPEGRepresentation().  These functions return a NSData Object that will have a pointer to either the Jpeg or PNG representation of the UIImage.  From there we can use the NSData object to get access to the raw bytes and the length in bytes of the image.

First we modify the prepared statement.
        sqlite3_stmt *statement = NULL;

        const char *query = "UPDATE Contacts SET firstname = ?, lastname = ?, phone = ?, photo = ? WHERE idx = ?";
        error = sqlite3_prepare_v2(_database, query, -1, &statement, NULL);
Bind the image to the blob.  Make sure to label the memory properly so the database engine knows how to handle it properly, there is no automatic garbage collection here.
            // now we store the image into the database as a PNG for better compression.
            sqlite3_bind_blob(statement, 4, [UIImagePNGRepresentation( bytes], (int)[UIImagePNGRepresentation( length], SQLITE_TRANSIENT);
            sqlite3_bind_int(statement, 5, (int)self.primaryKey);

Once you have bound the image to the SQL prepared statement, you can execute it. This can be time consuming, so like the AddNewPerson category, this is a good candidate for background processing.

Implement UIImagePickerController

the only thing left to do is implement the UIImagePickerController.  This class is used to select photos, take video, or use the camera to take snapshots, which is what we are going to do here.  Implementing this controller we step outside the storybook code and implement this controller manually.

To implement the control we need to implement the protocols from the UIImagePickerControllerDelegate, UINavigationControllerDelegate, and the UIActionSheetDelegate to present a simple choice dialog.

@interface AddContactViewController : UITableViewController <uiimagepickercontrollerdelegate, uiactionsheetdelegate,="" uinavigationcontrollerdelegate="">
@property (weak, nonatomic) IBOutlet UITextField *firstName;
@property (weak, nonatomic) IBOutlet UITextField *lastName;
@property (weak, nonatomic) IBOutlet UITextField *phone;
@property (weak, nonatomic) IBOutlet UIImageView *photo;

@property (nonatomic, assign) sqlite3 *database;

@property (nonatomic, weak) id delegate;



We also add a new selector takePhoto. In this method we implement the action sheet that we use to let the user select what kind of photo to take.
The result of the action sheet is used to configure the UIImagePickerController.

#pragma mark - UIActionSheetDelegate
- (void)actionSheet:(UIActionSheet *)actionSheet clickedButtonAtIndex:(NSInteger)buttonIndex
    BOOL bCancel = NO;
    UIImagePickerController *photoPicker = [[UIImagePickerController alloc] init];
    photoPicker.delegate = self;
    photoPicker.allowsEditing = YES;

    // we are processing a photo
    switch (buttonIndex)
        case 0: // take photo
            if ([UIImagePickerController isSourceTypeAvailable:UIImagePickerControllerSourceTypeCamera]) {
                photoPicker.sourceType = UIImagePickerControllerSourceTypeCamera;
            // cancel
            bCancel = YES;
    if ( !bCancel )
        // present the view controller.
        [self presentViewController:photoPicker animated:YES completion:nil];

Once we have configured the UIImagePickerController the UIActionSheetDelegate launches the view. Once the view has loaded, you will be able to take a picture.  The slideshow gallery demonstrates the application flow.

This slideshow requires JavaScript.

Once you have picked a photo or canceled, the UIImagePickerController will signal one of protocols that it defines. Pretty simple really, there are two protocol methods to implement, one for success, the other for canceling.

#pragma mark - UIImagePickerControllerDelegate
- (void)imagePickerController:(UIImagePickerController *)picker didFinishPickingMediaWithInfo:(NSDictionary *)info
    UIImage *selectedImage = (UIImage *)[info valueForKey:UIImagePickerControllerEditedImage];
    if ( selectedImage != nil )
    { = selectedImage;

    [self dismissViewControllerAnimated:YES completion:nil];

- (void)imagePickerControllerDidCancel:(UIImagePickerController *)picker
    [self dismissViewControllerAnimated:YES completion:nil];

implementing the UIImagePickerController is really not too difficult. Once you have the code hooked up, we need a way to call the action sheet. For this example, the selecting of a table cell is used to invoke the action sheet.

- (void)tableView:(UITableView *)tableView didSelectRowAtIndexPath:(NSIndexPath *)indexPath
    // make sure we only invoke the UIImagePickerController on the photo table view cell.
    if (indexPath.row == 0 && indexPath.section == 0) {
        [self takePhoto];

    UIActionSheet *dialog = nil;

    // present the action sheet that offers the user the choices for photos.
    // open a dialog with an OK and cancel button
    dialog = [[UIActionSheet alloc] initWithTitle:@""
                                otherButtonTitles:@"Take Photo", @"Choose Existing Photo", @"Edit Photo", nil];

    dialog.actionSheetStyle = UIActionSheetStyleBlackTranslucent;

    // show the action dialog on top of the photo.
    [dialog inView:self.view animated:YES];


And that's it. Using blobs in your SQLite database is really quite easy, almost as easy as handling string data.

Source code

The source code for this blog entry is available on GitHub.


IOS Human interface guidelines : Modal Contexts

Binary Data Programming guide for Cocoa

IOS developer library : About the Camera and Photo Library



Using SQLite in your IOS applications part II

Using SQLite in your IOS applications part I

Building Sqlite into your IOS applications 

About these ads

One thought on “Using SQLite in your IOS applications part III

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s