如何准备sql语句和绑定参数? [英] How to prepare sql statements and bind parameters?

问题描述

不幸的是，文档完全缺少示例(确实很奇怪)，好像它假设所有读者都喜欢成为优秀的程序员.而C++是我的新手，无法真正从文档中了解如何真正准备和执行语句.我喜欢在PDO中为PHP实现它的方式.通常，我只是这样做:

Unfortunatelly, the documentation completely lacks examples (what is really strange), as if it assumes all its readers to be good programmers. Whereas, I'm quite new to C++ and can not really figure out from the documentation how to really prepare and execute statements. I love the way how it is implemented in PDO for PHP. Normally, I just do it like this:

$s = $db->prepare("SELECT id FROM mytable WHERE id = :id");
$s->bindParam(':id', $id);
$s->execute();

或使用?令牌进行操作:

 $data = array();
 $data[] = 1;
 $data[] = 2;
 $s = $db->prepare("SELECT id FROM mytable WHERE id = ? or id = ?");
 $s->execute($data);

现在，我手中有C++和sqlite3.h.现在，我知道如何连接数据库-我做到了，没有任何错误:

Now, I have C++ and sqlite3.h in my hands. At this moment, I know just how to connect to database - I do it and get no errors:

sqlite3 * conn;
int rc = sqlite3_open(db_name, &conn);

请提供一些有关如何实现PDO在PHP中所做的类似操作的说明(带有清晰的小示例)-使用命名参数和?标记准备一条语句.

Please, give some instructions (with clear tiny examples) about how to implement similar things that PDO does in PHP - prepare a statement using named arguments and using ? tokens.

推荐答案

您可以在此处找到大量文档: sqlite.org
此示例未详细解释sqlite3函数调用和参数，因为这是很多要覆盖的信息-而是参考给定的链接以获取更详细的信息.

You can find a decent amount of documentation here: sqlite.org
This example does not explain the sqlite3 function calls and parameters in detail, as that is quite a lot of information to cover - instead refer to the given link for more in-depth detail.

此示例多次将值绑定到问题中的语句，并在每次绑定后读取所有查询结果:

This example binds values to the statement in your question multiple times, and reads all query results after each bind:

sqlite3* conn;
sqlite3_stmt* stmt = 0;

int rc = sqlite3_open(db_name, &conn);
//  Good idea to always check the return value of sqlite3 function calls. 
//  Only done once in this example:
if ( rc != SQLITE_OK ) { // Do something }

rc = sqlite3_prepare_v2( conn, "SELECT id FROM myTable WHERE id = ? or id = ?", -1, &stmt, 0 );

//  Optional, but will most likely increase performance.
rc = sqlite3_exec( conn, "BEGIN TRANSACTION", 0, 0, 0 );    

for ( int bindIndex = 0; bindIndex < number_of_times_you_wish_to_bind; bindIndex++ ) {
    //  Binding integer values in this example.
    //  Bind functions for other data-types are available - see end of post.

    //  Bind-parameter indexing is 1-based.
    rc = sqlite3_bind_int( stmt, 1, int_you_wish_to_bind ); // Bind first parameter.
    rc = sqlite3_bind_int( stmt, 2, int_you_wish_to_bind ); // Bind second parameter.

    //  Reading interger results in this example.
    //  Read functions for other data-types are available - see end of post.
    while ( sqlite3_step( stmt ) == SQLITE_ROW ) { // While query has result-rows.
        //  In your example the column count will be 1.
        for ( int colIndex = 0; colIndex < sqlite3_column_count( stmt ); colIndex++ ) { 
            int result = sqlite3_column_int( stmt, colIndex );
            //  Do something with the result.
        }
    }
    //  Step, Clear and Reset the statement after each bind.
    rc = sqlite3_step( stmt );
    rc = sqlite3_clear_bindings( stmt );
    rc = sqlite3_reset( stmt );
}
char *zErrMsg = 0;  //  Can perhaps display the error message if rc != SQLITE_OK.
rc = sqlite3_exec( conn, "END TRANSACTION", 0, 0, &zErrMsg );   //  End the transaction.

rc = sqlite3_finalize( stmt );  //  Finalize the prepared statement.

更多绑定函数

更多阅读功能

这篇关于如何准备sql语句和绑定参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！