Swift SQLite3 syntax and bind

Question

first of all, these are my functions:

Insert function

func insert(book : Book) throws -> Bool {
    var insertPointer: OpaquePointer? = nil
    let query = "INSERT INTO BOOK (bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy) VALUES (?, ?, ?, ?, ?, ?)"

    defer{
        sqlite3_finalize(insertPointer)
    }

    if sqlite3_prepare_v2(db, query, -1, &insertPointer, nil) == SQLITE_OK {
        sqlite3_bind_text(insertPointer, 1, book.bookTitle, -1, nil)
        sqlite3_bind_text(insertPointer, 2, book.bookAuthor, -1, nil)
        sqlite3_bind_text(insertPointer, 3, book.bookDesc, -1, nil)
      //sqlite3_bind_date(insertPointer, 4, book.bookDate,nil)
      //sqlite3_bind_image(insertPointer, 5, book.bookImg, -1, nil)
        sqlite3_bind_text(insertPointer, 6, book.createdBy, -1, nil)

        guard sqlite3_step(insertPointer) == SQLITE_DONE else {
            throw SQLiteError.Step(message: errorMessage)
        }
    } else {
        throw SQLiteError.Prepare(message: errorMessage)
    }

    return true
}

Update function

func update(book : Book) throws -> Bool {
    var updatePointer: OpaquePointer? = nil
    var query = "UPDATE Book SET bookName = ?, bookAuthor = ?, bookDesc = ?, bookDate = ?, bookImg = ?, createdBy = ?, WHERE bookId = ?"

    defer{
        sqlite3_finalize(updatePointer)
    }

    if sqlite3_prepare_v2(db, query, -1, &updatePointer, nil) == SQLITE_OK {

        sqlite3_bind_text(updatePointer, 2, book.bookAuthor, -1, nil)
        sqlite3_bind_text(updatePointer, 3, book.bookDesc, -1, nil)
        //sqlite3_bind_date(updatePointer, 4, book.bookDate,nil)
        //sqlite3_bind_image(updatePointer, 5, book.bookImg, -1, nil)
        sqlite3_bind_text(updatePointer, 6, book.createdBy, -1, nil)
        sqlite3_bind_text(updatePointer, 7, book.bookId, -1, nil)
        guard sqlite3_step(updatePointer) == SQLITE_DONE else {
            throw SQLiteError.Step(message: errorMessage)
        }
    } else {
        throw SQLiteError.Prepare(message: errorMessage)
    }

    return true
}

Delete function

func delete(book : Book) throws -> Bool {
    var deletePointer: OpaquePointer? = nil
    var query = "DELETE FROM Book WHERE bookId = ?"

    defer{
        sqlite3_finalize(deletePointer)
    }

    if sqlite3_prepare_v2(db, query, -1, &deletePointer, nil) == SQLITE_OK {
        sqlite3_bind_text(updatePointer, 1, book.bookId, -1, nil)
        guard sqlite3_step(deletePointer) == SQLITE_DONE else {
            throw SQLiteError.Step(message: errorMessage)
        }
    } else {
        throw SQLiteError.Prepare(message: errorMessage)
    }

    return true
}

And I have a Book class like this:

class Book{
    var bookId : Int
    var bookImg : Data
    var bookTitle : String
    var bookAuthor : String
    var bookDesc : String
    var bookDate : Date
    var createdBy : String

    init(bookId : Int, bookImg : Data, bookTitle : String, bookAuthor : String, bookDesc : String, bookDate : Date, createdBy : String){
        self.bookId = bookId
        self.bookImg = bookImg
        self.bookTitle = bookTitle
        self.bookAuthor = bookAuthor
        self.bookDesc = bookDesc
        self.bookDate = bookDate
        self.createdBy = createdBy
    }
}

I am new to Swift and SQLite. My question is that:

Am I doing it right with the parameter binding?
How do I bind Data and Date type into SQLite query? (the commented line in code above)

Any help would be greatly appreciated!

Rob · Accepted Answer

You asked:

Am I doing it right with the parameter binding?

Largely.

When binding strings, it's probably prudent to use SQLITE_TRANSIENT as the last parameter to sqlite3_bind_text and sqlite3_bind_blob, as defined here:

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

When binding bookId, you want to use sqlite3_bind_int64.
In delete you're referring to updatePointer. Change that to deletePointer.
You should probably be checking these sqlite3_bind_xxx return codes and throwing an error if they're not SQLITE_OK, too.

You then asked:

How do I bind Data and Date type into SQLite query? (the commented line in code above)

Re date type, SQLite doesn't have a native date type (see http://sqlite.org/datatype3.html). So you can either:

Use ISODateFormatter to build a string, and bind the string; if you need fractional seconds, you can use withFractionalSeconds option (introduced in iOS 11, macOS 10.13, etc.), e.g.:
```
let formatter = ISO8601DateFormatter()
formatter.formatOptions.insert(.withFractionalSeconds)
```
If you need milliseconds and need to support those older OS versions, use DateFormatter with dateFormat of yyyy-MM-dd'T'HH:mm:ss.SSSX, a locale of Locale(identifier: "en_US_POSIX"), and a timeZone of TimeZone(secondsFromGMT: 0), and again store and retrieve the date as a string and convert it; or
Use timeIntervalSince1970 of the Date, and insert that as sqlite3_bind_double.

The former string alternatives are easiest to use and makes it easy when visually inspecting the database in third party tools. The timeIntervalSince1970 is arguably a tad more efficient, but it just means you need to use unixepoch to convert the double to an intelligible date if looking at the column in third party SQLite tools, which can be a little cumbersome. It's a trade-off of efficiency vs usability.

Re the Data, insert that using sqlite3_bind_blob.

A couple of final minor observations:

You are deferring the sqlite3_finalize before you sqlite3_prepare_v2. You should defer it after sqlite3_prepare_v2. You should only be finalizing if prepare succeeded, not if it failed.
On updates with a WHERE clause, you might want to check sqlite3_changes to see whether any records changed. For updates by identifier, I changed the functions to throw errors if nothing was updated/deleted.
Several of these functions were defined as throwing errors as well as returning a boolean. For the update/delete functions that doesn't make sense (because we use the error to know whether it succeeded or not, making the boolean return value redundant). So I removed the Bool return type. For other functions (e.g. the SELECT routines) a return value obviously does make sense, but not for these pass/fail update routines.
For the Book properties, I removed the book prefix. It makes sense to have that prefix in SQL (it makes join queries easier to write), but it's redundant in a Swift type. You generally only use that sort of prefix where it's needed for disambiguation (e.g. bookDescription, to avoid confusion with the CustomStringConvertible property, description).

Anyways, pulling that together, you get something like:

var dateFormatter: DateFormatter = {
    let _formatter = DateFormatter()
    _formatter.dateFormat = "yyyy-MM-dd'T'HH:mm:ss.SSSX"
    _formatter.locale = Locale(identifier: "en_US_POSIX")
    _formatter.timeZone = TimeZone(secondsFromGMT: 0)
    return _formatter
}()

var errorMessage: String { return String(cString: sqlite3_errmsg(db)) }

func insert(_ book: inout Book) throws {
    var statement: OpaquePointer? = nil
    let query = "INSERT INTO book (bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy) VALUES (?, ?, ?, ?, ?, ?)"

    guard sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK else {
        throw SQLiteError.prepare(message: errorMessage)
    }

    defer { sqlite3_finalize(statement) }

    guard sqlite3_bind_text(statement, 1, book.title, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_bind_text(statement, 2, book.author, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_bind_text(statement, 3, book.bookDescription, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_bind_text(statement, 4, dateFormatter.string(from: book.createDate), -1, SQLITE_TRANSIENT) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard book.image.withUnsafeBytes({ bufferPointer -> Int32 in
        sqlite3_bind_blob(statement, 5, bufferPointer.baseAddress, Int32(book.image.count), SQLITE_TRANSIENT)
    }) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_bind_text(statement, 6, book.createdBy, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_step(statement) == SQLITE_DONE else {
        throw SQLiteError.step(message: errorMessage)
    }

    book.id = Int(sqlite3_last_insert_rowid(db))
}

func update(_ book: Book) throws {
    guard let id = book.id.flatMap({ Int64($0) }) else {
        throw SQLiteError.noDataChanged
    }

    var statement: OpaquePointer? = nil
    let query = "UPDATE Book SET bookName = ?, bookAuthor = ?, bookDesc = ?, bookDate = ?, bookImg = ?, createdBy = ? WHERE bookId = ?"

    guard sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK else {
        throw SQLiteError.prepare(message: errorMessage)
    }

    defer { sqlite3_finalize(statement) }

    guard sqlite3_bind_text(statement, 1, book.title, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_bind_text(statement, 2, book.author, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_bind_text(statement, 3, book.bookDescription, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_bind_text(statement, 4, dateFormatter.string(from: book.createDate), -1, SQLITE_TRANSIENT) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard book.image.withUnsafeBytes({ bufferPointer -> Int32 in
        sqlite3_bind_blob(statement, 5, bufferPointer.baseAddress, Int32(book.image.count), SQLITE_TRANSIENT)
    }) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_bind_text(statement, 6, book.createdBy, -1, SQLITE_TRANSIENT) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_bind_int64(statement, 7, id) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_step(statement) == SQLITE_DONE else {
        throw SQLiteError.step(message: errorMessage)
    }

    guard sqlite3_changes(db) > 0 else {
        throw SQLiteError.noDataChanged
    }
}

func delete(_ book: Book) throws {
    guard let id = book.id.flatMap({ Int64($0) }) else {
        throw SQLiteError.noDataChanged
    }

    var statement: OpaquePointer? = nil
    let query = "DELETE FROM Book WHERE bookId = ?"

    guard sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK else {
        throw SQLiteError.prepare(message: errorMessage)
    }

    defer { sqlite3_finalize(statement) }

    guard sqlite3_bind_int64(statement, 1, id) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_step(statement) == SQLITE_DONE else {
        throw SQLiteError.step(message: errorMessage)
    }

    guard sqlite3_changes(db) > 0 else {
        throw SQLiteError.noDataChanged
    }
}

func select(bookId: Int) throws -> Book {
    var statement: OpaquePointer? = nil
    let query = "SELECT bookId, bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy FROM Book WHERE bookId = ?"

    guard sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK else {
        throw SQLiteError.prepare(message: errorMessage)
    }

    defer { sqlite3_finalize(statement) }

    guard sqlite3_bind_int64(statement, 1, Int64(bookId)) == SQLITE_OK else {
        throw SQLiteError.bind(message: errorMessage)
    }

    guard sqlite3_step(statement) == SQLITE_ROW else {
        throw SQLiteError.step(message: errorMessage)
    }

    return try book(for: statement)
}

func selectAll() throws -> [Book] {
    var statement: OpaquePointer? = nil
    let query = "SELECT bookId, bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy FROM Book"

    guard sqlite3_prepare_v2(db, query, -1, &statement, nil) == SQLITE_OK else {
        throw SQLiteError.prepare(message: errorMessage)
    }

    defer { sqlite3_finalize(statement) }

    var books = [Book]()

    var rc: Int32
    repeat {
        rc = sqlite3_step(statement)
        guard rc == SQLITE_ROW else { break }
        books.append(try book(for: statement))
    } while rc == SQLITE_ROW

    guard rc == SQLITE_DONE else {
        throw SQLiteError.step(message: errorMessage)
    }

    return books
}

func book(for statement: OpaquePointer?) throws -> Book {
    let bookId = Int(sqlite3_column_int64(statement, 0))

    guard let bookNameCString = sqlite3_column_text(statement, 1) else {
        throw SQLiteError.column(message: errorMessage)
    }
    let bookName = String(cString: bookNameCString)

    guard let bookAuthorCString = sqlite3_column_text(statement, 2) else {
        throw SQLiteError.column(message: errorMessage)
    }
    let bookAuthor = String(cString: bookAuthorCString)

    guard let bookDescCString = sqlite3_column_text(statement, 3) else {
        throw SQLiteError.column(message: errorMessage)
    }
    let bookDesc = String(cString: bookDescCString)

    guard let bookDateCString = sqlite3_column_text(statement, 4) else {
        throw SQLiteError.column(message: errorMessage)
    }
    guard let bookDate = dateFormatter.date(from: String(cString: bookDateCString)) else {
        throw SQLiteError.invalidDate
    }

    let bookImgCount = Int(sqlite3_column_bytes(statement, 5))
    guard bookImgCount > 0 else {
        throw SQLiteError.missingData
    }
    guard let bookImgBlog = sqlite3_column_blob(statement, 5) else {
        throw SQLiteError.column(message: errorMessage)
    }
    let bookImg = Data(bytes: bookImgBlog, count: bookImgCount)

    guard let createdByCString = sqlite3_column_text(statement, 6) else {
        throw SQLiteError.column(message: errorMessage)
    }
    let createdBy = String(cString: createdByCString)

    return Book(id: bookId, image: bookImg, title: bookName, author: bookAuthor, bookDescription: bookDesc, createDate: bookDate, createdBy: createdBy)
}

With these definitions:

struct Book {
    var id: Int?
    var image: Data
    var title: String
    var author: String
    var bookDescription: String  // this is the only one where I kept the `book` prefix, simply because `description` is a reserved name
    var createDate: Date
    var createdBy: String
}

enum SQLiteError: Error {
    case open(result: Int32)
    case exec(message: String)
    case prepare(message: String)
    case bind(message: String)
    case step(message: String)
    case column(message: String)
    case invalidDate
    case missingData
    case noDataChanged
}

While I believe you should check all of the sqlite3_xxx calls for errors, I personally find all of the above a bit ugly. I’d suggest wrapping all the SQLite3 calls to get you out of the weeds. There are lots of them out there. This a thin wrapper simplifies the above code to something like:

func insert(_ book: inout Book) throws {
    let query = "INSERT INTO book (bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy) VALUES (?, ?, ?, ?, ?, ?)"

    let statement = try database.prepare(query, parameters: [
        book.title, book.author, book.bookDescription, book.createDate, book.image, book.createdBy
    ])
    try statement.step()
    book.id = Int(database.lastRowId())
}

func update(_ book: Book) throws {
    let query = "UPDATE Book SET bookName = ?, bookAuthor = ?, bookDesc = ?, bookDate = ?, bookImg = ?, createdBy = ? WHERE bookId = ?"

    let statement = try database.prepare(query, parameters: [
        book.title, book.author, book.bookDescription, book.createDate, book.image, book.createdBy, book.id
    ])
    try statement.step()
}

func delete(_ book: Book) throws {
    let query = "DELETE FROM Book WHERE bookId = ?"
    let statement = try database.prepare(query, parameters: [book.id])
    try statement.step()
}

func select(bookId: Int) throws -> Book? {
    let query = "SELECT bookId, bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy FROM Book WHERE bookId = ?"
    let statement = try database.prepare(query, parameters: [bookId])
    if try statement.step() == .row {
        return book(for: statement)
    } else {
        return nil
    }
}

func selectAll() throws -> [Book] {
    let query = "SELECT bookId, bookName, bookAuthor, bookDesc, bookDate, bookImg, createdBy FROM Book"
    let statement = try database.prepare(query)

    var books = [Book]()
    while try statement.step() == .row {
        if let book = book(for: statement) {
            books.append(book)
        }
    }
    return books
}

func book(for statement: Statement) -> Book? {
    guard
        let id = Int(from: statement, index: 0),
        let title = String(from: statement, index: 1),
        let author = String(from: statement, index: 2),
        let description = String(from: statement, index: 3),
        let date = Date(from: statement, index: 4),
        let data = Data(from: statement, index: 5),
        let createdBy = String(from: statement, index: 6) else {
            return nil
    }

    return Book(id: id, image: data, title: title, author: author, bookDescription: description, createDate: date, createdBy: createdBy)
}

Swift SQLite3 syntax and bind

Tags:

date

sqlite

swift

Jeff

1 Answers

Rob

Recent Activity

Donate For Us

Swift SQLite3 syntax and bind

Tags:

date

sqlite

swift

Jeff

1 Answers

Rob

Related questions

Recent Activity

Donate For Us