sqlite数据库添加读取记录，包含图片

sqlite数据库添加记录，包含图片

#include <sqlite3.h>
#include <iostream>
#include <vector>
#include <fstream>

// 读取文件中的二进制数据
std::vector<char> readBinaryFile(const std::string& filePath) {
    std::ifstream file(filePath, std::ios::binary | std::ios::ate);
    if (!file.is_open()) {
        std::cerr << "Error opening file: " << filePath << std::endl;
        return {};
    }

    std::streamsize size = file.tellg();
    file.seekg(0, std::ios::beg);

    std::vector<char> buffer(size);
    if (file.read(buffer.data(), size)) {
        return buffer;
    } else {
        std::cerr << "Error reading file: " << filePath << std::endl;
        return {};
    }
}

    struct Info{
        int             id;  //read unsed
        std::string strVal1;
        uint32_t    intVal2;
        float       floatVal3;
        std::string imgVal4;
    };

int main() {
    sqlite3* db;
    int result = sqlite3_open("your_database.db", &db);
    if (result != SQLITE_OK) {
        std::cerr << "Error opening database: " << sqlite3_errmsg(db) << std::endl;
        return result;
    }

    Info info = {"pngName", 20, 10.12, ""};
    std::vector<char> imageData = readBinaryFile("your_image.png");

    // 使用预处理语句插入数据
    const char* insertQuery = "INSERT INTO History (strVal1, intVal2, floatVal3, imgVal4 ) "
                              "VALUES (?, ?, ?, ?)";

    sqlite3_stmt* stmt;
    result = sqlite3_prepare_v2(db, insertQuery, -1, &stmt, nullptr);
    if (result != SQLITE_OK) {
        std::cerr << "Error preparing statement: " << sqlite3_errmsg(db) << std::endl;
        return result;
    }

    // 绑定参数
    sqlite3_bind_text(stmt, 1, info.strVal1.c_str(), -1, SQLITE_STATIC);
    sqlite3_bind_int(stmt, 2, info.intVal2);
    sqlite3_bind_double(stmt, 3, info.floatVal3);
    sqlite3_bind_blob(stmt, 4, imageData.data(), imageData.size(), SQLITE_STATIC);

    // 执行语句
    result = sqlite3_step(stmt);
    if (result != SQLITE_DONE) {
        std::cerr << "Error executing statement: " << sqlite3_errmsg(db) << std::endl;
        return result;
    }

    // 释放资源
    sqlite3_finalize(stmt);
    sqlite3_close(db);

    std::cout << "Data inserted successfully." << std::endl;

    return 0;
}

sqlite数据库读取记录，包含图片

int ReadData(std::list<Info> &infoList)
{
    sqlite3* _sqldb;
    int result = sqlite3_open("your_database.db", &_sqldb);
    if (result != SQLITE_OK) {
        std::cerr << "Error opening database: " << sqlite3_errmsg(_sqldb) << std::endl;
        return result;
    }

    const char *sql = "SELECT * FROM PrintHistory;";
    sqlite3_stmt *stmt;

    int rc = sqlite3_prepare_v2(_sqldb, sql, -1, &stmt, 0);

    if (rc != SQLITE_OK) {
        LOG_ERROR("Failed to prepare statement: %s", sqlite3_errmsg(_sqldb));
        return -2;
    }

    infoList.clear();
    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        Info history;
        int index = 0;
        int id = sqlite3_column_int(stmt, index++);
        history.id = id;

        char *strVal1 = (char*)sqlite3_column_text(stmt, index++);
        history.strVal1 = strVal1;

        int intVal2 = sqlite3_column_int(stmt, index++);
        history.intVal2 = intVal2;

        float floatVal3 = sqlite3_column_double(stmt, index++);
        history.floatVal3 = floatVal3;

        int size = sqlite3_column_bytes(stmt, index);
        const void *filePng = sqlite3_column_blob(stmt, index++);
        history.imgVal4.assign(static_cast<const char*>(filePng), static_cast<const char*>(filePng) + size);

        infoList.push_back(history);
    }

    if (rc != SQLITE_DONE) {
        LOG_ERROR("Failed to fetch data: %s\n", sqlite3_errmsg(_sqldb));
    }

    sqlite3_finalize(stmt);

    return 0;
}