Saving data into sqlite3 database in Swift

Question

I am working on an app that uses PencilKit. I am trying to save the PK drawing in the form of data in a sqlite3 database but it is not saving. I assume the problem is with the save function, not the one that fetches the drawing data to display because the drawing row in the database is empty when I query directly in terminal.

func save(canvas: Canvas) {
    // connect to database
    connect()
    
    // canvas.drawing is already in the form of data not PK drawing here
    let drawingData = canvas.drawing
    
    drawingData.withUnsafeBytes { drawingBuffer in
        
        let drawingPtr = drawingBuffer.baseAddress!

        var statement: OpaquePointer!
        
        if sqlite3_prepare_v2(database, "UPDATE drawings SET drawing = ? WHERE rowid = ?", -1, &statement, nil) != SQLITE_OK {
            print("Could not create (update) query")
        }
        
        sqlite3_bind_blob(statement, 1, drawingPtr, -1, nil)
        sqlite3_bind_int(statement, 2, Int32(canvas.id))
        
        if sqlite3_step(statement) != SQLITE_DONE {
            print("Could not execute update statement")
        }
        
        sqlite3_finalize(statement)

    }
}

Answer 1

A few observations:

1. With sqlite3_bind_blob, the fourth parameter is the size, which is required, and must not be negative. With sqlite3_bind_text, you can supply a -1 value because C strings are null terminated and it can figure out where the string ends, but it can’t do that with a blob.

   As the documentation says:

   If the fourth parameter to sqlite3_bind_blob() is negative, then the behavior is undefined.

   Thus, perhaps:

   guard let blob = drawingBuffer.baseAddress else { return }
   let count = Int32(drawingBuffer.count)
   

   And then:

   sqlite3_bind_blob(statement, 1, blob, count, nil)
   

2. Also, if a SQLite call fails, you should print the error, otherwise you are flying blind, e.g.

   let errorMessage = sqlite3_errmsg(database).map { String(cString: $0) } ?? "Unknown error"
   print("failure preparing UPDATE statement: \(errorMessage)")
   

   You should do that whenever you check a SQLite return code. Often this will make it much easier to diagnose why a call failed.