swift3 - 插入时最后一列数据覆盖所有列

Question

我在插入数据行时遇到此问题，结果显示“成功”。但是当我打印出表格时，出现了如下所示的内容。我已经尝试了好几天了，但无法弄清楚哪里出了问题。

日志:

Successfully inserted row. >> aaa sss ddd fff
Successfully inserted row. >> zzz xxx ccc vvv
Successfully inserted row. >> 9999999 00000000 aaaaaaa bbbbbbbbbbbbb
Successfully inserted row. >> ccccccccccccc ddddddddddd eeeeeeeeeeee fffffffffffffffff
Successfully inserted row. >> gggggggggggg hhhhhhhhhhhh iiiiiiiiiiiii jjjjjjjjjjjjjj
Successfully inserted row. >> kkkkkkkkkkk LLLLLLLLLL mmmmmmmm nnnnnnnnnnnnn
Query Result= id:1 msgID:fff loginID:fff conversationID:fff userID:fff
Query Result= id:2 msgID:vvv loginID:vvv conversationID:vvv userID:vvv
Query Result= id:3 msgID:bbbbbbb loginID:bbbbbbb conversationID:bbbbbbbb userID:bbbbbbbbbbbbb
Query Result= id:4 msgID:ddddddddddd loginID:ddddddddddd conversationID:ddddddddddd userID:fffffffffffffffff
Query Result= id:5 msgID:jjjjjjjjjjjjj loginID:jjjjjjjjjjjj conversationID:jjjjjjjjjjjj userID:jjjjjjjjjjjjjj
Query Result= id:6 msgID:nnnnnnnn loginID:nnnnnnnnnnn conversationID:nnnnnnnnnn userID:nnnnnnnnnnnnn

我确定数据库连接没问题，但结果就是错误。我什至检查了我的参数是否输入正确。

当我调用我的函数时:

DB_Handler.add_msgstatus(loginID: "aaa", conversationID: "sss", msgID: "ddd", userID: "fff")
DB_Handler.add_msgstatus(loginID: "zzz", conversationID: "xxx", msgID: "ccc", userID: "vvv")
DB_Handler.add_msgstatus(loginID: "9999999", conversationID: "00000000", msgID: "aaaaaaa", userID: "bbbbbbbbbbbbb")
DB_Handler.add_msgstatus(loginID: "ccccccccccccc", conversationID: "ddddddddddd", msgID: "eeeeeeeeeeee", userID: "fffffffffffffffff")
DB_Handler.add_msgstatus(loginID: "gggggggggggg", conversationID: "hhhhhhhhhhhh", msgID: "iiiiiiiiiiiii", userID: "jjjjjjjjjjjjjj")
DB_Handler.add_msgstatus(loginID: "kkkkkkkkkkk", conversationID: "LLLLLLLLLL", msgID: "mmmmmmmm", userID: "nnnnnnnnnnnnn")
DB_Handler.getAllMsgStatusInfo()

我的插入行函数:

static func add_msgstatus(loginID: String, conversationID: String, msgID: String, userID: String) {

    let queryString = "INSERT INTO msgstatus_tbl (id, msg_id, login_id, conversation_id, user_id) VALUES (null, ?, ?, ?, ?);"
    var preparedStmt: OpaquePointer? = nil
    if sqlite3_prepare(db, queryString, -1, &preparedStmt, nil) == SQLITE_OK {
        sqlite3_bind_text(preparedStmt, 1, msgID, -1, nil)
        sqlite3_bind_text(preparedStmt, 2, loginID, -1, nil)
        sqlite3_bind_text(preparedStmt, 3, conversationID, -1, nil)
        sqlite3_bind_text(preparedStmt, 4, userID, -1, nil)

        if sqlite3_step(preparedStmt) == SQLITE_DONE {
            print("Successfully inserted row. >> \(loginID) \(conversationID) \(msgID) \(userID)")
        } else {
            let errorMessage = String.init(cString: sqlite3_errmsg(db))
            print("Fail to insert row. \(errorMessage)")
        }
        sqlite3_finalize(preparedStmt)
    } else {
        let errorMessage = String.init(cString: sqlite3_errmsg(db))
        print("Unable to prepare statement. \(errorMessage)")
    }

}

我的 table :

static func create_msgstatus_tbl() {

    let queryString = "CREATE TABLE IF NOT EXISTS msgstatus_tbl (id INTEGER PRIMARY KEY, msg_id VARCHAR(255), login_id VARCHAR(255), conversation_id VARCHAR(255), user_id VARCHAR(255));"
    var preparedStmt: OpaquePointer? = nil
    if sqlite3_prepare_v2(db, queryString, -1, &preparedStmt, nil) == SQLITE_OK {
        if sqlite3_step(preparedStmt) == SQLITE_DONE {
            print("Table (msgstatus_tbl) exists/ created.")
        } else {
            print("Table (msgstatus_tbl) could not be created.")
        }
        sqlite3_finalize(preparedStmt)
    } else {
        let errorMessage = String.init(cString: sqlite3_errmsg(db))
        print("Unable to prepare statement. \(errorMessage)")
    }

}

我的检索数据功能:

static func getAllMsgStatusInfo() {

    let queryString = "SELECT * FROM msgstatus_tbl;"
    var preparedStmt: OpaquePointer? = nil
    if sqlite3_prepare_v2(db, queryString, -1, &preparedStmt, nil) == SQLITE_OK {
        var msgID = ""
        var loginID = ""
        var conversationID = ""
        var userID = ""
        var id = ""
        while (sqlite3_step(preparedStmt) == SQLITE_ROW) {
            id = String(cString: sqlite3_column_text(preparedStmt, 0))
            msgID = String(cString: sqlite3_column_text(preparedStmt, 1))
            loginID = String(cString: sqlite3_column_text(preparedStmt, 2))
            conversationID = String(cString: sqlite3_column_text(preparedStmt, 3))
            userID = String(cString: sqlite3_column_text(preparedStmt, 4))
            print("Query Result= id:\(id) msgID:\(msgID) loginID:\(loginID) conversationID:\(conversationID) userID:\(userID)")
        }
        sqlite3_finalize(preparedStmt)
    } else {
        let errorMessage = String.init(cString: sqlite3_errmsg(db))
        print("Unable to prepare statement. \(errorMessage)")
    }

}

Answer 1

你需要做一件事。当您使用 sqlite3_bind_text(preparedStmt, 1, msgID, -1, nil) 时，只需按照以下步骤操作 -:

1. let msgId = msgID as NSString

2. sqlite3_bind_text(preparedStmt, 1, msgId.utf8String, -1, nil)

对代码中提到的所有下一个 sqlite3_bind_text 语句执行相同的操作。

我也遇到了和你一样的问题。现在它对我有用。我希望它对您有用。