Framework 7

If you require a lot of data in your app there are many ways to store data in iOS. CoreData, NSDictionary and direct calls to an SQLite database. I usually choose to use SQLite databases because I’ve always used MySQL databases on previous web programming projects. The typical way to access an SQLite DB in iOS is using the code below.

sqlite3 *_database = 0;
NSString* dbPath = nil;
NSString *contentPath;
dbPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"Database.sqlite"];

if (sqlite3_open([dbPath UTF8String], &_database) != SQLITE_OK) {
    NSLog(@"Failed to open database!");
}
else
{
    NSString *query = [NSString stringWithFormat:@"SELECT * FROM tableName"];
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(_database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {
         while (sqlite3_step(statement) == SQLITE_ROW) {
            ClassName *object	= [[ClassName alloc] init];
            object.property = sqlite3_column_int(statement, 0);
            object.property = [NSString stringWithFormat:@"%s",sqlite3_column_text(statement, 1)];
            object.property = [NSString stringWithFormat:@"%s",sqlite3_column_text(statement, 2)];
            object.property = [NSString stringWithFormat:@"%s",sqlite3_column_text(statement, 3)];
            object.property = [NSString stringWithFormat:@"%s",sqlite3_column_text(statement, 4)];
            object.property = [NSString stringWithFormat:@"%s",sqlite3_column_text(statement, 5)];
            object.property = sqlite3_column_int(statement, 6);
		}
        sqlite3_finalize(statement);
    }
}

The code above works pretty well when you’re retreiving data from the database, but sometimes doesn’t work if you’re deleting, updating or inserting data. The problem is that the database can be locked when doing multiple statements at the same time, i.e. performing a select to get data then updating a record. I looked around for a framework that would make reading and writing to an SQLite database with a lot less code and handled the interface much easier, what I found was FMDB.

FMDB is an Objective-C wrapper for SQlite. Download the framework from the FMDB Github.

First, you’ll need to integrate the framework into your project.
1. Download the source code from the link above.
2. Add the following files into your project:
– FMDatabase.h
– FMDatabase.m
– FMDatabaseAdditions.h
– FMDatabaseAdditions.m
– FMResultSet.h
– FMResultSet.m
3. Add the “libsqlite3.dylib” framework to your project. To do this, access your target settings, click on “Summary”, scroll down to the “Linked Frameworks and Libraries” group and click the + button to add a new framework to your project. Search for “libsqlite3.dylib”.
4. Include the following headers at the top of your class:
– #import “sqlite3.h”
– #import “FMDatabase.h”
4. Start coding!

Below is some sample code to help you with the basic SQLite commands.

Create Table

NSArray *docPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [docPaths objectAtIndex:0];
NSString *dbPath = [documentsDir   stringByAppendingPathComponent:@"UserDatabase.sqlite"];

FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
[database open];
[database executeUpdate:@"CREATE TABLE tablename (id INTEGER  PRIMARY KEY DEFAULT NULL,field1Name INTEGER DEFAULT NULL,field2Name INTEGER DEFAULT NULL,field3Name INTEGER DEFAULT NULL,field4Name TEXT DEFAULT NULL)"];
[database close];

The database is being created and stored in the documents directory. You can only create, update, delete and insert data into a database in your applications documents directory in the iOS. It’s the only location that allows you to edit the database. If you wanted to only access data and not modify it, you could store your database in the resources of your project. If you are starting with an existing database that you created, you need to copy the database from your resources directory to the application documents directory. I usually create my SQlite database using MesaSQLite. It’s a free GUI for the MAC.

Retrieve Data From Table

NSArray *docPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [docPaths objectAtIndex:0];
NSString *dbPath = [documentsDir   stringByAppendingPathComponent:@"UserDatabase.sqlite"];

FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
[database open];
FMResultSet *results = [database executeQuery:@"SELECT * FROM tableName"];
// Or with variables FMResultSet *results = [database executeQuery:@"SELECT * from tableName where fieldName= ?",[NSString stringWithFormat:@"%@", variableName]];
while([results next]) {
    ClassName *objectName	= [[ClassName alloc] init];
    objectName.propertyName = [results intForColumn:@"id"];
    objectName.propertyName = [results stringForColumn:@"field1Name"];
    objectName.propertyName = [results stringForColumn:@"field2Name"];
    objectName.propertyName = [results stringForColumn:@"field3Name"];
    objectName.propertyName = [results stringForColumn:@"field4Name"];
    objectName.propertyName = [results stringForColumn:@"field5Name"];
}
[database close];

It’s nice that FMDB makes you actually put in the table field name, this makes it much easier to ensure you are pulling the correct field. In the old way you’re pulling the fields in order. Be sure that you are using the correct type, FMDB can retrieve data in the following types:
-intForColumn:
-longForColumn:
-longLongIntForColumn:
-boolForColumn:
-doubleForColumn:
-stringForColumn:
-dateForColumn:
-dataForColumn:
-dataNoCopyForColumn:
-UTF8StringForColumnIndex:
-objectForColumn:

Insert Data into Table

NSArray *docPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [docPaths objectAtIndex:0];
NSString *dbPath = [documentsDir   stringByAppendingPathComponent:@"UserDatabase.sqlite"];

FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
[database open];
[database executeUpdate:@"INSERT INTO tableName (fieldName, fieldName, fieldName, fieldName) VALUES (?, ?, ?, ?)", [NSNumber numberWithLong:fieldVariable], [NSNumber numberWithLong:fieldVariable], [NSString stringWithFormat:@"%@", fieldVariable], [NSNumber numberWithInt:fieldVariable], nil];
[database close];

All arguments provided to the -executeUpdate: method (or any of the variants that accept a va_list as a parameter) must be objects.The code above is insertng three NSNumbers and one NSString. If you didn’t convert your variables into an object the method will result in a crash or locking up your app.

Update Record in Table

NSArray *docPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [docPaths objectAtIndex:0];
NSString *dbPath = [documentsDir   stringByAppendingPathComponent:@"UserDatabase.sqlite"];

FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
[database open];
[database executeUpdate:@"UPDATE tableName set fieldName= ? where fieldName= ?", [NSNumber numberWithLong:fieldVariable], [NSString stringWithFormat:@"%@", fieldVariable], nil];
[database close];

Delete Record in Table

NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDir = [documentPaths objectAtIndex:0];
NSString *dbPath = [documentsDir   stringByAppendingPathComponent:@"UserDatabase.sqlite"];

FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
[database open];
[database executeUpdate:@"DELETE FROM tableName WHERE fieldName= ? and fieldName= ?", [NSString stringWithFormat:@"%@", fieldVariable], [NSNumber numberWithLong:fieldVariable], nil];
[database close];

The code above should give you a good start on using SQLite databases using the FMDB framework in your app. If you want more information about FMDB including some advanced features including FMDatabaseQueue using Threads read the documentation on the FMDB Github.