Database integration with an app

If you’re looking for a lightweight, very very very lightweight, relational database for your iOS app, you need to read the post on the link below, you’ll be glad you did.

I hope it stays up for a while. Follow the instructions on downloading and incorporate the appropriate DLL as a reference in your project.

And here’s a brief example of how you might use it. In this example, you’re going to take a look at a very simple database that works with something generic, a user object. The UserObject is just an external class, very simple.

1. Here’s your data controller class

#import <Foundation/Foundation.h>
#import <sqlite3.h>
#import "UserObject.h"

@interface DataController : NSObject
    sqlite3 *databaseHandle;
-(UserObject *)getUserObject;


2. Here’s your implementation of the data controller

#import "DataController.h"
#import "UserObject.h"

@implementation DataController

// Method to open a database, the database will be created if it doesn't exist
    // Create a string containing the full path to the sqlite.db inside the documents folder
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *databasePath = [documentsDirectory stringByAppendingPathComponent:@"sqlite.db"];
    // Check to see if the database file already exists
    bool databaseAlreadyExists = [[NSFileManager defaultManager] fileExistsAtPath:databasePath];
    // Open the database and store the handle as a data member
    if (sqlite3_open([databasePath UTF8String], &databaseHandle) == SQLITE_OK)
        // Create the database if it doesn't yet exists in the file system
        if (!databaseAlreadyExists)
            //USER OBJECT TABLE
            char *error;
            if (sqlite3_exec(databaseHandle, sqlStatement, NULL, NULL, &error) == SQLITE_OK)
                NSLog(@"Created user object table successfully");
                NSLog(@"Error creating sqllite.db: %s", error);

//Get the user object
-(UserObject *)getUserObject
    UserObject *myUserObject = nil;

    // Create the query statement to get all persons
    NSString *queryStatement = [NSString stringWithFormat:@"SELECT EMAILADDRESS, FIRSTNAME FROM USEROBJECT"];
    // Prepare the query for execution
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(databaseHandle, [queryStatement UTF8String], -1, &statement, NULL) == SQLITE_OK)
        // Iterate over all returned rows
        while (sqlite3_step(statement) == SQLITE_ROW) {
            if (sqlite3_column_text(statement, 0))
                if (sqlite3_column_text(statement, 1))

                    NSString *emailAddress = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 0)];
                    NSString *firstName = [NSString stringWithUTF8String:(char*)sqlite3_column_text(statement, 1)];
                    //Create user object
                    myUserObject = [[UserObject alloc] initWithEmailAddress:emailAddress andFirstName:firstName];

    return myUserObject;

// Method to store a user object
    // Create insert statement for the person
    NSString *insertStatement = [NSString stringWithFormat:@"INSERT INTO USEROBJECT (EMAILADDRESS, FIRSTNAME) VALUES (\"%@\", \"%@\")", userObject.userObjectEmailAddress, userObject.userObjectFirstName];
    char *error;
    if ( sqlite3_exec(databaseHandle, [insertStatement UTF8String], NULL, NULL, &error) == SQLITE_OK)
        NSLog(@"User Object inserted");

        //in case you wanted the last inserted auto incremented id
        int personID = sqlite3_last_insert_rowid(databaseHandle);
        NSLog(@"Error inserting User Object: %s", error);

// Close the database connection when the DataController is disposed
- (void)dealloc {


3. And finally, here’s how you can use it!

    //initialize the datacontroller
    DataController *dataController = [[DataController alloc]init];    
    [dataController initDatabase];

    //get a UserObject
    UserObject *myUserObject=[dataController getUserObject];

    //insert a UserObject
    [dataController insertUserObject:userObject];
Leave a comment

1 Comment

  1. Working with JSON in iOS « aboutss

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

%d bloggers like this: