SQLite Datbase connectivity in iPhone Applications

Database integration is often required in case we want to store some sort of persistent information in the application. The other prominent usage is caching the data for example caching the data from web requests in HTTP request driven applications. SQLITE has been the database of choice on mobile platforms because of his light footprint. In this Blog we explore the basics for integrating SQLLITE with iPhone Native application. Fortunately, MacOS X is shipped with SQLite pre-installed, including an interactive environment for issuing.SQL commands from within a Terminal window. Using SQLite3 database in iphone app, for that we will need to add libsqlite framework in application. Right click on frameworks->Add Existing.

In the search bar type “libsql” and it will show you some 4 frameworks with same name.You have to choose libsqllite, the current version as of this document is 3 so i assume libsqlite3.dylib for the purpose of this blog.

Add SQLite to XCode Project

click on add button to add the framework to project. To create a database you can use terminal or you can use firefox extension for sqlite3 i.e sqlite manager 0.7.7. Creating database using terminal: open terminal and set the path where you want to save database. 1. sqlite3 data.sqlite3 instead of “data” you can use any other name. 2. Create the required tables in SQLite
After creating tables, typing in .tables will give you tables in data.sqlite3 database.

Command to see tables in SQLite database

3. Lets do a few sample inserts in the SQLite DB:
INSERT INTO users (user , description) VALUES( 'User1', 'Description 1');
INSERT INTO users (user, description ) VALUES( 'User 2', 'Description 2');
4. Lets retrieve the list of records saved in the SQLite DB.
select * from users;

List Records SQLite

Creating database using sqlite manager: SQLiteManager is a firefox extension that eases the operations with SQLite. Open firefox and download and install sqlite manager 0.7.7. Restart firefox after installing, go to tools and you will see the option for SQLiteManager. Clicking the SQLite option will open a tab which will let you create a new database:

Creating SQLite DB with SQLiteManager

Once database is created, you can create new tables in the database.

clicking on the add button will invoke a popup as shown in the screenshot below.

Create table in SQLite iOS

enter the values and click ok. After records been added it looks like above screenshot. Now we will import the database into our project. Right click on Resources->Add->Existing File and select data sqlite3. This will add data.sqlite3 file into Resources folder. The Coding begins Open up the “User.h” file from the “Classes” folder and edit its contents
#import <UIKit/UIKit.h>

@interface User : NSObject {
	NSString *name; // member variable
	NSString *description;// member variable
	NSString *imageURL; // member variable

@property (nonatomic, retain) NSString *name;
@property (nonatomic, retain) NSString *description;
@property (nonatomic, retain) NSString *imageURL;

-(id)initWithName:(NSString *)n description:(NSString *)d url:(NSString *)u;

Lets decode this code a bit. initWithName line, this line will allow you to create a new object with the required data, we could have used the default init function, but it will be easier for us to define our own. Now we will actually have to implement the User Object, open up the “User.m” file and edit its contents
#import "User.h"

@implementation User
@synthesize name, description, imageURL;

-(id)initWithName:(NSString *)n description:(NSString *)d url:(NSString *)u {
	self.name = n;
	self.description = d;
	self.imageURL = u;
	return self;
The above code basically stores the supplied data from the initWithName function and return the object (self). Open up the “SQLite3TutorialAppDelegate.h”

#import <UIKit/UIKit.h>
#import  // (if this shows error "no such directory" then you can use #import "/usr/include/sqlite3.h"

// Import the SQLite database framework
@interface SQLiteTutorialAppDelegate : NSObject {
UIWindow *window;
UINavigationController *navigationController;

// Database variables
NSString *databaseName;
NSString *databasePath;

// Array to store the user objects
NSMutableArray *users;

@property (nonatomic, retain) IBOutlet UIWindow *window;
@property (nonatomic, retain) IBOutlet UINavigationController *navigationController;
@property (nonatomic, retain) NSMutableArray *users;

// Now open up the “SQLiteTutorialAppDelegate.m”
- (void)applicationDidFinishLaunching:(UIApplication *)application {

// Setup some globals
databaseName = @"data.sqlite3";
// Get the path to the documents directory and append the databaseName
NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [documentPaths objectAtIndex:0];
databasePath = [documentsDir stringByAppendingPathComponent:databaseName];

// Execute the "checkAndCreateDatabase" function
[self checkAndCreateDatabase];

// Query the database for all user records and construct the "users" array
[self readUsersFromDatabase];
// Configure and show the window
[window addSubview:[navigationController view]];
[window makeKeyAndVisible];

-(void) checkAndCreateDatabase{

// Check if the SQL database has already been saved to the users phone, if not then copy it over
BOOL success;

// Create a FileManager object, we will use this to check the status
// of the database and to copy it over if required
NSFileManager *fileManager = [NSFileManager defaultManager];

// Check if the database has already been created in the users filesystem
success = [fileManager fileExistsAtPath:databasePath];
// If the database already exists then return without doing anything

if(success) return;
// If not then proceed to copy the database from the application to the users filesystem

// Get the path to the database in the application package
NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

// Copy the database from the package to the users filesystem
[fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:nil];
[fileManager release];

-(void) readUsersFromDatabase {
// Setup the database object
	sqlite3 *database;
	// Init the User Array
	users = [[NSMutableArray alloc] init];

	// Open the database from the users filessystem
	if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
		// Setup the SQL Statement and compile it for faster access
		const char *sqlStatement = "select * from users";
		sqlite3_stmt *compiledStatement;
		if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {
			// Loop through the results and add them to the feeds array
			while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
				// Read the data from the result row
				NSString *aName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
				NSString *aDescription = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
				NSString *aImageUrl = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];

				// Create a new user object with the data from the database
				User *user = [[user alloc] initWithName:aName description:aDescription url:aImageUrl];

				// Add the user object to the users Array
				[users addObject:user];

				[user release];
		// Release the compiled statement from memory
sqlite3_column_text is used if record is varchar You can use sqlite3_column_int for integer type. For more you can read http://www.sqlite.org/keyword_index.html sqlite3_close.The sqlite3_close() routine is the destructor for the sqlite3 object. Calls to sqlite3_close() return SQLITE_OK if the sqlite3object is successfully destroyed and all associated resources are deallocated. readUsersFromDatabase will fetch all records from users table. Thats it for this tutorial, Enjoy integrating your iPhone apps with SQLite.