FMDB Database not working on device, but it is working in Simulator

Question

I have this database that i created, i want to load it using FMDB, but on the simulator i can query select the data, but not on the ipad.

i use this code to check it.

- (void)createDatabase{
    //set reference to database here

    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    // Database filename can have extension db/sqlite.
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *appDBPath = [documentsDirectory stringByAppendingPathComponent:@"product.db"];

    success = [fileManager fileExistsAtPath:appDBPath];
    if (success) {
        [self getAllData];
        return;
    }
    // The writable database does not exist, so copy the default to the appropriate location.
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"product.db"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:appDBPath error:&error];
    NSAssert(success, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    [self getAllData];
}

//method used to create database and check if it exists

- (void)getAllData {
    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"product.db"];

    database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    NSString *sqlSelectQuery = @"SELECT * FROM Categories";

    // Query result
    FMResultSet *resultsWithNameLocation = [database executeQuery:sqlSelectQuery];
    while([resultsWithNameLocation next]) {
        NSString *strID = [NSString stringWithFormat:@"%d",[resultsWithNameLocation intForColumn:@"id"]];
        NSString *strName = [NSString stringWithFormat:@"%@",[resultsWithNameLocation stringForColumn:@"cat"]];
        NSString *strLoc = [NSString stringWithFormat:@"%@",[resultsWithNameLocation stringForColumn:@"Location"]];

        // loading your data into the array, dictionaries.
        NSLog(@"ID = %@, Name = %@, Location = %@",strID, strName, strLoc);
    }
    [database close];
}

i put the product.db in the supporting files, and i copied it in the Copy Bundle Resources for the targets. Does anyone what i am doing wrong? i am kind of at a dead end here.

Thanks in advance

The problem occurs at the selecting stage, when i select it it returns:

"DB Error: 1 "no such table"

Answer 1

If you ever ran the app on the device before you refined the file open/copy logic, you may have accidentally created a blank database in the documents folder (because if you open a database and it's not found, it creates a blank database). Try deleting the app (which will remove any blank database that might be lingering about) and reinstalling the app and see if that fixes it.

By the way, in the future, you can avoid this problem by using openWithFlags (rather than open) with SQLITE_OPEN_READWRITE (but not SQLITE_OPEN_CREATE), and that will ensure that the app never creates a blank database if it doesn't find one.

You can see the SQLite documentation on sqlite3_open (which FMDB open uses) and sqlite_open_v2 (which FMDB openWithFlags uses) for a discussion of these flags.