SqlCipher数据插入遇到乱码及列数据错位问题的解决方案

于 2024-11-14 19:13:42 发布
阅读量255 收藏
点赞数 2
文章标签： qt
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_28426719/article/details/143778454
版权

一、问题

       在Qt开发环境下使用SqLite3的API进行数据插入时遇到了数据乱码及列数据错位的问题，经排查发现使用QString定义变量然后使用"sqlite3_bind_text"进行数据绑定时，会导致乱码及列数据错位。

二、解决方案

     将QString改为string类型后，问题不再复现。

三、示例代码

class DatabaseManager {
public:
    DatabaseManager() {
        const char *file= "C:\\ProgramData\\yjkSoft\\local_license.db";
        const char *key1 = "123456789";
        char* key = (char *) key1;
        if (sqlite3_open(file, &db) == SQLITE_OK) {
            if(db == NULL) {
                return;
            }
            if(sqlite3_key(db, key, strlen(key)) != SQLITE_OK) {
                return;
            }
            sqlite3_exec(db, "PRAGMA encoding = 'UTF-8';", nullptr, nullptr, nullptr);
        }
    }

    ~DatabaseManager() {
        if (db!=NULL) {
            sqlite3_close(db);
        }
    }

    // 插入sninfo数据  删除+插入=更新
    bool insertData(const SnInfoStru &SnInfo) {
        /*删除已有授权数据*/
        if(!deleteData(SnInfo.sn,"licenses")) return false;
        /*插入授权数据*/
        sqlite3_stmt *stmt;
        std::string insertQuery = "INSERT INTO licenses (product_id,sn,customers_count,peaks_count,failures_count,status,type,checks,days,move_times,has_moved,borrow,start_time,end_time,active_time,expire_time,check_time,is_handsel,is_try) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        int result = sqlite3_prepare_v2(db, insertQuery.c_str(), -1, &stmt, nullptr);
        if (result != SQLITE_OK) {
            std::cerr << "Failed to prepare insert statement: " << sqlite3_errmsg(db) << std::endl;
            return false;
        }
        sqlite3_bind_int(stmt, 1, SnInfo.product_id);
        sqlite3_bind_text(stmt, 2, SnInfo.sn.c_str(), SnInfo.sn.length(), SQLITE_STATIC);
        sqlite3_bind_int(stmt, 3, SnInfo.customers_count);
        sqlite3_bind_int(stmt, 4, SnInfo.peaks_count);
        sqlite3_bind_int(stmt, 5, SnInfo.failures_count);
        sqlite3_bind_int(stmt, 6, SnInfo.status);
        sqlite3_bind_int(stmt, 7, SnInfo.type);
        sqlite3_bind_int(stmt, 8, SnInfo.checks);
        sqlite3_bind_int(stmt, 9, SnInfo.days);
        sqlite3_bind_int(stmt, 10, SnInfo.move_times);
        sqlite3_bind_int(stmt, 11, SnInfo.has_moved);
        sqlite3_bind_int(stmt, 12, SnInfo.borrow);
        sqlite3_bind_text(stmt, 13, SnInfo.start_time.c_str(), -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 14, SnInfo.end_time.c_str(), -1, SQLITE_STATIC);
        sqlite3_bind_text(stmt, 15, SnInfo.active_time.c_str(), -1, SQLITE_STATIC);
        sqlite3_bind_int(stmt, 16, SnInfo.expire_time);
        sqlite3_bind_int(stmt, 17, SnInfo.check_time);
        sqlite3_bind_int(stmt, 18, SnInfo.is_handsel);
        sqlite3_bind_int(stmt, 19, SnInfo.is_try);
        result = sqlite3_step(stmt);
        sqlite3_finalize(stmt);
        if (result != SQLITE_DONE){
            std::cerr << "Insert data failed: " << sqlite3_errmsg(db) << std::endl;
            return false;
        }
        return true;
    }
    // 插入featureinfo数据
    bool insertData(const vector<FeatureInfoStru> &FeatureInfo) {
        if(FeatureInfo.size())
        {
            /*删除已有授权数据*/
            if(!deleteData(FeatureInfo[0].sn,"modules")) return false;
            /*插入授权数据*/
            sqlite3_stmt *stmt;
            std::string insertQuery = "INSERT INTO modules (uuid,product_id,map,sn,modules_name,version,num,vid,content,peaks_count,status,parent_id,level,item_id,remark,start,end,days,cn_name) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            int result = sqlite3_prepare_v2(db, insertQuery.c_str(), -1, &stmt, nullptr);
            if (result != SQLITE_OK) {
                std::cerr << "Failed to prepare insert statement: " << sqlite3_errmsg(db) << std::endl;
                return false;
            }
            result = sqlite3_exec(db, "BEGIN TRANSACTION;", nullptr, nullptr, nullptr);
            if (result != SQLITE_OK) {
                qDebug() << "Failed to begin transaction:" << sqlite3_errmsg(db);
                sqlite3_finalize(stmt);
                return false;
            }
            foreach(FeatureInfoStru temp,FeatureInfo)
            {
                uint nCol = 1;
                sqlite3_bind_text(stmt, nCol++, temp.uuid.c_str(), temp.uuid.length(), SQLITE_STATIC);
                sqlite3_bind_int(stmt,   nCol++, temp.product_id);
                sqlite3_bind_text(stmt, nCol++, temp.map.c_str(), temp.map.length(), SQLITE_STATIC);
                sqlite3_bind_text(stmt, nCol++, temp.sn.c_str(), temp.sn.length(), SQLITE_STATIC);
                sqlite3_bind_text(stmt, nCol++, temp.modules_name.c_str(), temp.modules_name.length(), SQLITE_STATIC);
                sqlite3_bind_text(stmt, nCol++, temp.version.c_str(), temp.version.length(), SQLITE_STATIC);
                sqlite3_bind_int(stmt,   nCol++,   temp.num);
                sqlite3_bind_int(stmt,   nCol++,   temp.vid);
                sqlite3_bind_text(stmt, nCol++, temp.content.c_str(), temp.content.length(), SQLITE_STATIC);
                sqlite3_bind_int(stmt, nCol++, temp.peaks_count);
                sqlite3_bind_int(stmt, nCol++, temp.status);
                sqlite3_bind_int(stmt, nCol++, temp.parent_id);
                sqlite3_bind_int(stmt, nCol++, temp.level);
                sqlite3_bind_int(stmt, nCol++, temp.item_id);
                sqlite3_bind_text(stmt, nCol++, temp.remark.c_str(), temp.remark.length(), SQLITE_STATIC);
                sqlite3_bind_text(stmt, nCol++, temp.start.c_str(), temp.start.length(), SQLITE_STATIC);
                sqlite3_bind_text(stmt, nCol++, temp.end.c_str(), temp.end.length(), SQLITE_STATIC);
                sqlite3_bind_text(stmt, nCol++, temp.days.c_str(), temp.days.length(), SQLITE_STATIC);
                sqlite3_bind_text(stmt, nCol++, temp.cn_name.c_str(), temp.cn_name.length(), SQLITE_STATIC);
                result = sqlite3_step(stmt);
                if (result != SQLITE_DONE){
                    std::cerr << "Insert data failed: " << sqlite3_errmsg(db) << std::endl;
                    return false;
                }
                sqlite3_reset(stmt);
            }
            // 提交事务
            result = sqlite3_exec(db, "COMMIT;", nullptr, nullptr, nullptr);
            if (result != SQLITE_OK) {
                qDebug() << "Failed to commit transaction:" << sqlite3_errmsg(db);
                sqlite3_finalize(stmt);
                return false;
            }
            sqlite3_finalize(stmt);
        }
        return true;
    }

    // 查询数据
    bool queryData(vector<SnInfoStru> &SnInfo) {
        std::string selectQuery = "SELECT * FROM licenses;";
        sqlite3_stmt *stmt;
        int result = sqlite3_prepare_v2(db, selectQuery.c_str(), -1, &stmt, nullptr);
        if (result != SQLITE_OK) {
            std::cerr << "Failed to prepare query statement: " << sqlite3_errmsg(db) << std::endl;
            return false;
        }
        while ((result = sqlite3_step(stmt)) == SQLITE_ROW) {
            SnInfoStru temp;
            temp.product_id              = sqlite3_column_int(stmt, 1);
            temp.sn                           = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 2));
            temp.customers_count    = sqlite3_column_int(stmt, 3);
            temp.peaks_count          = sqlite3_column_int(stmt, 4);
            temp.failures_count        = sqlite3_column_int(stmt, 5);
            temp.status                    = sqlite3_column_int(stmt, 6);
            temp.type                       = sqlite3_column_int(stmt, 7);
            temp.checks                   = sqlite3_column_int(stmt, 8);
            temp.days                      = sqlite3_column_int(stmt, 9);
            temp.move_times           = sqlite3_column_int(stmt, 10);
            temp.has_moved            = sqlite3_column_int(stmt, 11);
            temp.borrow                   = sqlite3_column_int(stmt, 12);
            temp.start_time              = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 13));
            temp.end_time               = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 14));
            temp.active_time            = reinterpret_cast<const char *>(sqlite3_column_text(stmt, 15));
            temp.expire_time           = sqlite3_column_int(stmt, 16);
            temp.check_time           = sqlite3_column_int(stmt, 17);
            temp.is_handsel            = sqlite3_column_int(stmt, 18);
            temp.is_try                     = sqlite3_column_int(stmt, 19);
            SnInfo.push_back(temp);
        }
        if (result != SQLITE_DONE) {
            std::cerr << "Query failed: " << sqlite3_errmsg(db) << std::endl;
            sqlite3_finalize(stmt);
            return false;
        }
        sqlite3_finalize(stmt);
        return true;
    }

    // 删除数据
    bool deleteData(string sn,string TableNameStr) {
        std::string deleteQuery = "DELETE FROM " + TableNameStr + " WHERE sn = ?";
        sqlite3_stmt *stmt;
        int result = sqlite3_prepare_v2(db, deleteQuery.c_str(), -1, &stmt, nullptr);
        if (result != SQLITE_OK) {
            std::cerr << "Failed to prepare delete statement: " << sqlite3_errmsg(db) << std::endl;
            return false;
        }
        sqlite3_bind_text(stmt, 1, sn.c_str(), -1, SQLITE_STATIC);
        result = sqlite3_step(stmt);
        sqlite3_finalize(stmt);
        if (result != SQLITE_DONE) {
            std::cerr << "Delete data failed: " << sqlite3_errmsg(db) << std::endl;
            return false;
        }
    }

private:
    sqlite3 *db;
};

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值