Table not found after adding preloaded sqlite db to iOS App using Xcode

Question

I have a preloaded SQLite database, named "myDB.dms". I want to package the DB and access the contents from within the app.

So to that first, I dragged and dropped the DB file in my Xcode ProjectNavigator window and on a prompt I click on "Copy files if needed".

I use FMDB library to access the SQLite DB.

I created a new Database interface class and added 3 different methods:

1. openDB
2. copyDB
3. executeQuery

Because while packaging the DB file will in Bundle resource folder, I had to copy the files from resource folder to directory folder as below:

func copyDB() -> Bool {
    let fileManager = FileManager.default
    let documentsUrl = fileManager.urls(for: .documentDirectory, in: .userDomainMask)
    var returnStatus: Bool = false

    guard documentsUrl.count != 0 else {
        print("Could not find documents url")
        return false
    }

    let finalDatabaseURL = documentsUrl.first!.appendingPathComponent(dbFileName)

    if !((try? finalDatabaseURL.checkResourceIsReachable()) ?? false) {
        print("DB does not exist in documents folder")

        let documentsURL = Bundle.main.resourceURL?.appendingPathComponent(dbFileName)

        do {
            try fileManager.copyItem(atPath: (documentsURL?.path)!, toPath: finalDatabaseURL.path)
        } catch let error as NSError {
            print ("Couldnt copy file to final location! Error:\(error.description)")
            returnStatus = false
        }
    } else {
        print ("Database file found at path: \(finalDatabaseURL.path)")
        returnStatus = true
    }

    return returnStatus
}

Below is the code to OpenDatabase

func openDB() -> Bool {
    let fileManager = FileManager.default
    let documentsUrl = fileManager.urls(for: .documentDirectory, in: .userDomainMask)
    let dbPath = documentsUrl.first!.appendingPathComponent(dbFileName)
    let database = FMDatabase(url: dbPath)

    var returnStatus: Bool = false

    if (self.copyDB() == false) {
        returnStatus = false
    } else {
        if (!database.open(withFlags: 1)) {
            print("Could not open database at \(dbPath.absoluteString)")
            returnStatus = false
        } else {
            self.database = database
            returnStatus = true
        }
    }

    return returnStatus
}

and below is the code for executing a query

func executeQuery(queryString:String) {
    print(queryString)
    do {
        if (database.open()){
            let results:FMResultSet = try database.executeQuery(queryString, values: nil)

            if results.next() == true {
                print(results)
            }
        }
    } catch let error as NSError {
        print(error.description)
    }
}

The copyDB() and openDB() works fine, but then I try to executeQuery(), I get below error:

Error Domain=FMDatabase Code=1 "no such table: tableName" UserInfo={NSLocalizedDescription=no such table: tableName}

Fracture

At a guess (sorry not familiar with IOS) the open before the copy creates the database thus the copy considers nothing needs to be done i.e. the database itself exists, albeit empty. I'd suggest 1) check if the file exists. 2) if the file doesn't exist then copy the file. 3) open the database. 4) execute the query. – Magnien

You are right. On further debugging, I found that the file is not getting copied from bundle resource to documents folder. The size in Bundle is 61734912, while in Documents is 0. Can anyone please confirm if the way I am copying is right as I catch exception Error Domain=NSCocoaErrorDomain Code=4 "The file “H1BData.dms” doesn’t exist." – Fracture

@Fracture I'm looking for tutorial/article to achieve the pre-populated database but doesn't get any help. Can you please suggest me any good article on the same thing you done. Else Source Code. – Blest

Answer 1

Below is the code which worked:

    func copyDataBase() -> Bool {
    let fileManager = FileManager.default
    var dbPath = ""

    do {
        dbPath = try fileManager.url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true).appendingPathComponent(dbFileName).path
    } catch {
        print(error.localizedDescription)
        return false
    }

    if !fileManager.fileExists(atPath: dbPath) {
        let dbResourcePath = Bundle.main.path(forResource: "dbName", ofType: "sqlite")
        do {
            try fileManager.copyItem(atPath: dbResourcePath!, toPath: dbPath)
        } catch {
            print(error.localizedDescription)
            return false
        }
    }
    return true
}

I hope this will help somebody in need

Fracture

Answer 2

  Firstly Create FileManager Object. Get Path and check file is exist or not then copy items from db.
     func callForCopyDBAllObejct() -> Bool {
     let fileM = FileManager.default
     var dbPath = nil

    do {
      dbPath = try fileM.url(for: .applicationSupportDirectory, 
 in: .userDomainMask, appropriateFor: nil, create: 
   true).appendingPathComponent(dbFileName).path
   } catch {
      print(error.localizedDescription)
       return false
   }

     if !fileM.fileExists(atPath: dbPath) {
       let dbResourcePath = Bundle.main.path(forResource: "dbName", 
   ofType: "sqlite")
       do {
         try fileM.copyItem(atPath: dbResourcePath!, toPath: 
    dbPath)
       } catch {
        print(error.localizedDescription)
        return false
       }
   }
   return true

}

Cayman

Thanks Sunil Kumar, this is exactly same answer as mine which worked for me. – Fracture