swift - 数据被插入到 SQLITE3 的错误列中

Question

我正在尝试将用户输入的数据插入到我设置的 SQLite 数据库中。这是我目前编写的代码:

var stmt: OpaquePointer?
let queryString = "INSERT INTO entries (id, name, email, avatar) VALUES (NULL,?,?,?)"

if(sqlite3_prepare_v2(db, queryString, -1, &stmt, nil) == SQLITE_OK){
        sqlite3_bind_text(stmt, 1, strName, -1, nil)
        sqlite3_bind_text(stmt, 2, strEmail, -1, nil)
        sqlite3_bind_text(stmt, 3, "batman", -1, nil)
    }

else{
        let errMsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing insert: \(errMsg)")
        return
}

正在从文本字段中提取 strName 和 strEmail。当我在另一个 View 中查询此数据时，由于某种原因，“ bat 侠”已插入第二列而不是用户输入。这是选择查询:

let queryString = "SELECT * FROM entries"
var stmt: OpaquePointer?

if sqlite3_prepare_v2(db, queryString, -1, &stmt, nil) != SQLITE_OK{
        let errmsg = String(cString: sqlite3_errmsg(db)!)
        print("error preparing select: \(errmsg)")
        return
}

while(sqlite3_step(stmt) == SQLITE_ROW){
        let id = sqlite3_column_int(stmt, 0)
        let name = String(cString: sqlite3_column_text(stmt, 1))
        let email = String(cString: sqlite3_column_text(stmt, 2))

        userList.append(User(id: Int(id), name: String(describing: name), email: String(describing: email)))
        print (id);
        print (name);
        print (email);
}

我希望从输出中看到的是:

1. 1
2. 用户名
3. 用户邮箱

我看到的是:

1. 1
2. bat 侠
3. 用户邮箱

我的插入语句有问题吗？

Answer 1

找到答案:

sqlite3_bind_text 存在问题，导致将不正确的数据插入到错误的行中。

更改了绑定(bind)语句:

sqlite3_bind_text(stmt, 1, strName, -1, nil)
sqlite3_bind_text(stmt, 2, strEmail, -1, nil)
sqlite3_bind_text(stmt, 3, "batman", -1, nil)

到:

sqlite3_bind_text(stmt, 1, strName, -1, SQLITE_TRANSIENT)
sqlite3_bind_text(stmt, 2, strEmail, -1, SQLITE_TRANSIENT)
sqlite3_bind_text(stmt, 3, "batman", -1, SQLITE_TRANSIENT)

SQLITE_TRANSIENT 定义为:

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

数据现在可以在查询时正确返回。