iPhone as Xcode simulator doesn't read from sqlite database

Question

I have coded an app that utilizes the sqlite database that ships with Xcode. It works well on the mac but when I select iPhone as simulator the app (on iPhone) doesn't read database data. Do I need to code access to the sqlite database differently from how it accesses it on the mac?

Here is my function to get data from a database that is used in a picker view. This picker view does not populate when I use an iPhone (linked to the computer via usb). However it populates when I run any of the other listed simulators

struct Gender {
    let gender:String
}

var genderCode = [Gender]()

func readGenderCode(){
    //database setup
    let fileURL = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false) .appendingPathComponent("OTC.sqlite")
    //opening the OTC database
    if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
        print("error opening database")
    }

    //get data by query
    let queryString = "SELECT gender from gender"

    //statement pointer
    var stmt2:OpaquePointer?

    //preparing the query
    if sqlite3_prepare(db, queryString, -1, &stmt2, nil) != SQLITE_OK {
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing read: \(errmsg)")
        return
    }

    //go through gender table records
    while(sqlite3_step(stmt2) == SQLITE_ROW)  {
        let gc = String(cString: sqlite3_column_text(stmt2,0))

        //populate the array
        genderCode.append(Gender(gender: String(describing: gc)))
    }

}

Answer 1

This behavior, where you’re not seeing the data in the database, generally just means that it is empty.

So, first, confirm this thesis. Download the container from the physical device (see https://stackoverflow.com/a/38064225/1271826) and open up the database found in this container from macOS and examine what it contains. I’m wagering that the database or the table is empty.

Assuming this is indeed the problem, it begs the question as to how you ended up with a blank database on that device rather than a copy of the database within your app bundle. Most likely, at some point during development and testing on the physical device, the app accidentally opened a database in the documents folder without first successfully copying the bundle version. Unfortunately, the default behavior of sqlite3_open is to create a blank database if one is not found. So, you want to (a) remove that blank database from your device; and (b) write code that prevents this from being able to happen in the future.

I would therefore suggest:

1. Remove your app from the device in question. This will remove any blank databases created during this development/testing process.

2. If you are distributing app with prepopulated database, remove all references to sqlite3_open and replace them with sqlite3_open_v2, using only the SQLITE_OPEN_READWRITE option (making sure that the app cannot possibly create a blank database for you). Specifically, do not use the SQLITE_OPEN_CREATE option.

3. Revisit your open routine. E.g. you might do something like:

   func open() -> Bool {
       let fileUrl = try! FileManager.default
           .url(for: .applicationSupportDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
           .appendingPathComponent(databaseName)
   
       if sqlite3_open_v2(fileUrl.path, &db, SQLITE_OPEN_READWRITE, nil) == SQLITE_OK {
           return true
       }
   
       close() // even though open failed, you still need to close it
   
       guard let bundleURL = Bundle.main.url(forResource: databaseName, withExtension: nil) else {
           print("not found in bundle")
           return false
       }
   
       try? FileManager.default.copyItem(at: bundleURL, to: fileUrl)
   
       guard sqlite3_open_v2(fileUrl.path, &db, SQLITE_OPEN_READWRITE, nil) == SQLITE_OK else {
           let error = sqlite3_errmsg(db).flatMap { String(cString: $0, encoding: .utf8) }
           print(error ?? "unknown error")
           close()
           return false
       }
   
       return true
   }
   
   func close() {
       sqlite3_close(db)
       db = nil
   }
   

   There are a few things that I’d like to draw your attention to in the above:

   • I would suggest you use the application support directory rather than the documents folder. See iOS Standard Directories: Where Files Reside or watch iOS Storage Best Practices video.

   • Just try to open database in the application support folder. If it fails, try copying from bundle to application support directory and try again.

   • By the way, if sqlite3_open_v2 (or sqlite3_open) fails, remember that you still have to call sqlite3_close. As the SQLite documentation says, “Whether or not an error occurs when it is opened, resources associated with the database connection handle should be released by passing it to sqlite3_close() when it is no longer required.”

4. Now your readGenderCode can use this open routine:

   func readGenderCode() -> [Gender]? {
       guard open() else { return nil }
   
       defer { close() }
   
       let sql = "SELECT gender from gender"
   
       var statement: OpaquePointer?
   
       guard sqlite3_prepare(db, sql, -1, &statement, nil) == SQLITE_OK else {
           let errmsg = sqlite3_errmsg(db).flatMap { String(cString: $0) }
           print("error preparing read:", errmsg ?? "Unknown error")
           return nil
       }
   
       defer { sqlite3_finalize(statement) }
   
       var genders = [Gender]()
   
       //go through gender table records
       while sqlite3_step(statement) == SQLITE_ROW {
           if let cString = sqlite3_column_text(statement, 0) {
               let string = String(cString: cString)
               genders.append(Gender(gender: string))
           }
       }
   
       return genders
   }
   

   Note:

   • If you’re going to open database when running SQL, you’ll want to close it when you’re done. Personally, I open database once and leave it open (rather than littering open calls throughout my database controller), but if you’re going to constantly re-open the database for every SQL call, remember to close it, too.

   • If your “prepare” succeeded, make sure to “finalize” it, too, or else you will leak.

   • I’d suggest avoiding force unwrapping operator, !, if you can.