How to Update a column in a SQLite table in a Swift program?

Question

Here's the code i have which crashes at runtime at the first bind:

func update(time: Date) throws {
    let sql = "UPDATE Entrys SET date = ? WHERE id = ?"  //Questionable
    guard let update = try db.prepareStatement(sql: sql) else { throw SQLiteError.prepare }
    sqlite3_finalize(update)

    let iso8601Time = ISO8601Time(date: time)
    guard sqlite3_bind_text(update, 1, iso8601Time.concise(), -1, SQLITE_TRANSIENT) == SQLITE_OK else { throw SQLiteError.bind }  //Throws error here

    guard sqlite3_bind_int(update, 2, Int32(id)) == SQLITE_OK else { throw SQLiteError.bind }

    guard sqlite3_step(update) == SQLITE_ROW else { throw SQLiteError.step }
}

ISO8601Time is an object to convert between Text and Swift.Date it works in other code where i do inserts.

The docs aren't helping me out.

It could be that my SQL syntax is incorrect. I haven't found a reliable example online.

Please advise.

=)

Edit 1:

I have confirmed that the following code works in the sqlite editor of the terminal. Which is similar to the form above

Update Entrys SET date = "test" WHERE id = 1;

I have also confirmed that my sqlite3_bind_text and sqlite3_bind_int work in other functions that are unit tested and passing. I'm not sure how to transfer this syntax to an UPDATE and the docs don't have anything that i've found so far.

EDIT 2:

The following show the SQLite syntax for an insert that works and has multiple parameters and might help to solve the issue.

func add(date: Date, confirmed: Bool, scale: Int, measurement: SQLMeasurement) throws -> SQLEntry {
    let sql = "INSERT INTO Entrys (date, confirmed, scale, measurementID) VALUES (?,?,?,?);"
    guard let insert = try? db.prepareStatement(sql: sql) else { fatalError() }
    defer { sqlite3_finalize(insert) }

    let iso8601Time = ISO8601Time(date: date)
    guard sqlite3_bind_text(insert, 1, iso8601Time.concise(), -1, SQLITE_TRANSIENT) == SQLITE_OK else { throw SQLiteError.bind }

    var bool: Int    //FIXME: Vars are trouble
    if confirmed { bool = 1 }
    else { bool = 0 }
    guard sqlite3_bind_int(insert, 2, Int32(bool)) == SQLITE_OK else { throw SQLiteError.bind }

    guard sqlite3_bind_int(insert, 3, Int32(scale)) == SQLITE_OK else { throw SQLiteError.bind }
    guard sqlite3_bind_int(insert, 4, Int32(measurement.id)) == SQLITE_OK else { throw SQLiteError.bind }

    guard sqlite3_step(insert) == SQLITE_DONE else { throw SQLiteError.step }
    let lastId = db.lastId()
    return SQLEntry(id: lastId, db: db)
}

user_4117097 · Answer

Actually you need to pass null-terminated utf8 string to sqlite3_bind_text. That can be done in the following way:

let iso8601TimeStr = iso8601Time.concise() as NSString
guard sqlite3_bind_text(update, 1, iso8601TimeStr.UTF8String, -1, SQLITE_TRANSIENT) == SQLITE_OK else { throw SQLiteError.bind }

user_2331445 · Answer

The correct order is:

create the prepared statement
bind values to parameters
run the SQL
destroy the object to avoid resource leaks (with sqlite3_finalize(stmt))

You create the prepared statement and delete it immediately, but then you still access it, which leads to a crash.

Documentation:

The sqlite3_finalize() function is called to delete a prepared statement.

see https://www.sqlite.org/c3ref/finalize.html

How to Update a column in a SQLite table in a Swift program?

2 Answers