Boost C++ Libraries

This section covers using server-side prepared statements. You should use them whenever a query contains parameters not known at compile-time.

Preparing a statement

To prepare a statement, call connection::prepare_statement or connection::async_prepare_statement, passing your statement as a string. This yields a statement object:

// Table setup
const char* table_definition = R"%(
    CREATE TEMPORARY TABLE products (
        id INT PRIMARY KEY AUTO_INCREMENT,
        description VARCHAR(256),
        price INT NOT NULL,
        show_in_store TINYINT
    )
)%";
results result;
conn.execute(table_definition, result);

// Prepare a statement to insert into this table
statement stmt = conn.prepare_statement(
    "INSERT INTO products (description, price, show_in_store) VALUES (?, ?, ?)"
);

The question mark characters (?) represent parameters (as described here). When you execute the statement (next section), you provide values for each of the parameters you declared, and the server will use these values to run the statement.

Executing a statement

To execute a statement, use any of the following functions:

For example:

// description, price and show_in_store are not trusted, since they may
// have been read from a file or an HTTP endpoint
void insert_product(
    tcp_connection& conn,
    const statement& stmt,
    string_view description,
    int price,
    bool show_in_store
)
{
    results result;
    conn.execute(stmt.bind(description, price, show_in_store), result);
}

Some observations:

You can pass std::optional and boost::optional for parameters that may be NULL. If the optional doesn't have a value, NULL will be sent to the server. For example:

// description, price and show_in_store are not trusted, since they may
// have been read from a file or an HTTP endpoint
void insert_product(
    tcp_connection& conn,
    const statement& stmt,
    std::optional<string_view> description,
    int price,
    bool show_in_store
)
{
    // If description has a value, a string will be sent to the server; otherwise, a NULL will
    results result;
    conn.execute(stmt.bind(description, price, show_in_store), result);
}

Type casting with statement parameters

MySQL is quite permissive with the type of statement parameters. In most cases, it will perform the required casts for you. For example, given this table definition:

const char* table_definition = "CREATE TEMPORARY TABLE my_table(my_field TINYINT)";

We can write:

int value = get_int_value_from_user();
auto stmt = conn.prepare_statement("INSERT INTO my_table VALUES (?)");

results result;
conn.execute(stmt.bind(value), result);

MySQL expects a TINYINT, but we're sending an int, which is bigger. As long as the value is in range, this won't cause any trouble. If the value is out-of-range, execute will fail with an error.

Executing a statement with a variable number of parameters

The above approach works when you know at compile time how many parameters the statement has. In some scenarios (e.g. a graphical interface), this may not be the case. For these cases, you can bind a statement to a field or field_view iterator range:

void exec_statement(tcp_connection& conn, const statement& stmt, const std::vector<field>& params)
{
    results result;
    conn.execute(stmt.bind(params.begin(), params.end()), result);
}

Closing a statement

Prepared statements are created server-side, and thus consume server resources. If you don't need a statement anymore, you can call connection::close_statement or connection::async_close_statement to instruct the server to deallocate it.

Prepared statements are managed by the server on a per-connection basis. Once you close your connection with the server, all prepared statements you have created using this connection will be automatically deallocated.

If you are creating your prepared statements at the beginning of your program and keeping them alive until the connection is closed, then there is no need to call close_statement(), as closing the connection will do the cleanup for you. If you are creating and destroying prepared statements dynamically, then it is advised to use close_statement() to prevent excessive resource usage in the server.

Finally, note that statement's destructor does not perform any server-side deallocation of the statement. This is because closing a statement involves a network operation that may block or fail.

Type mapping reference for prepared statement parameters

The following table contains a reference of the types that can be used when binding a statement. If a type can be used this way, we say to satisfy the WritableField concept. The table shows how a parameter v in a expression conn.execute(stmt.bind(v), result) is interpreted by MySQL, depeding on v's type.