c++ - sqlite 插入 blob - 绑定(bind)或列索引超出范围

Question

我正在尝试将二进制文件插入到 sqlite 数据库中。我收到“绑定(bind)或列索引超出范围”错误。我不知道为什么会出现该错误，一切似乎都井井有条。我已经用评论标记了错误的位置。我仔细检查了该文件是否存在且大小是否正确。

private void InsertBlob(uint PID, uint MID, int CR, int CG, int CB){
    ifstream file("my_file.bin", ios::in | ios::binary);
    file.seekg(0, ifstream::end);
    streampos size = file.tellg();
    file.seekg(0);

    //read file to buffer
    char* buffer = new char[size];
    file.read(buffer, size);

    //C++ string build, MBinary is blob others are Integers
    string Command = "BEGIN; INSERT INTO Dat (PID,MID,CID_R,CID_G,CID_B,MBinary) VALUES (" + to_string(PID) + "," + to_string(MID) + "," + to_string(CR) + "," + to_string(CG) + "," + to_string(CB) + ",?);COMMIT;";

    //convert string to char
    char c_style_string[1024];
    strncpy(c_style_string, Command.c_str(), sizeof(c_style_string));
    c_style_string[sizeof(c_style_string) - 1] = 0;

    int rc;

    sqlite3_open("AnalysisDatabase.db3", &db);
    sqlite3_stmt *stmt = NULL;
    rc = sqlite3_prepare_v2(db,c_style_string,-1, &stmt, NULL);

    **//following line throws the error: bind or column index out of range**
    rc = sqlite3_bind_blob(stmt, 1, buffer, size, SQLITE_STATIC);

    rc = sqlite3_step(stmt);
    sqlite3_finalize(stmt);
    sqlite3_close(db);

Answer 1

您绑定(bind)到声明 BEGIN，显然没有占位符。您需要使用多个语句。 sqlite3_prepare_v2 的最后一个参数设置指向分号后的下一条语句的指针。

这是您的代码的修改版本:

sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);

char const* const command = "INSERT INTO Dat (PID, MID, CID_R, CID_G, CID_B, MBinary)"
                            " VALUES (?, ?, ?, ?, ?, ?)";

sqlite3_open("AnalysisDatabase.db3", &db);
sqlite3_stmt* stmt = NULL;
int rc = sqlite3_prepare_v2(db, command, -1, &stmt, NULL);

// Note: you're not handling rc. Also, why were you concatenating strings
// instead of binding params, like you were already doing for the blob?
rc = sqlite3_bind_int(stmt, 1, PID);
rc = sqlite3_bind_int(stmt, 2, MID);
rc = sqlite3_bind_int(stmt, 3, CT);
rc = sqlite3_bind_int(stmt, 4, CG);
rc = sqlite3_bind_int(stmt, 5, CB);
rc = sqlite3_bind_blob(stmt, 6, buffer, size, SQLITE_STATIC);

rc = sqlite3_step(stmt);
sqlite3_finalize(stmt);

sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);

编辑:您也没有在原始代码中deleteing buffer。您需要开始使用 RAII 来管理您的资源。