CODEWITHSUNDEEP
iossqlite
Badgersoft
Badgersoft

Reputation: 21

sqlite Prepopulated database is empty when trying to access ios

I am fairly new to Swift 3 and ios, having previously written the program for Android which the database is accessed. The current problem is that the database is prepopulated with 10 tables, however, when I try to access the dbase there are no tables, either in the simulator or device, so cannot access the data. I have searched the forum and internet but can only find information to check if the database exists. The sqlite wrapper is FMDB that is being used.

code:

override func viewDidLoad() {
    super.viewDidLoad()
let filemgr = FileManager.default
let dirPaths = filemgr.urls(for: .documentDirectory, in: .userDomainMask)   

 let destPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!

databasePath = dirPaths[0].appendingPathComponent("level24.db").path

 print(databasePath)

 let fullDestPath = URL(fileURLWithPath: destPath).appendingPathComponent("level24.db")

 let bundleDatabasePath = Bundle.main.path(forResource: "level24", ofType: ".db")

 var level2DB = FMDatabase(path: databasePath as String)


  if filemgr.fileExists(atPath: fullDestPath.path){
  print("Database file is exist")
  print(filemgr.fileExists(atPath: bundleDatabasePath!))

  }else{
  filemgr.replaceItemAt(databasePath, withItemAt: bundleDatabasePath.path)
  }



level2DB = FMDatabase(path: databasePath as String)
 if (level2DB?.open())!{
          print("Database is open")

var querySQL = "SELECT * FROM sqlite_master WHERE name = '\(tblName)' and type='table'"
let results:FMResultSet? = level2DB?.executeQuery(querySQL, withArgumentsIn:nil)

        if (results == nil){
            print("Results = \(results)")

            do{
                try filemgr.copyItem(atPath: bundleDatabasePath!, toPath: fullDestPath.path)
            }catch{
                print("\n",error)
            }

        }
    print("Results after = \(results)")

    let querySQL2 = "SELECT QUESTION FROM tblSani WHERE _ID = 5"

    let results2:FMResultSet? = level2DB?.executeQuery(querySQL2, withArgumentsIn:nil)

    print(results2?.string(forColumn: "QUESTION") as Any)

    }
}

output is:

 /Users/***/Library/Developer/CoreSimulator/Devices/4F511422-2F86-49BF-AB10-5CA74B9A7B40/data/Containers/Data/Application/58DD87EB-C20F-4058-B9BC-CCD7ECEEFA98/Documents/level24.db
Database is open
Results after = Optional(<FMResultSet: 0x608000245220>)
2017-04-05 21:09:26.833 Level 2[3161:210849] DB Error: 1 "no such table: tblSani"
2017-04-05 21:09:26.833 Level 2[3161:210849] DB Query: SELECT QUESTION FROM tblSani WHERE _ID = 5
 2017-04-05 21:09:26.834 Level 2[3161:210849]DBPath: /Users/***/Library/Developer/CoreSimulator/Devices/4F511422-2F86-49BF-AB10-5CA74B9A7B40/data/Containers/Data/Application/58DD87EB-C20F-4058-B9BC-CCD7ECEEFA98/Documents/level24.db
nil

It would be appreciated if you could help me to find a solution to the problem.

Upvotes: 1

Views: 64

Answers (1)

Badgersoft
Badgersoft

Reputation: 21

after working at this for many hours I have come up with this solution: This is put into the AppDelegate.swift and will check at start of app

code:

var filemgr = FileManager.default
static let dirPaths = FileManager().urls(for: .documentDirectory, in: .userDomainMask)
static let destPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!

var databasePath = dirPaths[0].appendingPathComponent("level24.db").path

static let fullDestPath = URL(fileURLWithPath: destPath).appendingPathComponent("level24.db")

static let bundleDatabasePath = Bundle.main.path(forResource: "level24", ofType: ".db")

//function to check if dbase exists
func checkdbase(){

    print(databasePath)

    print("Full path = \(AppDelegate.fullDestPath)")

    var level2DB = FMDatabase(path: databasePath as String)


    if filemgr.fileExists(atPath: AppDelegate.fullDestPath.path){

        print("Database file is exist")
        print(filemgr.fileExists(atPath: AppDelegate.bundleDatabasePath!))
        print("bundle = \(AppDelegate.bundleDatabasePath)")

        let level2DB = FMDatabase(path: databasePath as String)

        if (level2DB?.open())!{
            print("Database is open")

       // use a select statement for a known table and row
   let querySQL2 = "SELECT * FROM tblSani WHERE _ID = 5"

            let results:FMResultSet? = level2DB?.executeQuery(querySQL2, withArgumentsIn:nil)

                if results?.next()==true{
                    print("Database has tables")

                }else{
                    print("Database no tables")
                    removeDB()

                }

        }
    }else{
        removeDB()
    }
}

//function to remove existing dbase then unpack the dbase from the bundle
func removeDB(){

    do{
        try filemgr.removeItem(atPath: AppDelegate.fullDestPath.path)
        print("Database removed")

    }catch {
        NSLog("ERROR deleting file: \(AppDelegate.fullDestPath)")
    }

    do{
        try filemgr.copyItem(atPath: AppDelegate.bundleDatabasePath!, toPath: AppDelegate.fullDestPath.path)
        print("Databse re-copied")
    }catch{
        print("\n",error)
    }

}

call the function from the AppDelegate 'didFinishLaunchingWithOptions'

checkdbase()

If you are able to improve this answer please do to help others who may have had the same problem

Upvotes: 1

Related Questions