SQLITE_BUSY error in iOS application

Question

I am writing an iOS application to allow medical doctors to select diagnoses codes from a database and bill patients that are also in the sqlite3 database. The main page is a form that asks for the patient name and date of birth. Also on the main page is a button to begin the search (drill down through body locations) for the diagnoses code.

I am using a tab bar with a bill tab, patients tab, and a doctors tab. A user can add patients and doctors from their respective pages. This works and shows the patients/doctors in their list unless I have navigated to the diagnoses detail page on the bill tab.

I keep my database connection in a "DatabaseManager" class that opens and closes the database file on the device.

class DatabaseManager {

var db:COpaquePointer!

init(){

}
/**
*   Checks that the database file is on the device. If not, copies the database file to the device.
*   Connects to the database after file is verified to be in the right spot.
**/
func checkDatabaseFileAndOpen(){
    let theFileManager = NSFileManager.defaultManager()
    let filePath = dataFilePath()
    if theFileManager.fileExistsAtPath(filePath) {
        db = openDBPath(filePath)
    } else {

        let pathToBundledDB = NSBundle.mainBundle().pathForResource("testDML", ofType: "sqlite3")// Copy the file from the Bundle and write it to the Device
        let pathToDevice = dataFilePath()
        var error:NSError?

        if (theFileManager.copyItemAtPath(pathToBundledDB!, toPath:pathToDevice, error: nil)) {
            db = openDBPath(pathToDevice)
        } else {
            println("database failure")
        }
    }
}

/**
*   Gets the path of the database file on the device
**/
func dataFilePath() -> String {
    let paths = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true)
    let documentsDirectory = paths[0] as! NSString
    return documentsDirectory.stringByAppendingPathComponent("testDML.sqlite3") as String
}

/**
*   Makes a connection to the database file located at the provided filePath
**/
func openDBPath(filePath:String) -> COpaquePointer {

    var db:COpaquePointer  = nil
    var result = sqlite3_open(filePath, &db)
    println("openResult: \(result)")
    if result != SQLITE_OK {
        sqlite3_close(db)
        println("Failed To Open Database")
        return nil
    }else {
        return db
    }
}

func closeDB() {
    var closeResult = sqlite3_close_v2(db)
    print("closed result:\(closeResult)")
    if closeResult == SQLITE_OK {
    }
}

Any time I do a query I open the database, do the query, and then close the database. I get SQLITE_OK on closing and opening the database for every query I run, but I get a SQLITE_BUSY result when adding patients and doctors only when I have navigated to the diagnoses detail page on the bill tab. All the detail page does is retrieve the diagnoses code and update some text on the screen. The add functions for patients and doctors are below:

func addPatientToDatabase(inputPatient:String, dateOfBirth:String, email:String){

    var (firstName, lastName) = split(inputPatient)

    println(dateOfBirth)
    let query = "INSERT INTO Patient (pID,date_of_birth,f_name,l_name, email) VALUES (NULL, '\(dateOfBirth)', '\(firstName)', '\(lastName!)', '\(email)')"
    var statement:COpaquePointer = nil
    if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
        var sqliteResult = sqlite3_step(statement)
        if sqliteResult == SQLITE_DONE {
            println("Saved \(firstName) \(lastName!)")
        }else {
            println("Add patient failed \(sqliteResult)")
        }
    }
}

func addDoctorToDatabase(inputDoctor:String, email:String) {
    var (firstName, lastName) = split(inputDoctor)

    let query = "INSERT INTO Doctor (dID,f_name,l_name, email) VALUES (NULL,'\(firstName)', '\(lastName!)', '\(email)')"
    var statement:COpaquePointer = nil

    if sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK {
        var sqliteResult = sqlite3_step(statement)
        if sqliteResult == SQLITE_DONE {
            println("Saved \(firstName) \(lastName!)")
        }else {
            println("Add doctor failed for \(firstName) \(lastName!) with error \(sqliteResult)")
        }

    }
}

I thought that this wouldn't be a problem because it is impossible for the user to run two queries at once and I have made sure that there is only one connection. Does anyone have any suggestions on what might be happening here?

Answer 1

In a Swift project in addition to Mostruash's answer, you may even need to use sqlite3_close_v2() method. sqllite3_close() method not always returns SQLITE_OK.

If the database connection is associated with unfinalized prepared statements or unfinished sqlite3_backup objects then sqlite3_close() will leave the database connection open and return SQLITE_BUSY. If sqlite3_close_v2() is called with unfinalized prepared statements and/or unfinished sqlite3_backups, then the database connection becomes an unusable "zombie" which will automatically be deallocated when the last prepared statement is finalized or the last sqlite3_backup is finished. The sqlite3_close_v2() interface is intended for use with host languages that are garbage collected, and where the order in which destructors are called is arbitrary.

Applications should finalize all prepared statements, close all BLOB handles, and finish all sqlite3_backup objects associated with the sqlite3 object prior to attempting to close the object. If sqlite3_close_v2() is called on a database connection that still has outstanding prepared statements, BLOB handles, and/or sqlite3_backup objects then it returns SQLITE_OK and the deallocation of resources is deferred until all prepared statements, BLOB handles, and sqlite3_backup objects are also destroyed.

Answer 2

I believe you forget finalizing your prepared statements using sqlite3_finalize(), unless you have unmatched open/close calls or you access the db connection from multiple threads. According to sqlite guidelines:

If the database connection is associated with unfinalized prepared statements or unfinished sqlite3_backup objects then sqlite3_close() will leave the database connection open and return SQLITE_BUSY.