Swift Sqlite SQL error

Question

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"

Use `sqlite3_errmsg()` to print the error reason. – Note that you can pass a Swift String directly to C functions, e.g. `sqlite3_open(dbPath, &db)` — Martin R, Nov 30 '16 at 12:47
@MartinR The error message is "near \"\u{01}\": syntax error" — Ian Newson, Nov 30 '16 at 12:49
Duplicate of http://stackoverflow.com/questions/34135305/nsfilemanager-defaultmanager-fileexistsatpath-returns-false-instead-of-true ? – Don't use `absoluteString` to get a file path. — Martin R, Nov 30 '16 at 12:49
@MartinR I've changed my code to use `path` instead of `absoluteString` and it still fails at the same point. The error message changes though, to "near \"" — Ian Newson, Nov 30 '16 at 12:51
Have you tried to remove the unnecessary `.cString(using: .utf8)` stuff? — Martin R, Nov 30 '16 at 12:52
@MartinR Looks like that was the cause, thanks! Post as an answer and I'll accept. — Ian Newson, Nov 30 '16 at 12:57

score 2 · Accepted Answer · edited May 23 '17 at 12:01

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

Swift Sqlite SQL error

1 Answers1