Zachary Powell

Software Programmer

Zachary Powell

Download my CV Send me a Message

Using SQlite on iOS

Zachary Powell - 21/12/2015

We have in the past looked at using SQlite on Android, today we shall look at doing the same under iOS.

Before we start, I will assume that you either have a pre-existing app you want to use SQLite with, or you have just created a new project within Xcode. Either way will work well.

SQLite on iOS

Before we start with the actual implementation, we need to import the SQLite 3 library into our project. This will allow us to then use it within our database handler class.

First in the project navigator, click on your projects name. Make sure the general tab is selected under “Linked Frameworks and Libraries” section, then click on the small plus icon.

Screen Shot 2015-12-17 at 18.09.28

This will bring up a window that allows you to select frameworks and libraries to be added. Here, we need to search for sqlite and then select the item called “libsqlite3.tdb”. Finally click on ‘add’ to actually add the library to your project.

Screen Shot 2015-12-17 at 18.14.30

Objective C Object Class

Just as with the Android example, lets first make a simple Object class that we can use to save and get the data from the database.

BuildingLocation.h

#import <Foundation/Foundation.h>

@interface BuildingLocation : NSObject {
    NSString *_name;
    NSString *_description;
    float  _Long;
    float  _Lat;
}

@property (nonatomic, copy) NSString *name;
@property (nonatomic, copy) NSString *description;
@property (nonatomic) float Long;
@property (nonatomic) float Lat;

- (id)init:(NSString *)name description:(NSString *)description Long:(float)Long Lat:(float)Lat;

@end

BuildingLocation.m

#import "BuildingLocation.h"

@implementation BuildingLocation

@synthesize name = _name;
@synthesize description = _description;
@synthesize Long = _Long;
@synthesize Lat =_Lat;

-(id)init:(NSString *)name description:(NSString *)description Long:(float)Long Lat:(float)Lat {
    if ((self = [super init])) {
        self.name = name;
        self.description = description;
        self.Long = Long;
        self.Lat = Lat;
    }
    return self;
}

@end

The above header and main file are much the same as our Android version. We set up a couple of variables to store a location, and then set up an init function. Unlike the Java Android version, we did not set up getter and setter methods as this time around we will access the variables directly.

Database Handler

Now we shall set up the Header and main file for the actual database handler.

DBHandler.h

#import <Foundation/Foundation.h>
#import "BuildingLocation.h"
#import <sqlite3.h>
#import <CoreLocation/CoreLocation.h>


@interface DBHelper : NSObject
{
    NSString *databasePath;
}

+(DBHelper*)getSharedInstance;

-(BOOL)createDB;

-(BOOL) clearLocations;

-(BOOL) insertLocation:(BuildingLocation*)location;

-(NSArray*) getAllLocations;

@end

Here we just define the methods that we will be creating within the Main file. Also make sure to include the header file for your object class and the SQlite library.

DBHandler.m

#import "DBHelper.h"
static DBHelper *sharedInstance = nil;
static sqlite3 *database = nil;
static sqlite3_stmt *statement = nil;

@implementation DBHelper

+(DBHelper*)getSharedInstance{
    if (!sharedInstance) {
        sharedInstance = [[super allocWithZone:NULL]init];
        [sharedInstance createDB];
    }
    return sharedInstance;
}


-(BOOL)createDB{
    NSString *docsDir;
    NSArray *dirPaths;
    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains
    (NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = dirPaths[0];
    // Build the path to the database file
    databasePath = [[NSString alloc] initWithString:
                    [docsDir stringByAppendingPathComponent: @"town.db"]];
    BOOL isSuccess = YES;
    NSFileManager *filemgr = [NSFileManager defaultManager];
    if ([filemgr fileExistsAtPath: databasePath ] == NO)
    {
        const char *dbpath = [databasePath UTF8String];
        if (sqlite3_open(dbpath, &database) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt = "CREATE TABLE if not exists LocationInfo (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Name TEXT, Description TEXT, Longitude TEXT, Lat TEXT)";
            
            if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                isSuccess = NO;
                NSLog(@"Failed to create table");
            }
            sqlite3_close(database);
            return  isSuccess;
        }
        else {
            isSuccess = NO;
            NSLog(@"Failed to open/create database");
        }
    }
    return isSuccess;
}

-(BOOL) clearLocations{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        NSString *insertSQL = @"DELETE FROM LocationInfo";
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            return YES;
        }
        else {
            return NO;
        }
    }
    return NO;
}

-(BOOL) insertLocation:(BuildingLocation*)location{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
     
        NSNumber *longNumber = [NSNumber numberWithFloat:location.Long];
        NSNumber *latNumber = [NSNumber numberWithFloat:location.Lat];

        NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO LocationInfo (Name, Description, Longitude, Lat) values (\"%@\",\"%@\", \"%@\", \"%@\")",location.name, location.description, [longNumber stringValue], [latNumber stringValue]];
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            return YES;
        }
        else {
            NSLog(@"Error while updating. '%s'", sqlite3_errmsg(database));
            return NO;
        }
    }
    return NO;
}

-(NSArray*) getAllLocations{
    const char *dbpath = [databasePath UTF8String];
    NSString *querySQL;
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        NSString *querySQL = @"";
        
        querySQL = @"SELECT * FROM LocationInfo";
       
        const char *query_stmt = [querySQL UTF8String];
        NSMutableArray *resultArray = [[NSMutableArray alloc]init];
        if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statement) == SQLITE_ROW)
            {
                BuildingLocation *location = [[BuildingLocation alloc] init];
                
                location.name = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
                location.description = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)];
                NSNumberFormatter * nFormatter = [[NSNumberFormatter alloc] init];
                [nFormatter setNumberStyle:NSNumberFormatterDecimalStyle];
                NSNumber *numlong = [nFormatter numberFromString:[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)]];
                NSNumber *numlat = [nFormatter numberFromString:[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 5)]];

                location.Long = [numlong floatValue];
                location.Lat = [numlat floatValue];

                [resultArray addObject:location];
            }
            return resultArray;
        }
    }
    return nil;

}

@end

This is the complete file with the same methods we implemented with Android.

+(DBHelper*)getSharedInstance{
    if (!sharedInstance) {
        sharedInstance = [[super allocWithZone:NULL]init];
        [sharedInstance createDB];
    }
    return sharedInstance;
}

The first method we create will get the instance of a created database. This allows us to use the same database for the life of the app. If the database is already initialised we will use it, if not we will run the createDB method to create a new instance and then pass that.

Next, we create the method that will actually create the database

-(BOOL)createDB{
    NSString *docsDir;
    NSArray *dirPaths;
    // Get the documents directory
    dirPaths = NSSearchPathForDirectoriesInDomains
    (NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = dirPaths[0];
    // Build the path to the database file
    databasePath = [[NSString alloc] initWithString:
                    [docsDir stringByAppendingPathComponent: @"town.db"]];
    BOOL isSuccess = YES;
    NSFileManager *filemgr = [NSFileManager defaultManager];
    if ([filemgr fileExistsAtPath: databasePath ] == NO)
    {
        const char *dbpath = [databasePath UTF8String];
        if (sqlite3_open(dbpath, &database) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt = "CREATE TABLE if not exists LocationInfo (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, Name TEXT, Description TEXT, Longitude TEXT, Lat TEXT)";
            
            if (sqlite3_exec(database, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                isSuccess = NO;
                NSLog(@"Failed to create table");
            }
            sqlite3_close(database);
            return  isSuccess;
        }
        else {
            isSuccess = NO;
            NSLog(@"Failed to open/create database");
        }
    }
    return isSuccess;
}

This method is pretty straight forward. First we create a database path in the apps document directory, then using the FileManager we can check if the database file exists. If it already does, there is nothing more we need to do. However if the file has not yet been created, we will go ahead and create the new file.

Using the method sqlite3_open to open a database at the file location is the quickest way to do this. The result is a database object that uses a newly created .db file at the specified location. Using this database object we can then execute our SQL statement to populate the database with our location table.

-(BOOL) clearLocations{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        NSString *insertSQL = @"DELETE FROM LocationInfo";
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            return YES;
        }
        else {
            return NO;
        }
    }
    return NO;
}

The next method we implement is the simple clearLocations. First we open the database using the set path. If the database is opened successfully (or already open), we can then execute the delete SQL. Using the sqlite3_prepare_v2 method, we step over the statement to check that the SQLite has been executed.

-(BOOL) insertLocation:(BuildingLocation*)location{
    const char *dbpath = [databasePath UTF8String];
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
     
        NSNumber *longNumber = [NSNumber numberWithFloat:location.Long];
        NSNumber *latNumber = [NSNumber numberWithFloat:location.Lat];

        NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO LocationInfo (Name, Description, Longitude, Lat) values (\"%@\",\"%@\", \"%@\", \"%@\")",location.name, location.description, [longNumber stringValue], [latNumber stringValue]];
        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(database, insert_stmt,-1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE)
        {
            return YES;
        }
        else {
            NSLog(@"Error while updating. '%s'", sqlite3_errmsg(database));
            return NO;
        }
    }
    return NO;
}

Our next method looks at inserting a location into the database. We pass a BuildingLocation object to the method, then open the database. In the BuildingLocation, we are storing the long and lat as a float. To convert these to strings for the database, we must first create NSNumber objects from the float values.

NSNumber *longNumber = [NSNumber numberWithFloat:location.Long];
NSNumber *latNumber = [NSNumber numberWithFloat:location.Lat];

Then we create the SQL string that will be what we execute in the database.

NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO LocationInfo (Name, Description, Longitude, Lat) values (\"%@\",\"%@\", \"%@\", \"%@\")",location.name, location.description, [longNumber stringValue], [latNumber stringValue]];

Finally, we run this SQL string much the same we as the clearLocation method, checking that it was completed and returning true if that is the case.

-(NSArray*) getAllLocations{
    const char *dbpath = [databasePath UTF8String];
    NSString *querySQL;
    if (sqlite3_open(dbpath, &database) == SQLITE_OK)
    {
        NSString *querySQL = @"";
        
        querySQL = @"SELECT * FROM LocationInfo";
       
        const char *query_stmt = [querySQL UTF8String];
        NSMutableArray *resultArray = [[NSMutableArray alloc]init];
        if (sqlite3_prepare_v2(database, query_stmt, -1, &statement, NULL) == SQLITE_OK)
        {
            while (sqlite3_step(statement) == SQLITE_ROW)
            {
                BuildingLocation *location = [[BuildingLocation alloc] init];
                
                location.name = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 1)];
                location.description = [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 2)];
                NSNumberFormatter * nFormatter = [[NSNumberFormatter alloc] init];
                [nFormatter setNumberStyle:NSNumberFormatterDecimalStyle];
                NSNumber *numlong = [nFormatter numberFromString:[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 4)]];
                NSNumber *numlat = [nFormatter numberFromString:[[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(statement, 5)]];

                location.Long = [numlong floatValue];
                location.Lat = [numlat floatValue];

                [resultArray addObject:location];
            }
            return resultArray;
        }
    }
    return nil;

}

The last method will get all the locations from the database and return an array of locations. As before, we first open the database, and then create the SQL and run it. The difference is, because we are now using a SELECT statement, sqlite3_prepare_v2 will return the rows of the database. sqlite3_step allows us to iterate over these rows and access the data. We then create a new BuildingLocation object and set its variables to the matching values in that database row.

Once they have all be set, we add the created object to the array. Once we have done iterating over the rows, we will then return the array.

Example of Use

DBHelper *db;
db = [DBHelper getSharedInstance];

BuildingLocation *locationObject = [[BuildingLocation alloc] init];
locationObject.name = "test";
locationObject.description = "this is a test place";
locationObject.Long = 1.458375;
locationObject.Lat =  52.105336;
           
[db insertLocation:locationObject];

NSMutableArray *locations;
locations = [db getAllLocations];

Here is an example of how we would use the code, first creating a new instance of the database, then we create a BuildingLocation object and insert it into the database. Finally, we get back the array of saved locations from the database.

If you have any comments or questions please ask below.

Leave a Comment

Tags: ,

Leave a Reply

Your email address will not be published. Required fields are marked *

Tags: ,

“ Any fool can write code that a computer can understand. Good programmers write code that humans can understand. ”

-Martin Fowler