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
– Martin R, Commented Nov 30, 2016 at 12:47
@MartinR The error message is "near \"\u{01}\": syntax error" — Ian Newson
– Ian Newson, Commented Nov 30, 2016 at 12:49
Duplicate of stackoverflow.com/questions/34135305/… ? – Don't use absoluteString to get a file path. — Martin R
– Martin R, Commented Nov 30, 2016 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
– Ian Newson, Commented Nov 30, 2016 at 12:51
Have you tried to remove the unnecessary .cString(using: .utf8) stuff? — Martin R
– Martin R, Commented Nov 30, 2016 at 12:52

Community · Accepted Answer · 2017-05-23 12:01:46Z

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

Collectives™ on Stack Overflow

Swift Sqlite SQL error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related