Ian Newson Ian Newson - 3 days ago 6
Swift Question

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"

Answer

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)")
}
Comments