How does binding parameters work in SQLite3 (with

问题:

Somebody recommended to use parameter binding in SQLite in order to speedup recurring queries. However, it does not work if I have multiple parameters for binding. I don't see my error. All SQLite functions return with SQLITE_OK...

Below, I have written a minimal example. It creates a table, makes three entries and then queries two times, one time with one bind parameter and one time with two. The first time returns with one result, which is correct. The second one returns with zero results and sqlite3_step() returns with 101 (SQLITE_DONE). Why does it not return one result also?

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

int main(int argc, char * argv[])
{
  sqlite3 * pDB;
  int iReturn = sqlite3_open("./Test.db", &pDB);
  if (iReturn != SQLITE_OK) {
    return 1;
  }

  // create table
  {
    std::string str = "CREATE TABLE Names(ID INTEGER PRIMARY KEY, Name VARCHAR(30))";
    iReturn = sqlite3_exec(pDB, str.c_str(), NULL, NULL, NULL);
    if (iReturn != SQLITE_OK) {
      sqlite3_close(pDB);
      return 1;
    }
  }

  // insert data using binding
  {
    std::string str = "INSERT INTO Names(Name) VALUES(?)";
    sqlite3_stmt * pStmt = nullptr;

    iReturn = sqlite3_prepare_v2(pDB, str.c_str(), str.size() + 1, &pStmt, nullptr);
    if (iReturn != SQLITE_OK) {
      sqlite3_close(pDB);
      return 1;
    }

    printf("The statement %s has %d parameter(s).\n", str.c_str(), sqlite3_bind_parameter_count(pStmt));

    std::vector<std::string> vecNames;
    vecNames.push_back("Smith");
    vecNames.push_back("Morpheus");
    vecNames.push_back("Neo");

    for (unsigned int i = 0, iEnd = vecNames.size(); i != iEnd; ++i)
    {
      iReturn = sqlite3_bind_text(pStmt, 1, vecNames[i].c_str(), vecNames[i].size() + 1, nullptr);
      if (iReturn != SQLITE_OK) {
        return 1;
      }

      if (sqlite3_step(pStmt) != SQLITE_DONE) {
        sqlite3_finalize(pStmt);
        sqlite3_close(pDB);
        return 1;
      }

      sqlite3_reset(pStmt);
      sqlite3_clear_bindings(pStmt);
    }

  }

  // query using one bind parameter
  {
    sqlite3_stmt * pStmt = nullptr;
    string str = "SELECT ID FROM Names WHERE Name=?1";
    iReturn = sqlite3_prepare_v2(pDB, str.c_str(), str.size() + 1, &pStmt, nullptr);
    if (iReturn != SQLITE_OK) {
      return 1;
    }

    printf("The statement %s has %d parameters(s).\n", str.c_str(), sqlite3_bind_parameter_count(pStmt));

    // fourth parameter is length = position of \0
    iReturn = sqlite3_bind_text(pStmt, 1, "Neo", 3, NULL);
    if (iReturn != SQLITE_OK) {
      return 1;
    }

    vector<string> vecResults;
    char cBuffer[1024];
    string strBuffer;
    while (sqlite3_step(pStmt) == SQLITE_ROW)
    {
      sprintf(cBuffer, "%s", sqlite3_column_text(pStmt, 0));
      strBuffer = cBuffer;
      vecResults.push_back(strBuffer);
    }

    sqlite3_finalize(pStmt);

    printf("Found %d results.\n", vecResults.size());
    for (unsigned int i = 0, iEnd = vecResults.size(); i != iEnd; ++i)
    {
      printf("%d: %s\n", i, vecResults[i].c_str());
    }
  }

  // query using two bind parameters
  {
    sqlite3_stmt * pStmt = nullptr;
    string str = "SELECT ID FROM Names WHERE Name=?1 AND ID=?2";
    iReturn = sqlite3_prepare_v2(pDB, str.c_str(), str.size() + 1, &pStmt, nullptr);
    if (iReturn != SQLITE_OK) {
      return 1;
    }

    printf("The statement %s has %d parameters(s).\n", str.c_str(), sqlite3_bind_parameter_count(pStmt));

    // fourth parameter is length = position of \0
    iReturn = sqlite3_bind_text(pStmt, 1, "Neo", 3, NULL);
    if (iReturn != SQLITE_OK) {
      return 1;
    }


    iReturn = sqlite3_bind_text(pStmt, 2, "3", 2, NULL);
    if (iReturn != SQLITE_OK) {
      return 1;
    }


    vector<string> vecResults;
    char cBuffer[1024];
    string strBuffer;
    while (sqlite3_step(pStmt) == SQLITE_ROW)
    {
      sprintf(cBuffer, "%s", sqlite3_column_text(pStmt, 0));
      strBuffer = cBuffer;
      vecResults.push_back(strBuffer);
    }

    sqlite3_finalize(pStmt);

    printf("Found %d results.\n", vecResults.size());
    for (unsigned int i = 0, iEnd = vecResults.size(); i != iEnd; ++i)
    {
      printf("%d: %s\n", i, vecResults[i].c_str());
    }
  }

  sqlite3_close(pDB);

  return 0;
}

回答1:

Because the row is not a string.

Change the binding of your second parameter to sqlite3_bind_int(pStmt, 2, 3); and it should work fine.

Also, why do you have your name row as VARCHAR(30) instead of TEXT?