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;
}