c++ - SQLite3 带参数的多重插入；只插入一条记录

Question

我试图通过重新使用准备好的语句、使用事务和参数化查询来提高我的保存代码的效率。但是，每当我运行保存代码时，只有一条记录最终出现在我的表中。我认为这与 sqlite3_step 的放置位置有关。

但我也希望其他人能检查我的代码，让我知道我做的事情是否正确；我在任何地方都找不到这样做的例子，所以它是我在各种主题上找到的大约六个论坛帖子和 Stack Overflow 主题的困惑:

//Planet Data

//Delete previous save data
dData("Generated_Planets", bErrors);

if (plData.size() > 0)
{
    sqlite3_exec(dBase, "BEGIN TRANSACTION", NULL, NULL, &error);

    sqlStr = "Insert Into Generated_Planets (ID, Name, Affiliation, Disposition, Race, Player_Owned, Is_Destroyed, EKS, Planet_Size, Current_Pop, Max_Pop) Values (?,?,?,?,?,?,?,?,?,?,?);";

    if (sqlite3_prepare_v2(dBase, sqlStr.c_str(), sqlStr.size(), &statement, 0) == SQLITE_OK)
    {
        cout << "Saving planet data";
        //Save new data
        for (i = 0; i <= plData.size(); i++)
        {
            if (i == plData.size())
            {
                finalize(statement, bErrors);
            }

            else
            {
                sqlI1 = plData.at(i).pID;
                sqlS1 = plData.at(i).pName;
                sqlS2 = plData.at(i).pAffiliation;
                sqlS3 = plData.at(i).pDispo;
                sqlS4 = plData.at(i).pRace;
                sqlI2 = plData.at(i).bIsPOwned;
                sqlI3 = plData.at(i).bIsDestroyed;
                sqlF1 = plData.at(i).pEKS;
                sqlF2 = plData.at(i).pSize;
                sqlLLI1 = plData.at(i).pCPop;
                sqlLLI2 = plData.at(i).pMPop;

                find = "'";
                temp = "\"";

                foundAt = sqlS1.find(find);

                if (foundAt != string::npos)
                {
                    sqlS1.replace(foundAt,1,temp);
                }

                //Bind parameters
                sqlite3_bind_int(statement,1,sqlI1);
                sqlite3_bind_text(statement,2,sqlS1.c_str(),sqlS1.size(),SQLITE_TRANSIENT);
                sqlite3_bind_text(statement,3,sqlS2.c_str(),sqlS2.size(),SQLITE_TRANSIENT);
                sqlite3_bind_text(statement,4,sqlS3.c_str(),sqlS3.size(),SQLITE_TRANSIENT);
                sqlite3_bind_text(statement,5,sqlS4.c_str(),sqlS4.size(),SQLITE_TRANSIENT);
                sqlite3_bind_int(statement,6,sqlI2);
                sqlite3_bind_int(statement,7,sqlI3);
                sqlite3_bind_double(statement,8,sqlF1);
                sqlite3_bind_double(statement,9,sqlF2);
                sqlite3_bind_int64(statement,10,sqlLLI1);
                sqlite3_bind_int64(statement,11,sqlLLI2);

                sqlite3_step(statement);

                cout << ".";
            }
        }
    }       

    else
    {
        *bErrors = true;
        createBInfo();      
        d.createBReport("SQL Code 2",sqlite3_errmsg(dBase),bLocale + to_string(__LINE__),bTDate,"./SC_Log.txt");    
    }

    sqlite3_exec(dBase, "END TRANSACTION", NULL, NULL, &error);

    sFlags_Temp.push_back(saveFlag());
    sFlags_Temp.at(sFlags_Temp.size()-1).sfName = "GPlanets";
    sFlags_Temp.at(sFlags_Temp.size()-1).sfValue = 1;
    cout << "Done" << endl << endl;
}

Answer 1

看起来您在 sqlite3_step 之后和绑定(bind)下一组参数之前缺少对 sqlite3_reset 的调用。而且您忽略了本应告诉您的返回码。

请在此处查看 sqlite3_step 的文档:http://sqlite.org/c3ref/step.html