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;    
  }
  

Answer 1

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 :).

Answer 2

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.