Swift SQLite last value is being written only

Question

I am writing multiple values to an SQLite database row, however, it is writing only the last value to all the cells in the row. How can I fix this? Code below:

//creating a statement
var insertStatement: OpaquePointer? = nil

//the insert query
let queryString = "INSERT INTO Users (title, firstName, lastName, email, affiliatedOrg, region, privacyRequest) VALUES (?,?,?,?,?,?,?);"

if sqlite3_prepare_v2(db, queryString, -1, &insertStatement, nil) == SQLITE_OK {

    sqlite3_bind_text(insertStatement, 1, titleField, -1, nil)
    sqlite3_bind_text(insertStatement, 2, firstNameField, -1, nil)
    sqlite3_bind_text(insertStatement, 3, lastNameField, -1, nil)
    sqlite3_bind_text(insertStatement, 4, emailField, -1, nil)
    sqlite3_bind_text(insertStatement, 5, affiliatedOrgField, -1, nil)
    sqlite3_bind_text(insertStatement, 6, ukRegionField, -1, nil)
    sqlite3_bind_text(insertStatement, 7, privacyString, -1, nil)

    if sqlite3_step(insertStatement) == SQLITE_DONE {
        print("Successfully inserted row.")
    } else {
        print("Could not insert row.")
    }
} else {
    print("INSERT statement could not be prepared.")
}

sqlite3_finalize(insertStatement)

Many thanks in advance.

Are you saying that only `privacyString` is getting saved? Or are you saying that you're saving multiple rows of data and only the last one is getting saved? — Rob, Apr 13 '18 at 15:27
@user1391152 : It is important to clarify what Rob has asked here. — Nitish, Apr 13 '18 at 15:41
Unrelated, but I'd personally also check the return codes from those `sqlite3_bind_text` calls, to make sure they succeeded. — Rob, Apr 13 '18 at 16:54

Rob · Answer 1 · 2018-04-13T16:54:46.260

2

You should be using SQLITE_TRANSIENT as the last parameter to sqlite3_bind_text as outlined in https://stackoverflow.com/a/28642293/1271826. As the SQLite documentation says:

The SQLITE_TRANSIENT value means that the content will likely change in the near future and that SQLite should make its own private copy of the content before returning.

So, you should:

sqlite3_bind_text(insertStatement, 1, titleField, -1, SQLITE_TRANSIENT)

Where,

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

But, bottom line, SQLITE_TRANSIENT will ensure that SQLite will copy the data and not be dependent upon Swift internal memory management for these bound values.

edited Apr 13 '18 at 16:54

answered Apr 13 '18 at 15:31

Rob

415,655
72
787
1,044

Though you are right, but still, it should be investigated as to why he's getting the last value is getting saved everywhere or for that matter last record is being saved. There must be something wrong in the original code. – Nitish Apr 13 '18 at 15:39
1

@Nitish: Swift creates *temporary* C string representations which are passed to sqlite3_bind_text, and are invalid as soon as the function returns. With SQLITE_STATIC, even a single insert statement could cause wrong results because it is assumed that all passed C strings are valid until the insert statement has finished. – Martin R Apr 13 '18 at 16:37

Swift SQLite last value is being written only

1 Answers1