SQLite in Swift : cannot insert, update, delete records after insert a record to database

Question

I have a problem with Sqlite in Swift. Every time I run my app on simulator and insert a new record, then after insert successfully, I can not insert or update, delete any records, it's like database changed to read-only. But if I run my app without inserting a new record, then update, delete statements work totally fine. I do open and then close the connection to database each time execute any SQL statements. Here's my insert function :

func insertJobToDatabase(label: String, startTime: NSDate, finishTime: NSDate, startDay: NSDate, finishDay: NSDate, color: String){

    let db = connectDatabase("TimeTable.sqlite")
    // Set date formatter
    let dateFormatter = NSDateFormatter()
    dateFormatter.dateFormat = "dd-MM-yyyy HH:mm"

    //Insert string of interting new job
    let insertStatementString = "INSERT INTO Jobs VALUES (\"\(label)\",'\(dateFormatter.stringFromDate(startTime))','\(dateFormatter.stringFromDate(finishTime))','\(dateFormatter.stringFromDate(startDay))','\(dateFormatter.stringFromDate(finishDay))',\"\(color)\", \(Int(self.getMaxIdOfTable(db)+1)));"

    //compile Insert string
    var insertStatement: COpaquePointer = nil
    if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {

        //Execute Insert string
        if sqlite3_step(insertStatement) == SQLITE_DONE {
            print("Successfully inserted row.")
        } else {
            print("Could not insert row.")
        }
    } else {
        print("INSERT statement could not be prepared.")
    }
    // 5
    sqlite3_finalize(insertStatement)

    if sqlite3_close_v2(db) == SQLITE_OK{
        print("closed")
    }
}

update function :

func updateDatabase(updateStatementString: String){

    let db = connectDatabase("TimeTable.sqlite")
    var updateStatement: COpaquePointer = nil
    if sqlite3_prepare(db, updateStatementString, -1, &updateStatement, nil) == SQLITE_OK{
        if sqlite3_step(updateStatement) == SQLITE_DONE{
            print("Successfully update row.")
        }
        else{
            print("Could not update row.")
        }
    }
    else{
        print("UPDATE statement could not be prepared")
    }
    sqlite3_finalize(updateStatement)

    if sqlite3_close_v2(db) == SQLITE_OK{
        print("closed")
    }
}

Delete function :

    func deleteInDatabase(id: Int){

    let db = connectDatabase("TimeTable.sqlite")
    let deleteStatementString = "DELETE FROM Jobs WHERE id = \(id)"
    var deleteStatement: COpaquePointer = nil
    if sqlite3_prepare(db, deleteStatementString, -1, &deleteStatement, nil) == SQLITE_OK{
        if sqlite3_step(deleteStatement) == SQLITE_DONE{
            print("Successfully deleted row.")
        }
        else{
            print("Could not delete row.")
        }
    }
    else{
        print("DELETE statement could not be prepared")
    }
    sqlite3_finalize(deleteStatement)

    if sqlite3_close_v2(db) == SQLITE_OK{
        print("closed")
    }
}

connectDatabase function :

    func connectDatabase(fileName: String) -> COpaquePointer {
    var db: COpaquePointer = nil

    //Searching for path of database
    let documentsURL = NSFileManager.defaultManager().URLsForDirectory(.DocumentDirectory, inDomains: .UserDomainMask)[0]
    let fileURL = documentsURL.URLByAppendingPathComponent(fileName)

    let dbPath: String = fileURL!.path!
    let fileManager = NSFileManager.defaultManager()
    if !fileManager.fileExistsAtPath(dbPath){       //If database not exist then...

        let documentURL = NSBundle.mainBundle().resourceURL
        let fromPath = documentURL!.URLByAppendingPathComponent(fileName)        //Get database path from projects location

        var error: NSError?

        do {
            try fileManager.copyItemAtPath(fromPath!.path!, toPath: dbPath) //Try to copy database from projects location to applications documents location
        } catch let error1 as NSError{
            error = error1
        }

        let alert: UIAlertView = UIAlertView()

        if(error != nil){
            alert.title = "Error Occured"
            alert.message = error?.localizedDescription //If database is not exist in projects location then pop out error alert
        }
        else {
            alert.title = "Successfully Copy"                   //Notify by an alert if copy successfully
            alert.message = "Your database copy successfully"
            if sqlite3_open(dbPath, &db) == SQLITE_OK {
                print("Successfully opened connection to database")     //Open database just copied
            } else {
                print("Unable to open database")
            }

        }
        alert.delegate = nil
        alert.addButtonWithTitle("Ok")
        alert.show()
    }
    else{
        print("Database already exist in \(dbPath)")            //Notify by an alert if there's already a database in applications documents location
        if sqlite3_open(dbPath, &db) == SQLITE_OK {
            print("Successfully opened connection to database") //Open database without copy
        } else {
            print("Unable to open database")
        }
    }

    return db
}

I'm sure that my app connected to database and there's nothing wrong with update, delete statements because I'm still able to update, delete records if it's not insert any records yet.

Answer 1

A couple of thoughts:

1. If sqlite3_open fails, you should examine the numeric return code. If another SQLite call fails, I'd suggest examining the result of sqlite3_errmsg. Using those, you can diagnose why it's failing.

2. Having done that, you informed us that it reported "database is locked".
   That is generally a result of trying to perform a query while another is still in progress. I don't see anything here, but I wonder if you have some other code that does a SELECT for which you may have neglected to finalize/close (or perhaps you're in the middle of a SELECT and then trying to do one of these update statements).

   I'd suggest putting a log statement in connectDatabase and again everywhere you close the database, and I bet you'll find some sequence where you try to open a database prior to seeing the close of the prior call.

   Regardless, this "database is locked" has been discussed extensively on Stack Overflow, so see that for more information.