Sqlite C ++中的预准备语句如何工作 [英] How does prepared statements in Sqlite C++ work

问题描述

我不知道如何在Sqlite3代码中实现准备好的语句

I do not know how to implement prepared statements in my Sqlite3 code

#include <iostream>
#include <sqlite3.h>
#include <stdio.h>

static int callback (void* NotUsed, int argc, char** argv, char** azColName) {
    int i;
    for (i = 0; i < argc; i++) {
        std::cout << ("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    std::cout << ("\n");
    return 0;
}

int main (int argc, char* argv[]) {
    sqlite3* db;
    char* zErrMsg = 0;
    int rc;
    char* sql;

    /* Open database */
    rc = sqlite3_open ("test.db", &db);

    if (rc) {
        std::cerr << "Can't open database: \n" << sqlite3_errmsg (db);
        return (0);
    }
    else {
        std::cout << "Opened database successfully\n";
    }
    std::string newName;
    std::cin >> newName;
    /* Create SQL statement */
    sql = "UPDATE company SET name = newName WHERE id = 1";


    /* Execute SQL statement */
    rc = sqlite3_exec (db, sql, callback, 0, &zErrMsg);

    if (rc != SQLITE_OK) {
        std::cout << "SQL error: \n" << zErrMsg;
        sqlite3_free (zErrMsg);
    }
    else {
        std::cout << "Records created successfully\n";
    }
    sqlite3_close (db);
    return 0;
}

用户必须输入newName，并且此变量应用于更新数据库中的字段.这种方式不起作用，因为Sql脚本正在搜索列.在互联网上，我发现必须使用准备好的语句，但是我不知道如何实现它.

The user has to input newName and this variable should be used to Update a field in the Database. This way it does not work, because the Sql script is searching for a column. In the internet I found, that I had to use a prepared statement, but I do not know how to implement it.

推荐答案

您从一个sql语句开始，该语句具有占位符，该占位符表示您以后希望绑定的参数.在这里，我为占位符使用一个问号，但是文档.

You start with an sql statement that has placeholders for the parameters that you wish to bind later. Here, I use a single question mark for the placeholder, but there are other options described in the documentation.

std::string sql = "UPDATE company SET name = ? WHERE id = 1";

然后，您构造一个准备好的语句(或如他们在sqlite文档中所说的那样编译").通常，您将使用 sqlite_prepare_v2 函数，但是有其他(例如，当您的语句使用utf-8以外的其他格式编码时.)

Then you construct a prepared statement (or "compile", as they say it in sqlite documentation). You'll normally use sqlite_prepare_v2 function, but there are others (for when your statement is encoded in something else than utf-8, for example).

sqlite3_stmt* stmt; // will point to prepared stamement object
sqlite3_prepare_v2(
    db,            // the handle to your (opened and ready) database
    sql.c_str(),    // the sql statement, utf-8 encoded
    sql.length(),   // max length of sql statement
    &stmt,          // this is an "out" parameter, the compiled statement goes here
    nullptr);       // pointer to the tail end of sql statement (when there are 
                    // multiple statements inside the string; can be null)

然后绑定参数.有整堆可用的功能.您确切使用哪一个取决于类型您绑定到该参数的数据.在这里，我们绑定文本，因此我们使用 sqlite3_bind_text :

Then you bind the parameter(s). There's a whole bunch of avaliable functions. Which one exactly you use depends on the type of data that you're binding to the parameter. Here, we bind text, so we use sqlite3_bind_text:

std::string newName = /* get name from user */;
sqlite3_bind_text(
    stmt,             // previously compiled prepared statement object
    1,                // parameter index, 1-based
    newName.c_str(),  // the data
    newName.length(), // length of data
    SQLITE_STATIC);   // this parameter is a little tricky - it's a pointer to the callback
                      // function that frees the data after the call to this function.
                      // It can be null if the data doesn't need to be freed, or like in this case,
                      // special value SQLITE_STATIC (the data is managed by the std::string
                      // object and will be freed automatically).

因此，准备好的语句已准备就绪.现在，您将其传递给 sqlite3_step :

So, the prepared statement is ready to go. Now you execute it by passing it to sqlite3_step:

 sqlite3_step(stmt); // you'll want to check the return value, read on...

现在，当您逐步执行本应返回结果表行的语句时，只要有要处理的结果行，此函数将继续返回 SQLITE_ROW 和 SQLITE_DONE ，当没有剩余时.您可以使用 sqlite3_column_ * 函数系列从结果行中获取单个列.我会让你自己解决这个问题.

Now, when you step through a statement that's supposed to return rows of a result table, this function will keep returning SQLITE_ROW as long as there are result rows to process, and SQLITE_DONE when there are none left. You can use sqlite3_column_* family of functions to get the single columns from a result row. I'll let you figure this out on your own.

对于您拥有的简单更新语句， sqlite3_step 将在第一次调用时返回 SQLITE_DONE .此处.

For a simple update statements that you have, sqlite3_step will return SQLITE_DONE on the first call. More info and possible error codes are here.

全部完成后，您将破坏准备好的语句.

When it's all done, you finish by destructing the prepared statement.

sqlite3_finalize(stmt);

我希望这可以帮助您入门.

I hope this should get you started.

这篇关于Sqlite C ++中的预准备语句如何工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！