Boost C++ Libraries

This section covers using server-side prepared statements, an alternative to with_params. In general, prepared statements are more complex and less flexible than with_params, but might provide more efficiency under certain circumstances. Prefer with_params if you're not sure. See this section for a comparison between with_params and prepared statements.

Working with statements involves three networking operations:

The statement class holds a server-supplied handle to an open prepared statement.

Preparing a statement

Calling async_prepare_statement yields a statement object:

// Ask the server to prepare a statement to insert a new employee.
// statement is a lightweight handle to the server-side statement.
// Each ? is a parameter
mysql::statement stmt = co_await conn.async_prepare_statement(
    "INSERT INTO employee (first_name, last_name, salary, company_id) 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

Before executing a statement, you must specify its actual parameters by calling statement::bind. Binding happens client-side: the statement handle and the passed parameters are packed into an object that can be passed to async_execute, without any communication with the server. The object returned by bind can be passed to async_execute:

// Bind and execute the statement. You must pass one parameter per '?' placeholder in the statement.
// In the real world, parameters should be runtime values, rather than constants.
// Note that bind() does not involve communication with the server
mysql::results result;
co_await conn.async_execute(stmt.bind("John", "Doe", 40000, "HGS"), result);

Some observations:

Closing a statement

Prepared statements are created server-side, and thus consume server resources. You can deallocate statements that you don't need anymore by calling async_close_statement:

// Deallocate the statement from the server.
// Note that closing the connection will also deallocate the statement.
co_await conn.async_close_statement(stmt);

Prepared statements are managed by the server on a per-session basis. This is, once you close your connection with the server, any allocated prepared statements will be automatically closed for you. Calling any_connection::async_reset_connection will also close all the statements prepared by the current session. This is used by connection_pool to clean up sessions.

In general, avoid closing statements explicitly if you're using async_reset_connection or connection_pool, or if you're preparing a fixed number of statements at program startup. Closing statements involves network traffic that can be avoided. On the other hand, if you are creating and destroying prepared statements dynamically without using the aforementioned techniques, consider closing statement explicitly to limit server resource consumption.

NULLs and optionals

You can pass std::optional and boost::optional for parameters that may be NULL:

// Inserts a new employee into the database.
// We may not have the salary information for some people,
// so we represent the value as an optional
asio::awaitable<void> insert_employee(
    mysql::any_connection& conn,
    const mysql::statement& stmt,
    std::string_view first_name,
    std::string_view last_name,
    std::optional<int> salary,
    std::string_view company_id
)
{
    // If salary has a value, an integer will be sent to the server.
    // Otherwise, a NULL value will be sent
    mysql::results result;
    co_await conn.async_execute(stmt.bind(first_name, last_name, salary, company_id), result);
}

Parameter server-side type casting

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

// Prepare the statement
mysql::statement stmt = co_await conn.async_prepare_statement(
    "INSERT INTO employee (first_name, last_name, salary, company_id) VALUES (?, ?, ?, ?)"
);

// Execute it, passing an 8 byte unsigned integer as the salary value.
// The salary column was created as an INT (4 byte, signed integer).
// MySQL will cast the value server-side, and emit an error only if the supplied
// value is out of range of the target type
std::uint64_t salary = 45000;
mysql::results result;
co_await conn.async_execute(stmt.bind("John", "Doe", salary, "HGS"), result);

Executing a statement with a variable number of parameters

statement::bind has two forms:

The range should contain field or field_view elements, which can represent any MySQL type. For example:

// Executes the passed statement with the given parameters.
asio::awaitable<void> execute_statement(
    mysql::any_connection& conn,
    const mysql::statement& stmt,
    const std::vector<mysql::field>& params
)
{
    mysql::results result;
    co_await conn.async_execute(stmt.bind(params.begin(), params.end()), result);

    // Do something useful with result
}

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, depending on v's type.