在SQLite3中绑定参数如何工作（用最少的例子）？ [英] How does binding parameters work in SQLite3 (with minimal example)?

问题描述

有人建议在SQLite中使用参数绑定，以加速循环查询。但是，如果我有多个参数绑定，它不工作。我没有看到我的错误。所有SQLite函数都返回SQLITE_OK ...

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...

下面，我写了一个最小的例子。它创建一个表，使三个条目，然后查询两次，一次有一个绑定参数，一次有两个。第一次返回一个结果，这是正确的。第二个返回零结果，sqlite3_step（）返回101（SQLITE_DONE）。为什么不返回一个结果？

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

推荐答案

将第二个参数的绑定更改为 sqlite3_bind_int（pStmt，2，3）; 工作正常。

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

此外，为什么你的名称行为VARCHAR（30）而不是TEXT？

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

这篇关于在SQLite3中绑定参数如何工作（用最少的例子）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！