how do we open an already existing database fmdb and where to include it?

Question

I am using fmdb but i am not knowing where to add the db file. I found this old answer that i think it doesn't fit for xcode 4.2 (since we dont have 'Resources' folder anymore).

I created a database in 'Lita', added the extension .sqlite and added the db file as follows :

dbsqlite

then tried to use the following

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *docsPath = [paths objectAtIndex:0];
NSString *path = [docsPath stringByAppendingPathComponent:@"db.sqlite"];
FMDatabase *database = [FMDatabase databaseWithPath:path];
[database open];
FMResultSet *results = [database executeQuery:@"select * from tbl"];
printf("hi");
while([results next]) {
    printf("hi2");
NSString *name = [results stringForColumn:@"clm1"];
NSString *text  = [results stringForColumn:@"clm2"];        
NSLog(@"test: %@ - %@",name, text);
}
printf("done");

i am getting the 'hi' 'done' but never 'hi2'...

anyone can help?

score 6 · Accepted Answer · answered Apr 26 '12 at 12:48

You want to add the (compiled) database to the project as a resource, and then copy it into the documents folder of the app on first start-up.

Here is an example method that does the work, call it from the applicationDidFinishLaunching and use the resultant FMDatabase * for all you queries.

- (FMDatabase *)openDatabase
{
    NSFileManager *fm = [NSFileManager defaultManager];
    NSString *documents_dir = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];
    NSString *db_path = [documents_dir stringByAppendingPathComponent:[NSString stringWithFormat:@"database.s3db"]];
    NSString *template_path = [[NSBundle mainBundle] pathForResource:@"template_db" ofType:@"s3db"];

    if (![fm fileExistsAtPath:db_path])
        [fm copyItemAtPath:template_path toPath:db_path error:nil];
    FMDatabase *db = [FMDatabase databaseWithPath:db_path];
    if (![db open])
        NSLog(@"Failed to open database!");
    return db;
}

thanks. can u guide me where to add the resource? in the same directory with AppDelegate.h or any other directory? or where? — iosMentalist, Apr 26 '12 at 13:06
i should not have added the extension '.sqlite' to the file. what is the difference? should i just leave exentionless?! it is illogical!! anyway i used Hampus suggestion ... thanks — iosMentalist, Apr 27 '12 at 05:02
.sqlite is the file containing SQL query statements to create the database, while the compiled database usually has a .s3db extension. You can create a database from the file containing SQL statements using "sqlite3 database.s3db < database.sqlite". — Hampus Nilsson, Apr 27 '12 at 12:25

score 1 · Answer 2 · answered Dec 02 '12 at 07:47

I am working with the same issues right now. But to answer your question: the documents folder is the folder that is to be used in the device.

Put the database in the the supporting files folder.

Here is what works for me:

//hide status bar for full screen view
[[UIApplication sharedApplication]setStatusBarHidden:YES          withAnimation:UIStatusBarAnimationNone];

//set reference to database here
NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
            NSString *documentDir = [documentPaths objectAtIndex:0];
            self.databasePath = [documentDir stringByAppendingPathComponent:@"RPRA.sqlite"];

    [self createAndCheckDatabase];

    // Override point for customization after application launch.
    return YES;
}

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

-(void) createAndCheckDatabase
{
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    success = [fileManager fileExistsAtPath:self.databasePath];

    if(success) return;

    //else move database into documents folder
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath]
                                     stringByAppendingPathComponent:@"RPRA.db"];

    [fileManager copyItemAtPath:databasePathFromApp toPath:self.databasePath error:nil];
}

Also, I would like to point out that XCode is pretty touchy about the SQL or SQLite files that you use. If you are having problems getting your app to read your SQL file (such as no column found error in all your tables), check to make sure your Firefox SQLite Manager tool isn't adding any extra meta-tables. I had to recreate my datatbase into another database by doing Command A and pasting it into the Firefox SQLite Manager Run SQL section and execute the sql. This created a new database that XCode could read without any meta tables. — Droid Chris, Oct 23 '13 at 16:16

score 0 · Answer 3 · edited Oct 17 '14 at 17:35

- (void)ensureDBFile
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *dbFilePath = [documentsDirectory stringByAppendingPathComponent:[NSString stringWithFormat:@"retails.sqlite"]];

    NSLog(@"Database Filepath Delgate = %@", dbFilePath);

    NSFileManager *fileManager = [NSFileManager defaultManager];
    BOOL dbFileExists = [fileManager fileExistsAtPath:dbFilePath];

    if(!dbFileExists){
        //Copy the db file if it didn't exist
        NSString *bundledDbPath = [[NSBundle mainBundle] pathForResource:@"retails" ofType:@"sqlite"];

        if (bundledDbPath != nil) {
            BOOL copiedDbFile = [fileManager copyItemAtPath:bundledDbPath toPath:dbFilePath error:nil];
            if (!copiedDbFile) {
                NSLog(@"Error: Couldn't copy the db file from the bundle");
            }
        }
        else {
            NSLog(@"Error: Couldn't find the db file in the bundle");
        }
    }
    self.db = [FMDatabase databaseWithPath:dbFilePath];
    [self.db setShouldCacheStatements:NO];
    [self.db open];
    if([self.db hadError]) {
    NSLog(@"Database Error %d: %@", [self.db lastErrorCode], [self.db lastErrorMessage]);
    }
}

how do we open an already existing database fmdb and where to include it?

3 Answers3

Linked