CODEWITHSUNDEEP
swiftsqlite
Magno
Magno

Reputation: 387

Swift 4 + SQLite when inserting

I am using the "raw" C functions to access my SQLite database and getting 2 problems with date and text fields, my code, considere that all is already working:

let queryString = "INSERT INTO LOG (field1, field2, field3, field4) VALUES (?, ?, ?, ?);" 
if sqlitePrepare(database: &db, query: queryString, statement: &insertStatement) == SQLITE_OK {
    sqlite3_bind_int(insertStatement, 1, intVar)
    sqlite3_bind_double(insertStatement, 2, Date().timeIntervalSinceReferenceDate)
    sqlite3_bind_text(insertStatement, 3, strVar1, -1, nil)
    sqlite3_bind_text(insertStatement, 4, strVar2, -1, nil)
    if sqlite3_step(insertStatement) != SQLITE_DONE {
          let errmsg = String(cString: sqlite3_errmsg(db)!)
         print("failure insert: \(errmsg)")
     }
    sqlite3_finalize(insertStatement)
}

Issue #1 is how to populate the Date field as a valid SQLite timestamp? Issue #2 and more important, the values that are updated into the table with the string fields (field3/field4) are wrong. Only the last value is used to fill both (strVar1/strVar2), so if strVar1 = "A" and strVar2 = "B", both field3 and field4 are saved as "B", value of strVar2. This is very weird because all examples I found have no difference here.

Upvotes: 0

Views: 2721

Answers (1)

OOPer
OOPer

Reputation: 47876

#1 is how to populate the Date field as a valid SQLite timestamp?

SQLite3 has no DATETIME type nor TIMESTAMP type. So, you need to store a timestamp as a TEXT or NUMERIC.

When you choose NUMERIC, you should better use UNIX epoch time.

sqlite3_bind_double(insertStatement, 2, Date().timeIntervalSince1970)

#2

Swift allocates a temporary region to store UTF-8 representation when passing a String to a parameter of type UnsafePointer<Int8>. And the region is released immediately after the call to the function, and may be reused.

Try this:

sqlite3_bind_text(insertStatement, 3, strdup(strVar1), -1, free)
sqlite3_bind_text(insertStatement, 4, strdup(strVar2), -1, free)

strdup allocates a stable region which is available until freed.

Or (thanks to rmaddy and Martin R "SQLITE_TRANSIENT undefined in Swift"), you can use SQLITE_TRANSIENT.

Define somewhere in your code:

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

And use it:

sqlite3_bind_text(insertStatement, 3, strVar1, -1, SQLITE_TRANSIENT)
sqlite3_bind_text(insertStatement, 4, strVar2, -1, SQLITE_TRANSIENT)

Upvotes: 2

Related Questions