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)

    }
}

Rob · Accepted Answer · 2020-10-16 14:10:03Z

3

A few observations:

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)
```
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.

edited Oct 16, 2020 at 14:10

answered Oct 16, 2020 at 13:01

Rob

441k74 gold badges850 silver badges1.1k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Leo Dabus Over a year ago

or simply sqlite3_errmsg(database).map(String.init)

Clare Over a year ago

Thank you Rob! The database is not empty now!

Collectives™ on Stack Overflow

Saving data into sqlite3 database in Swift

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related