sqlite for swift is unstable

sqlite swift
swift create sqlite database
how to import sqlite in swift
sqlite swift documentation
update query in sqlite swift
how to use existing sqlite database in swift
insert data in sqlite swift
swiftui sqlite

I've set up swift project to use sqlite. sometimes, when inserting it doesn't actually insert the correct (or all) the values. I know because I restart the app, and when I come back in the entries are either randomly wrong (with stuff not inserted) or nil. but sometimes correct.

here is where I set it, and yes the data is correct in the values before inserting.

let update = "INSERT INTO ToDoItem (itemName, completed, goalDate) " + "VALUES (?, ?, ?);"
var statement: COpaquePointer = nil
if sqlite3_prepare_v2(database, update, -1, &statement, nil) == SQLITE_OK {
    let itemName = item.itemName as String
    let completed = item.completed == true ? 1 : 0

    sqlite3_bind_text(statement, 1, itemName, -1, nil)
    sqlite3_bind_int(statement, 2, Int32(completed))

    if let goalDate = item.goalDate?.toString() {
        sqlite3_bind_text(statement, 3, goalDate, -1, nil)
    } else {
        sqlite3_bind_text(statement, 3, "", -1, nil)
    }

    //println("inserting \(itemName), \(completed) and \(item.goalDate?.toString())")
    //println("")
}

if sqlite3_step(statement) != SQLITE_DONE {
    println("error updateing table")
    sqlite3_close(database)
    return
}
sqlite3_finalize(statement)

sqlite3_close(database)

you can see the commented out println in the middle, if that is not commented out, then the itemName sometimes gets part of that string.

I had the same problem. I found the way to resolve this problem.

sqlite3_bind_text(statement, 1, itemName, -1, nil) --> itemName should be UTF8 String

You should convert itemName as NSString and use UTF8String to convert your string to UTF8. Right code is the same here

let itemName = item.itemName as NSString
sqlite3_bind_text(statement, 1, itemName.UTF8String, -1, nil)

Good luck.

sqlite for swift is unstable, In swift SQLite doesn't have SQLITE_TRANSIENT and SQLITE_STATIC defined, so we need to explicitly define it. swift 3 & 4 Define following property of SQLITE  In this SQLite with Swift tutorial, you’ll learn to use a SQLite database with Swift projects by creating tables and inserting, updating and deleting rows. Update note: Adam Rush updated this tutorial to Xcode 11, iOS 13 and Swift 5. Chris Wagner wrote the original. In software development, it doesn’t take long before you need to persist

In swift SQLite doesn't have SQLITE_TRANSIENT and SQLITE_STATIC defined, so we need to explicitly define it.

swift 3 & 4

Define following property of SQLITE

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

SQL function Add SQLITE_TRANSIENT instead of nil as last parameter while binding text sqlite3_bind_text.

let update = "INSERT INTO ToDoItem (itemName, completed, goalDate) " + "VALUES (?, ?, ?);"
var statement: OpaquePointer = nil
if sqlite3_prepare_v2(database, update, -1, &statement, nil) == SQLITE_OK {
    let itemName = item.itemName as String
    let completed = item.completed == true ? 1 : 0

    sqlite3_bind_text(statement, 1, itemName, -1, SQLITE_TRANSIENT)
    sqlite3_bind_int(statement, 2, Int32(completed))

    if let goalDate = item.goalDate?.toString() {
        sqlite3_bind_text(statement, 3, goalDate, -1, SQLITE_TRANSIENT)
    } else {
        sqlite3_bind_text(statement, 3, "", -1, SQLITE_TRANSIENT)
    }

    //println("inserting \(itemName), \(completed) and \(item.goalDate?.toString())")
    //println("")
}

if sqlite3_step(statement) != SQLITE_DONE {
    println("error updateing table")
    sqlite3_close(database)
    return
}
sqlite3_finalize(statement)

sqlite3_close(database)

Referenced from SQLITE_TRANSIENT undefined in Swift

Issues · stephencelis/SQLite.swift · GitHub, over SQLite3. Contribute to stephencelis/SQLite.swift development by creating an account on GitHub. bind dictionary params will crash. #978 opened on  “They go down in value like a yoyo, they’re useless and unstable. And even if crypto companies do make it stable, it’s still a basket of currencies,” the SWIFT spokesman said. The only cryptocurrency SWIFT said has promise, unsurprisingly, is Facebook’s forthcoming Libra. For the rest of the industry, the network “doesn’t see it

This behavior actually does conform to the specification and the bug is in your code.

The source of your problem is in the swift String you are passing to sqlite3_bind_text. sqlite3_bind_text accepts the text as a const char* which is bridged as UnsafePointer<CChar> in Swift. The behavior when passing a Swift String to a function that accepts UnsafePointer<CChar> is documented in the "Constant Pointers" section of the Interacting with C APIs. It says:

The string will automatically be converted to UTF8 in a buffer, and a pointer to that buffer is passed to the function.

Which is probably what you want to have happen.

BUT it also says:

The pointer passed to the function is guaranteed to be valid only for the duration of the function call. Don’t try to persist the pointer and access it after the function has returned.

This is the source of your bug. sqlite3_bind_text() does in fact persist the pointer in the prepared statement possibly until sqlite3_finalize() is called so that it can use it in future sqlite3_step() calls.

Another answer suggested using NSString.UTF8String. This has a lifetime that is a little longer and seems like it should be enough. The documentation says:

This C string is a pointer to a structure inside the string object, which may have a lifetime shorter than the string object and will certainly not have a longer lifetime. Therefore, you should copy the C string if it needs to be stored outside of the memory context in which you use this property.

"memory context" here seems vague. I'm not sure if it means the current function, or until the current autoreleasepool is drained, or something else. If it is one of the first two, you are safe. If not, well I would say you are still safe because it seems like NSString.UTF8String has been used in situations like these for a long time without issues..

stephencelis/SQLite.swift, Build Information SQLite iOS Version 0.11.4 (however it's likely to App crash in Xcode 10.2 after migrating project swift 3 -> swift 4.2 #895. SQLite is written in C programming language. So if we want to use SQLite in our application we might have to work with complicated C functions, data types and pointers. (But here, in this post I am going through easier approach; I am using already available SQLite wrapper [FMDB] interface between SQLite database and my swift application).

stephencelis/SQLite.swift, I'm new to SQLite.swift. I have been From what I can tell, SQLite.swift just uses Connection. Correct? I'm seeing a crash on iOS 11.4 when i access the value. Let's start with how you use the version of SQLite that included with Windows. To use the version of SQLite that is installed with Windows. Choose the Browse tab, and search for the Microsoft.Data.SQLite.core package, and then install it. Search for the SQLitePCLRaw.bundle_winsqlite3 package, and then install it only to the UWP project in your

Core Data: How to Preload Data and Use Existing SQLite Database, First post to the SQLite.swift gitter chat room Gerard I want to achieve below things in SQLite.swift I'm seeing a crash on iOS 11.4 when i access the value. Swift SQLite Tutorial Source Code. So that’s, all of this Swift SQLite Tutorial friends. Hope you found it helpful. If you are having any question regarding this Swift SQLite tutorial, then let me know in comments. Also if you found this Swift SQLite Tutorial helpful then please SHARE it with friends. Thank You 🙂

Working with SQLite Databases in iOS with FMDB Library, This Swift tutorial shows you how to preload data in Core Data. You will also learn how to use an existing SQLite database in Core Data by building a abort​() causes the application to generate a crash log and terminate. stephencelis / SQLite.swift. Code Issues 220 Pull requests 33 Actions Projects 0 Wiki Security Insights. Join GitHub today. GitHub is home to over 40 million developers working together to host and review code, manage projects, and build software together. A type-safe, Swift-language layer over SQLite3. 57 contributors.

Comments
  • You also have to use SQLITE_TRANSIENT for the last parameter to sqlite3_bind_text.
  • SQLITE_TRANSIENT is not available swift 3? can you please advise.
  • @SaurabhPrajapati I found this answer that covers manually defining SQLITE_TRANSIENT in Swift 3: stackoverflow.com/a/26884081/470339