Data Persistence

Blog Author


Published Date

21st September, 2018




  • Share this blog :

Data Persistence Using SQLite

Key Skills & Concepts

  • Creating a database and adding data to it
  • Including the database in Xcode
  • Reading from a database
  • Making a database writable
  • Inserting a record
  • Updating a record
  • Deleting a record

  The SQLite database is a popular open-source database written in C. The database is small and designed for embedding in an application. Unlike a database such as Oracle, SQLite is a C library included when compiling a program. SQLite is part of the standard open-source Linux/BSD server stack, and as OS X is essentially FreeBSD, it was only natural Apple chose SQLite as the iPhone’s embedded database.

Adding a SQLite Database

Adding a SQLite database to your project involves two steps. First, you must create the database. In this chapter’s first task, you create a database using the Firefox SQLite Manager plug-in. Second, you must add the SQLite library to your Xcode project. The first task also illustrates adding the SQLite library to your Xcode project. After creating the database and loading it, you can then use the database programmatically using its C programming interface.

Creating a Simple Database Using FireFox SQLite Manager

  1. If you don’t already have Firefox, download and install it.
  2. Select Add-ons from the Tools menu (Figure 16-1).
  3. Select Get Add-ons, type SQLite in the search box, and install SQLite
  4. Once installed and you have restarted Firefox, select Tools | SQLite



Figure 16-1 Adding SQLite Manager to Firefox


  1. Select the New icon (the blank paper graphic), and create a new database named Note SQLite Manager automatically adds the .sqlite extension.
  2. Click Create Table and create a new table named
  3. Add three columns: id, name, and Make id an INTEGER and check Primary Key and Autoinc check boxes.
  4. Make name a VARCHAR and check only Allow
  5. Make photo a BLOB and check only Allow
  6. Your screen should resemble Figure 16-2.
  7. Click OK and the SQLite Manager generates the database table.


 Figure 16-2 Creating a database using SQLite Manager

  NOTE SQLite does not enforce foreign key relationships. You must instead write triggers manually to enforce foreign key relationships. SQLite does not support right outer joins or full outer joins. SQLite views are read-only.  

  1. Click the Browse & Search tab, and then click the Add Record Button.
  2. In the action sheet, leave id Name the name Icon One. Notice the small paperclip beside photo. Move your mouse over the paperclip, and the tooltip should say ―Add File as a Blob‖ (Figure 16-3). Click the paperclip and add a photo from the book’s Resources folder. If the photo column doesn’t say something like BLOB (Size: 65984), the file was not added as a blob.
  3. Click OK, and the record is Add another record, selecting any image from this book’s Resources folder.
  4. From the menu, select Database | Close Database and close the Exit and quit Firefox.


Figure 16-3 Adding a record using SQLite Manager


  1. Open Xcode and create a new View-based Name the application MyDBProject.
  2. Select Frameworks in Groups & Right-click Frameworks and select Add | Existing Frameworks from the pop-up menu.
  3. Navigate to your iPhone Simulator’s sdk
  4. Navigate to usr/lib and add 0.dylib to the project.
  5. Add the database file to the Resources in Groups & Like a photo, check the Copy Items check box.
  6. This task is Do not delete the project or database, as you use them for this chapter’s remainder.

NOTE On my computer, adding binary data using SQLite Manager is buggy. Sometimes it works, sometimes not. If getting the file’s content added is problematic, a workaround is to add the record and insert Null for the blob’s value. After inserting, update the row, and add the file’s content. Updating the blob seems more stable in SQLite Manager.

Basic SQLite Database Manipulation

If you have ever used a database from within a programming language, SQLite database manipulation using C should seem intuitive. You open the database. You create a prepared statement containing an SQL string. That statement might have one or more parameters you bind values to. After binding, you execute the statement. If the statement returns results, you loop through each record and load the record’s column values into your program’s variables. After looping through all records, you finalize the statement, and, if you are finished with the database, you close the database. The steps are similar for most languages and databases.

Opening the Database

You open a database using the sqlite3_open, sqlite_open16, or sqlite3_open_v2 commands. This chapter uses the sqlite3_open command exclusively. The sqlite3_open command takes a database filename as a UTF-8 string and opens the database. Listing 16-1, taken from the SQLite’s online documentation, lists the sqlite_open3 method signature.     Listing 16-1 The sqlite3_open method signature (from SQLite online documentation) int sqlite3_open( const char *filename, /* Database filename (UTF-8) * / sqlite3 **ppDb /* OUT: SQLite db handle */ ); The method returns an integer as the method’s success or failure code. Listing 16-2, from the SQLite online documentation, lists several common result codes. Listing 16-2 SQLite return codes (taken from SQLite online documentation) #define SQLITE_OK #define SQLITE_ERROR #define SQLITE_READONLY database */ #define SQLITE_INTERRUPT #define SQLITE_IOERR occurred */ #define SQLITE_CANTOPEN #define SQLITE_MISMATCH #define SQLITE_ROW ready */ #define SQLITE_DONE executing */ 0 /* Successful result */ 1 /* SQL error or missing database */ 8/* Attempt to write a readonly 9/* Operation terminated by 10/* Some kind of disk I/O error 14 /* Unable to open the database file */ 20 /* Data type mismatch */ 100/* sqlite3_step() has another row 101/* sqlite3_step() has finished

Statements, Preparing Statements,and Executing Statements

There are two primary ways of executing SQL statements using SQLite’s C interface. One method is the sqlite3_exec method. Although a powerful method, it is more advanced C programming, and so this chapter uses the sqlite3_stmt structure and the sqlite3_prepare_v2 and sqlite3_step statements instead of the sqlite3_exec function.

The SQLite sqlite3_stmt

The sqlite3_stmt encapsulates a SQL statement. For instance, ―select * from photos‖ is a SQL statement. In your program, you encapsulate this SQL string using a statement. For instance, the following code snippet illustrates creating a SQL string, initializing a statement, and loading the statement (Listing 16-3). Listing 16-3 Using a sqlite3_stmt in a C program const char *sqlselect = "SELECT id,name,photo FROM photos"; static sqlite3_stmt *statement = nil; sqlite3_prepare_v2(database, sqlselect, -1, &statement, NULL);

The SQLite sqlite3_prepare_v2 Method

You load a SQL string into a statement using sqlite3_prepare methods. The prepare methods are sqlite3_prepare, sqlite3_prepare_v2, sqlite3_prepare_16, and sqlite3_prepare16_v2. This chapter uses only the sqlite3_prepare_v2 method. Notice the prepare statement takes a C string, not an NString, but getting the C string from an NString is not difficult—simply call the NSString’s UTF8String method. The sqlite3_prepare_v2 method’s signature is in Listing 16-4. Notice, like the open statements, the prepare statement returns an integer result code you should check when calling the method.   Listing 16-4 The sqlite3_prepare_v2 method signature (taken from the SQLite online documentation) int sqlite3_prepare_v2( sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const char **pzTail      /* OUT: Pointer to unused portion of zSql */ ); /* Database handle */ /* SQL statement, UTF-8 encoded */ /* Maximum length of zSql in bytes. */

The SQLite sqlite3_step Method

The sqlite3_step method executes a prepared statement. You must call this method at least once. For instance, when calling insert or update, you call sqlite3_step once. You only call it once because these statements do not result in a record set being returned from the database. When selecting data, you typically call this method multiple times until you receive no more results. The following is the method’s signature. int sqlite3_step(sqlite3_stmt*); Like the other SQLite methods, this method returns a response code you should check after calling the method.


You select one or more records from a SQL database using a select statement. Because a select statement usually returns multiple rows, you must loop through the row set if you wish to obtain all records. while (sqlite3_step(statement) == SQLITE_ROW){ //process row here }

Obtaining SQLite Column Values

You obtain column values through a method in Listing 16-5. Using these methods will become more apparent after the next task. Listing 16-5 Methods for obtaining column data (from SQLite online documentation) const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); int sqlite3_column_bytes(sqlite3_stmt*, int iCol); int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); int sqlite3_column_int(sqlite3_stmt*, int iCol); sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); int sqlite3_column_type(sqlite3_stmt*, int iCol); sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol); Opening and Querying a Database

  1. Open MyDBProject in Open MyDBProjectAppDelegate.h.
  2. In Classes, create a new group called
  3. Add a new Objective-C class called PhotosDAO to Create another Objective-C class called PhotoDAO.
  4. Add a name, photoID, and photo property to h and PhotoDAO.m (Listings 16-6 and 16-7).
  5. Open h and import SQLite3 and PhotoDAO. Add a reference to the database you will use (Listing 16-8).
  6. Add a getAllPhotos method to PhotosDAO and implement the method (Listing 16-9).
  7. Open h and import PhotosDAO and PhotoDAO (Listing 16-10).
  8. Add an NSMutableArray property to hold the Add an IBOutlet for a UIImageView. Add a UILabel named theLabel, add an IBAction, and name the method changeImage.
  9. Open m and synthesize photos and theImageView (Listing 16-11).
  10. Implement the viewDidLoad and changeImage methods so they match Listing 16-11.
  11. Save or build the

  Listing 16-6 PhotoDAO.h #import <Foundation/Foundation.h> @interface PhotoDAO : NSObject { NSString * name; NSInteger photoID; UIImage * photo; } @property (nonatomic, retain) NSString * name; @property (nonatomic, assign) NSInteger photoID; @property (nonatomic, retain) UIImage * photo; @end Listing 16-7 PhotoDAO.m #import "PhotoDAO.h" @implementation PhotoDAO @synthesize name; @synthesize photoID; @synthesize photo; - (void) dealloc { [name release]; [photo release]; [super dealloc]; } @end Listing 16-8 PhotosDAO.h #import <Foundation/Foundation.h> #import <sqlite3.h> #import "PhotoDAO.h" @interface PhotosDAO : NSObject { sqlite3 *database; } - (NSMutableArray *) getAllPhotos; @end Listing 16-9 PhotosDAO.m #import "PhotosDAO.h" @implementation PhotosDAO - (NSMutableArray *) getAllPhotos { NSMutableArray * photosArray = [[NSMutableArray alloc] init]; @try { NSFileManager *fileManager = [NSFileManager defaultManager]; NSString *theDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"myDatabase.sqlite"]; BOOL success = [fileManager fileExistsAtPath:theDBPath]; if (!success) { NSLog(@"Failed to find database file '%@'.", theDBPath);} if (!(sqlite3_open([theDBPath UTF8String], &database) == SQLITE_   OK)) { NSLog(@"An error opening database, normally handle error here."); } const char *sql = "SELECT id,name,photo FROM photos"; sqlite3_stmt *statement; if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) != SQLITE_OK){ NSLog(@"Error, failed to prepare statement, normally handle error here."); } while (sqlite3_step(statement) == SQLITE_ROW) { PhotoDAO * aPhoto = [[PhotoDAO alloc] init]; aPhoto.photoID = sqlite3_column_int(statement, 0); = [NSString stringWithUTF8String:(char *) sqlite3_column_text(statement, 1)]; const char * rawData = sqlite3_column_blob(statement, 2); int rawDataLength = sqlite3_column_bytes(statement, 2); NSData *data = [NSData dataWithBytes:rawData length: rawDataLength]; = [[UIImage alloc] initWithData:data]; [photosArray addObject:aPhoto]; [aPhoto release]; } if(sqlite3_finalize(statement) != SQLITE_OK){ NSLog(@"Failed to finalize data statement, normally error handling here."); } if (sqlite3_close(database) != SQLITE_OK) { NSLog(@"Failed to close database, normally error handling here."); } } @catch (NSException *e) { NSLog(@"An exception occurred: %@", [e reason]); return nil; } return photosArray; } @end Listing 16-10 MyDBProjectViewController.h #import <UIKit/UIKit.h> #import "PhotoDAO.h"; #import "PhotosDAO.h"; @interface MyDBProjectViewController : UIViewController { NSMutableArray * photos; IBOutlet UIImageView * theImageView; IBOutlet UILabel * theLabel; } @property (nonatomic, retain) NSMutableArray * photos; @property (nonatomic, retain) IBOutlet UIImageView * theImageView; @property (nonatomic, retain) IBOutlet UILabel * theLabel; - (IBAction) changeImage: (id) sender; @end     Listing 16-11 MyDBProjectViewController.m #import "MyDBProjectViewController.h" @implementation MyDBProjectViewController @synthesize photos; @synthesize theImageView; @synthesize theLabel; - (void)viewDidLoad { PhotosDAO * myPhotos = [[PhotosDAO alloc] init]; = [myPhotos getAllPhotos]; [self.theImageView setImage:((PhotoDAO *)[ objectAtIndex:0]).photo]; [self.theLabel setText:((PhotoDAO *)[ objectAtIndex:0]). name]; [myPhotos release]; [super viewDidLoad]; } - (IBAction) changeImage: (id) sender { static NSInteger currentElement = 0; if(++currentElement == [ count]) currentElement = 0; PhotoDAO * aPhoto = (PhotoDAO *) [ objectAtIndex: currentElement]; [self.theLabel]; [self.theImageView]; } - (void)dealloc { [photos release]; [theImageView release]; [theLabel release]; [super dealloc]; } @end   Slide142  

Figure 16-4 Adding a UIImageView and a UIToolBar to the view’s canvas


  1. Open xib in Interface Builder. Add a toolbar, a label, and a UIImageView (Figure 16-4). Change the button’s title to Next. Remove the text from the label.
  2. Connect the File’s Owner theLabel outlet to the label added to the Connect the theImageView outlet to the UIImageView. Connect the changeImage action to the Next button. Save and exit Interface Builder.
  3. Build and run the application in iPhone As I am a registered developer, I deployed the application to my iPhone and ran the application (Figures 16-5 and 16-6). Aren’t they cute? Excuse my obligatory computer book author’s inclusion of his or her kids into the book.

NOTE You should never load an entire database at once when creating a real application, especially when using a large blob, like this example. Only load what you need. But for simplicity, you loaded all records in this task.     Slide142Slide143

Figure 16-5 Running the application  on my iphone                                      Figure 16-6 Running the application on my iPhone

  (first image)                                                                                                                (secondimage)

The Model-View-Controller

When writing a program for any platform, you should adhere to the MVC design pattern as closely as possible. Rather than placing the database logic in a view or view controller, you created separate classes, insulating the view and controller layers from the database layer. The MyDBProjectViewController knows nothing about the underlying SQLite3 library; the view controller only knows about PhotosDAO and PhotoDAO. Notice you further separated the code by placing it in its own group, Model, under Classes. All this separation makes debugging and maintaining the program easier. It also makes reading and understanding this chapter’s example code easier.

Opening the Database

To keep the task’s length manageable and focused, rather than creating several data access methods in PhotosDAO, you only created one. - (NSMutableArray *) getAllPhotos; This method returns an array of PhotoDAO objects. The getAllPhotos method first finds the database and opens it. Because the database is in the resources folder, you can access it directly using the bundle’s resourcePath.   NSFileManager *fileManager = [NSFileManager defaultManager]; NSString *theDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent: @"chapter18.sqlite"]; After obtaining the database’s path, you open it. if (!(sqlite3_open([theDBPath UTF8String], &database) == SQLITE_OK)) Notice that you obtain the UTF8String from the NSString before passing the sqlite3_open method the path.

Querying the Data

After opening the database, you query it for the photo records. If you have ever worked with a database using code, for instance, Java Database Connectivity (JDBC), then this code should look familiar. The getAllPhotos method first creates the SQL select string. Next, the method places the string in a statement and then queries the database. After obtaining the data, getAllPhotos loops through each record.For each new record, getAllPhotos creates a new PhotoDAO. The newly created PhotoDAO object’s values are then set to the appropriate values from the current record. After initializing the PhotoDAO object, getAllPhotos places the object into PhotosDAO’s photosArray.

Loading a Blob into NSData

This code snippet is useful. It shows you a quick, easy way to load a blob, any blob, into an NSData object. First, load the blob into a C string. const char * rawData = sqlite3_column_blob(statement, 2); Second, obtain the blob’s byte size. int rawDataLength = sqlite3_column_bytes(statement, 2); Third, create an NSData class using the C string and size variables. NSData *data = [NSData dataWithBytes:rawData length:rawDataLength]; As you already know the database blob is an image, you initialize the PhotoDAO’s photo property using the UIImage’s initWithData method. = [[UIImage alloc] initWithData:data]; This same technique works for other binary data as well (replacing UIImage with the appropriate class).

Closing the Database

When finished using a statement, you release its resources by finalizing the statement. if(sqlite3_finalize(statement) != SQLITE_OK) After you no longer need the database, you close it. if (sqlite3_close(database) != SQLITE_OK) Selecting all records only has limited value. Rarely will you use SQL statements where you do not wish to limit the results returned. For this, you typically add parameters to your SQL statements and then replace the parameters with values in your program. This is called binding your program’s values to the statements’ parameters. Programs must also usually allow more than simply selecting data; most applications allow users to add, edit, and delete records. In the next section, you learn about binding, inserting, updating, and deleting records.

About Author


Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .

Related Blogs

Write For Us