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.

http://www.iosdevelopment.be/sqlite-tutorial/

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;
}
-(void)initDatabase;
-(void)insertUserObject:(UserObject*)userObject;
-(UserObject *)getUserObject;

@end

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
-(void)initDatabase
{
    // 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
            const char *sqlStatement = "CREATE TABLE IF NOT EXISTS USEROBJECT (ID INTEGER PRIMARY KEY AUTOINCREMENT, EMAILADDRESS TEXT, FIRSTNAME TEXT)";
            char *error;
            if (sqlite3_exec(databaseHandle, sqlStatement, NULL, NULL, &error) == SQLITE_OK)
            {
                NSLog(@"Created user object table successfully");
            }
            else
            {
                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];
                    
                }
            }

        }
        sqlite3_finalize(statement);
    }
    
    return myUserObject;
}

// Method to store a user object
-(void)insertUserObject:(UserObject*)userObject
{
    // 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);
    }
    else
    {
        NSLog(@"Error inserting User Object: %s", error);
    }
}

// Close the database connection when the DataController is disposed
- (void)dealloc {
    sqlite3_close(databaseHandle);
}

@end

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];
Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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: