sqlite for swift is unstable

Question

I've set up swift project to use sqlite. sometimes, when inserting it doesn't actually insert the correct (or all) the values. I know because I restart the app, and when I come back in the entries are either randomly wrong (with stuff not inserted) or nil. but sometimes correct.

here is where I set it, and yes the data is correct in the values before inserting.

let update = "INSERT INTO ToDoItem (itemName, completed, goalDate) " + "VALUES (?, ?, ?);"
var statement: COpaquePointer = nil
if sqlite3_prepare_v2(database, update, -1, &statement, nil) == SQLITE_OK {
    let itemName = item.itemName as String
    let completed = item.completed == true ? 1 : 0

    sqlite3_bind_text(statement, 1, itemName, -1, nil)
    sqlite3_bind_int(statement, 2, Int32(completed))

    if let goalDate = item.goalDate?.toString() {
        sqlite3_bind_text(statement, 3, goalDate, -1, nil)
    } else {
        sqlite3_bind_text(statement, 3, "", -1, nil)
    }

    //println("inserting \(itemName), \(completed) and \(item.goalDate?.toString())")
    //println("")
}

if sqlite3_step(statement) != SQLITE_DONE {
    println("error updateing table")
    sqlite3_close(database)
    return
}
sqlite3_finalize(statement)

sqlite3_close(database)

you can see the commented out println in the middle, if that is not commented out, then the itemName sometimes gets part of that string.

Answer 1

I had the same problem. I found the way to resolve this problem.

sqlite3_bind_text(statement, 1, itemName, -1, nil) --> itemName should be UTF8 String

You should convert itemName as NSString and use UTF8String to convert your string to UTF8. Right code is the same here

let itemName = item.itemName as NSString
sqlite3_bind_text(statement, 1, itemName.UTF8String, -1, nil)

Good luck.

Answer 2

In swift SQLite doesn't have SQLITE_TRANSIENT and SQLITE_STATIC defined, so we need to explicitly define it.

swift 3 & 4

Define following property of SQLITE

let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)

SQL function Add SQLITE_TRANSIENT instead of nil as last parameter while binding text sqlite3_bind_text.

let update = "INSERT INTO ToDoItem (itemName, completed, goalDate) " + "VALUES (?, ?, ?);"
var statement: OpaquePointer = nil
if sqlite3_prepare_v2(database, update, -1, &statement, nil) == SQLITE_OK {
    let itemName = item.itemName as String
    let completed = item.completed == true ? 1 : 0

    sqlite3_bind_text(statement, 1, itemName, -1, SQLITE_TRANSIENT)
    sqlite3_bind_int(statement, 2, Int32(completed))

    if let goalDate = item.goalDate?.toString() {
        sqlite3_bind_text(statement, 3, goalDate, -1, SQLITE_TRANSIENT)
    } else {
        sqlite3_bind_text(statement, 3, "", -1, SQLITE_TRANSIENT)
    }

    //println("inserting \(itemName), \(completed) and \(item.goalDate?.toString())")
    //println("")
}

if sqlite3_step(statement) != SQLITE_DONE {
    println("error updateing table")
    sqlite3_close(database)
    return
}
sqlite3_finalize(statement)

sqlite3_close(database)

Referenced from SQLITE_TRANSIENT undefined in Swift