sqlite3 "unable to open database file" - ios

Question

I am using sqlite3 in my project.

I am getting error after couple(50-60) of transaction that "unable to open database file",So check my database file path but path is correct and file is there.
I tried each and every solution discussed on stack overflow, but with no
luck.
I check my "DocumentDirectory" path, done all necessary step before to close database. Like:

sqlite3_finalize(selectStatement);

sqlite3_close(database);

I don't know how to tackle this problem.can I check that my sqlite3 database is open or not.

====================== 1============================

+(NSMutableArray*)executeDataSet:(NSString*)query
    {
            NSMutableArray  *arryResult = [[NSMutableArray alloc] initWithCapacity:0];


        const char *sql = [query UTF8String];
        sqlite3_stmt *selectStatement;
            sqlite3 *database = [DataBaseClass openDataBase];

        //prepare the select statement
        int returnValue = sqlite3_prepare_v2(database, sql, -1, &selectStatement, NULL);
        if(returnValue == SQLITE_OK)
        {
          //my code 
        }

        }
        //sqlite3_reset(selectStatement);
          // NILOBJECT(selectStatement);
        // NILOBJECT(selectStatement);
        sqlite3_finalize(selectStatement);
        sqlite3_close(database);

       return arryResult;
    }

==================== 2 =================================

    +(sqlite3 *) openDataBase {
        sqlite3 * edenAdultDatabase;
        NSString * databasePath =[DataBaseClass pathForDatabase]; 

        if(sqlite3_open([databasePath UTF8String], &edenAdultDatabase) == SQLITE_OK) {

            NSLog(@"Yes database is open");
            return edenAdultDatabase;
        }  
        else
          {
            NSLog(@"do something %s",sqlite3_errmsg(edenAdultDatabase));
          } 
        return edenAdultDatabase;

    }

====================== 3 ===========================

+(NSString *) pathForDatabase {
    NSString *libraryDir = [FileManager pathForPrivateDocumentsFolder];

    NSFileManager *fileMgr = [NSFileManager defaultManager];

    NSError *error;

    NSString *privateFolderPath = [libraryDir stringByAppendingPathComponent:@"DataBase"];

    if (![fileMgr fileExistsAtPath:privateFolderPath])
    {
        [fileMgr createDirectoryAtPath:privateFolderPath withIntermediateDirectories:NO attributes:nil error:&error];
    }

    /*
    // My database in library private folder ..this is just for test. 
    // I copied databae to document dir but no luck.
    NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [documentPaths objectAtIndex:0];
    privateFolderPath = [documentsDir stringByAppendingPathComponent:kDatabaseName];
    */

    privateFolderPath = [privateFolderPath stringByAppendingPathComponent:kDatabaseName];

    return privateFolderPath;    
}

The problem probably rests in some of the code you haven't shared with us. You're not showing us which line is reporting the error you report, either. I wonder if there's some path where you open database, but neglect to close it. Also, log every close (that way you can make sure the open and close statements are properly paired). I might suggest logging error if open failed (right now, it will be silent if open failed, which is harder to identify in your log). BTW, there's no need to open and close the database for every database interaction. — Rob, Apr 02 '14 at 05:33
Also, you might want to seriously consider [FMDB](https://github.com/ccgus/fmdb), which simplifies SQLite programming. — Rob, Apr 02 '14 at 05:33
@Rob sqlite3_close(database); NSLog(@"database close %d", sqlite3_close(database)); //gives me 21 is it ok. — utkal patel, Apr 02 '14 at 05:44
That's the idea, but you're calling it twice (that's why you're getting 21 - `SQLITE_MISUSE` on the second call). Only call it once, saving the return code in a variable, and include that in the `NSLog`. Also, just like you have an `openDataBase` method, perhaps have a `closeDataBase` method, and replace all of your individual `sqlite3_close` calls with the call to this method. Anyway, once you have this logging, you can now confirm that you don't have any mismatched open/close pairs. — Rob, Apr 02 '14 at 05:49
@Rob thanks rob, I really appreciate your attention. I am getting sqlite3_close '0' which is perfect but after couple of transaction I am getting "unable to open file". See my updated "opendatabse" method i am going into else part after some transaction. — utkal patel, Apr 02 '14 at 06:00

score 5 · Answer 1 · edited May 23 '17 at 11:54

There is a weird issue in database connectivity, sometime it does not connect. Therefore it is recommended by people that your application should open a database once (during in initialisation phase) and close the connection when application is terminating.

Reference: Sqlite opening issue

Regarding checking database connectivity, Sqlite3 does not provide any method to check that either database is open or not.

By using Shared instance of database manager, you can achieve it. Define a boolean at class level and set it's value when you open the database:

// .h file
BOOL isDatabaseOpen;

// .m file
-(void) openDatabase
{
    if(![self isDatabaseExist])
    {
        // copy database to library
        [self copyDatabaseFile];
    }

    NSString *sqLiteDb = [self getDatabaseLibraryPath];
    if (sqlite3_open([sqLiteDb UTF8String], &_databaseHandler) != SQLITE_OK) {
        NSLog(@"Database --> Failed to open");
        isDatabaseOpen = NO;
    }
    else
    {
        isDatabaseOpen = YES;
    }
}

and then you can use the following method to check is database opened or not.

-(BOOL) isDatabaseOpen
{
    return isDatabaseOpen;
}

Let me know if it worked :).

Nice idea and nice work! I openend my database on every query. From a loop with 14k datasets where saved only 3k of them.. opening the database once (!) saved all 14k. Thx! — Rikco, Jun 21 '16 at 13:35

score 3 · Answer 2 · answered Apr 02 '14 at 06:20

check out this kind of solution

first of all create function like below:

-(void)checkDBAndCopy{

NSArray *dirPath=NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,NSUserDomainMask, YES);
NSString *connectionPath=[dirPath objectAtIndex:0];
strDBPath=[connectionPath stringByAppendingPathComponent:@"database.sqlite"];
NSLog(@"%@",strDBPath);
NSFileManager *filemanager=[[NSFileManager alloc]init];
if (![filemanager fileExistsAtPath:strDBPath]) {
    NSString *databasePathFromApp=[[[NSBundle mainBundle]resourcePath]stringByAppendingPathComponent:@"database.sqlite"];

    [filemanager copyItemAtPath:databasePathFromApp toPath:strDBPath error:nil];
}
}

and call this function like below method:-

-(NSMutableArray *)RetriveSharedspots:(NSString *)Query{

[self checkDBAndCopy];

if (sqlite3_open([strDBPath UTF8String], &contactDB)==SQLITE_OK) {

    sqlite3_stmt *statement;

    if (sqlite3_prepare_v2(contactDB, [Query UTF8String],-1,&statement,NULL)==SQLITE_OK)        
        {

        while (sqlite3_step(statement)==SQLITE_ROW) {

           // Your code
        }
    }
    sqlite3_finalize(statement);
}
sqlite3_close(databaseName);

return array;

    }

Above this worked for me great. try this.

score 0 · Answer 3 · answered Jan 16 '19 at 19:40

Just sharing my case with this issue.

I have a project that uses databaseFile1.sqlite and I am not sure if there was a build of it installed on my simulator.

Then I changed the database file, say databaseFile2.sqlite different contents, different filename. Then this issue came up. As I read the solutions and comments, I realized that the issue shouldn't be so biggie.

Welp, I deleted the build and restarted Xcode. Voila. It's okay now. Later on, I will revert back to databaseFile1.sqlite the database, and I'll see if this issue can be reproduced.

sqlite3 "unable to open database file" - ios

3 Answers3