How do I bind a variable to a prepared statement with the SOCI libary?

Question

Currently my application only supports SQLite databases, but I would like to support both SQLite and MySQL databases, so I'm testing out the SOCI library to see if it does what I need. However, despite the examples and documentation, I can't figure out how SOCI handles prepared statements.

When using the SQLite C API, you prepare statement:

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

And later you bind a value to the :id place holder, execute the statement and step through the results:

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
}

How do I do this with SOCI? It looks like the prepare and bind concepts are not separated like with the native SQLite API. Does the bind have to happen during the prepare using soci::use()?

Update 1: In case I'm not explaining the question well enough: Here's a small, working, C++ example using the SQLite C API. If I could see this re-implemented using SOCI, it would answer the question.

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

// Tables and data
const char* table = "CREATE TABLE test ( user_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;",
                        -1, &statement, 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, index, 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, NULL );
    sqlite3_exec( database, hank, NULL, NULL, NULL );
    sqlite3_exec( database, bill, NULL, NULL, NULL );
    sqlite3_exec( database, fred, NULL, NULL, NULL );

    sqlite3_stmt* statement = make_statement( database );

    bind_statement( statement, 2 );

    execute_statement( statement );

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

    return 1;
}

The same program partially implemented using SOCI (Note the two stub functions marked as HELPME)

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

const char* table = "CREATE TABLE test ( user_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');";

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

Update 2: I ended up ditching SOCI when I found the cppdb library. Unlike SOCI, it is just a very thin wrapper around the native C APIs, which suits my needs at this time.

Answer 1

The documentation explains how to use prepared statements with parameters:

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

Please note that the lifetime of the user_id and name variables must be at least as long as that of st.