c – 如何使用SOCI库将变量绑定到预准备语句？

目前我的应用程序只支持sqlite数据库,但我想支持sqlite和 MySQL数据库,所以我正在测试 SOCI library,看看它是否能满足我的需求.然而,尽管有 examples and documentation,我还是无法弄清楚SOCI如何处理准备好的陈述.

使用SQLite C API时,您准备声明：

sqlite3_stmt* statement;
sqlite3_prepare_v2( database_handle_pointer,"SELECT * FROM table WHERE user_id=:id;",-1,&statement,NULL );

然后将值绑定到：id占位符,执行语句并逐步执行结果：

const sqlite3_int64 user_id = some_function_that_returns_a_user_id();
const int index = sqlite3_bind_parameter_index( statement,":id" );
sqlite3_bind_int64( statement,index,user_id );

while ( sqlite3_step( statement ) == sqlITE_ROW )
{
     // Do something with the row
}

我如何使用SOCI进行此操作？看起来准备和绑定概念不像原生sqlite API那样分开.在使用soci :: use()准备期间是否必须发生绑定？

更新1：如果我没有充分解释这个问题：这是一个使用sqlite C API的小型工作C示例.如果我能看到这个使用SOCI重新实现,它将回答这个问题.

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

// Tables and data
const char* table = "create table test ( @R_772_9004@id integer,name CHAR );";
const char* hank = "INSERT INTO test (user_id,Name) VALUES(1,'Hank');";
const char* bill = "INSERT INTO test (user_id,Name) VALUES(2,'Bill');";
const char* fred = "INSERT INTO test (user_id,Name) VALUES(3,'Fred');";

// Create a sqlite prepared statement to SELEct a user from the test table.
sqlite3_stmt* make_statement( sqlite3* database )
{
    sqlite3_stmt* statement;
    sqlite3_prepare_v2( database,"SELECT name FROM test WHERE user_id=:id;",NULL );
    return statement;
}

// Bind the requested user_id to the prepared statement.
void bind_statement( sqlite3_stmt* statement,const sqlite3_int64 user_id )
{
    const int index = sqlite3_bind_parameter_index( statement,":id" );
    sqlite3_bind_int64( statement,user_id );
}

// Execute the statement and print the name of the SELEcted user.
void execute_statement( sqlite3_stmt* statement )
{
    while ( sqlite3_step( statement ) == sqlITE_ROW )
    {
        std::cout << sqlite3_column_text( statement,0 ) << "\n";
    }
}

int main()
{
    // Create an in-memory database.
    sqlite3* database;
    if ( sqlite3_open( ":memory:",&database ) != sqlITE_OK )
    {
        std::cerr << "Error creaTing database" << std::endl;
        return -1;
    }

    // Create a table and some rows.
    sqlite3_exec( database,table,NULL,NULL );
    sqlite3_exec( database,hank,bill,fred,NULL );

    sqlite3_stmt* statement = make_statement( database );

    bind_statement( statement,2 );

    execute_statement( statement );

    // Cleanup
    sqlite3_finalize( statement );
    sqlite3_close( database );

    return 1;
}

使用SOCI部分实现相同的程序(注意标记为HelPME的两个存根函数)

#include <soci/soci.h>
#include <iostream>

const char* table = "create table test ( @R_772_9004@id integer,'Fred');";

soci::statement make_statement( soci::session& database )
{
    soci::statement statement =
        database.prepare << "SELECT name FROM test WHERE user_id=:id";
    return statement;
}

void bind_statement( soci::statement& statement,const int user_id )
{
    // HelPME: what goes here?
}

void execute_statement( soci::statement& statement )
{
    // HelPME: what goes here?
}

int main()
{
    soci::session database( "sqlite3",":memory:" );

    database << table;
    database << hank;
    database << bill;
    database << fred;

    soci::statement statement = make_statement( database );
    bind_statement( statement,2 );
    execute_statement( statement );
}

更新2：当我找到cppdb library时,我最终放弃了SOCI.与SOCI不同,它只是一个非常薄的本地C API包装器,适合我的需求.

int user_id;
String name;
statement st = (database.prepare << "SELECT name FROM test WHERE user_id = :id",use(user_id),into(Name));

user_id = 1;
st.execute(true);