SQLite Not Importing Properly into iOS

Question

It appears that when I copy a .sqlite3 file into iOS, and use FMDB to access it, the database is empty. I just drag and drop it into Xcode. When I run:

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"db.sqlite3"];
FMDatabase *db = [FMDatabase databaseWithPath:writableDBPath];

FMResultSet *results = [db executeQuery:@"SELECT * FROM tableName"];

results returns nil. Looking at [db lastErrorMessage], it says my table does not exist. When I open up the .sqlite file in the command line (the one that is stored in the iPhone Simulator), I get an empty database. Is there a specific way to import a SQLite database into iOS? If so, how?

Seamus Campbell · Accepted Answer · 2013-04-07T17:10:03.820

I see two likely problems:

First, make sure the .sqlite file is included in your project target. Select your file in the File Navigator; open the Utilities panel (the right hand panel icon in the section that says "View"), and look for a section titled "Target Membership". If the box for your desired target (you probably only have one) is not checked, your database file is not being copied into the bundle.

Second, you are attempting to find the database in your documents directory. However, you must manually copy it from your bundle to that directory before you can open it as a writable file:

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *dbPath = [documentsDirectory stringByAppendingPathComponent:@"db.sqlite3"];

NSURL *dbURL = [NSURL fileURLWithPath:dbPath];

// copy a database from the bundle if not present
// on disk
NSFileManager *fm = [NSFileManager defaultManager];
if (![fm fileExistsAtPath:dbPath]) {
    NSURL *bundlePath = [[NSBundle mainBundle] URLForResource:@"db" withExtension:@"sqlite3"];
    NSError *error = nil;
    if (!bundlePath || ![fm copyItemAtURL:bundlePath toURL:dbURL error:&error]) {
        NSLog(@"error copying database from bundle: %@", error);
    }
}

Thanks. `[self applicationDocumentsDirectory]` cannot be found. I replaced it with the first three lines above in my question. I then used the NSString `dbPath` as the path and I'm still getting the same issue. Any ideas why this is? Did I do it right be replacing your method my three lines above? — darksky, Apr 07 '13 at 17:08
This code will only copy the DB from the bundle if there is not a DB in the documents directory already, so make sure you're running a fresh installation — delete the old copy of the app from your device or the simulator. I think part of what was going on here is that `-databaseWithPath:` was creating a new DB if it didn't find one, resulting in an empty stored DB. — Seamus Campbell, Apr 07 '13 at 17:12

score 0 · Answer 2 · answered Feb 13 '14 at 06:34

I think that you can also your original code. You just need to open the "db"

NSArray * paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString * documentDirectory = [paths objectAtIndex:0];
//NSString * documentDirectory = @"/Users/cefitdept/Documents/ImportDB/";

NSString * dbPath = [documentDirectory stringByAppendingPathComponent:@"ImportDB.sqlite"];
FMDatabase * db = [FMDatabase databaseWithPath:dbPath];
if (![db open]) {
    return;
}
FMResultSet * results = [db executeQuery:@"SELECT * FROM main"];
while ([results next]) {
    NSData * data = [results dataForColumn:@"engs"];
    NSString * string = [[NSString alloc] initWithData:data encoding:NSUTF8StringEncoding];
}

SQLite Not Importing Properly into iOS

2 Answers2