CODEWITHSUNDEEP
swiftsqlite
Ian Newson
Ian Newson

Reputation: 7949

Swift Sqlite SQL error

I'm attempting to create a simple sqlite database in Swift, but I'm getting an error (specifically SQLITE_ERROR) when attempting to create a table.

Here is my code:

        var db :OpaquePointer?
        let dbPath = try FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
            .appendingPathComponent("\(Date.init().timeIntervalSince1970)".replacingOccurrences(of: ".", with: "") + ".db")
            .absoluteString

        var returnCode :Int32 = sqlite3_open(dbPath.cString(using: .utf8), &db)
        if SQLITE_OK != returnCode {
            preconditionFailure("Failed to open db")
        }

        var stmt :OpaquePointer?
        returnCode = sqlite3_prepare_v2(db, "CREATE TABLE Things (name TEXT)".cString(using: .utf8), -1, &stmt, nil)
        if SQLITE_OK != returnCode {
            preconditionFailure("Failed to prepare table creation SQL")
        }

Sqlite is included via a Cocoapod. I have tried using different encodings of the string when converting to a C string, specifically I've tried using ASCII encoding, and I've also tried hard coding the database name.

The error occurs in sqlite3_prepare_v2.

The error message is "near \"\u{01}\": syntax error"

Upvotes: 2

Views: 2333

Answers (1)

Martin R
Martin R

Reputation: 539695

I am not 100% sure why your .cString(using: .utf8) approach to convert a Swift string to a C string causes problems. It could be the same issue as in Why does Swift return an unexpected pointer when converting an optional String into an UnsafePointer? (which was reported as a Swift bug). Unwrapping the result of cString() explicitly seems to help:

let sql = "CREATE TABLE Things (name TEXT)".cString(using: .utf8)!
returnCode = sqlite3_prepare_v2(db, sql, -1, &stmt, nil)

But you can pass a Swift String directly to C functions expecting a const char * (compare String value to UnsafePointer<UInt8> function parameter behavior):

var returnCode = sqlite3_open(dbPath, &db)
// ...
returnCode = sqlite3_prepare_v2(db, "CREATE TABLE Things (name TEXT)", -1, &stmt, nil)

and this works as expected.

Additional remarks:

  • Use .path to convert a URL to a file path string, not .absoluteString.
  • Use sqlite3_errmsg() to get error messages if something failed.
  • Remove unnecessary type annotations, as in var returnCode :Int32.

Putting it all together:

var db: OpaquePointer?
let dbPath = try FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
    .appendingPathComponent("xxx.db")
    .path

var returnCode = sqlite3_open(dbPath, &db)
if SQLITE_OK != returnCode {
    let errmsg = String(cString: sqlite3_errmsg(db))
    fatalError("Failed to open db: \(errmsg)")
}

var stmt: OpaquePointer?
returnCode = sqlite3_prepare_v2(db, "CREATE TABLE Things (name TEXT)", -1, &stmt, nil)
if SQLITE_OK != returnCode {
    let errmsg = String(cString: sqlite3_errmsg(db))
    fatalError("Failed to prepare table creation SQL: \(errmsg)")
}

Upvotes: 3

Related Questions