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

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.

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.

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.

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

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.


Person (ReadPersons)

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

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

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.

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.

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.

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.

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.

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 


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

Comments are closed.