Boost C++ Libraries

All the previous tutorials have only used SELECT statements, but Boost.MySQL is not limited to them. Using async_execute you can run any SQL statement supported by MySQL.

In this tutorial, we will write a program that changes the first name of an employee, given their ID, and prints the updated employee details. We will use an UPDATE and transaction management statements. INSERT and DELETE statements have similar mechanics.

A simple UPDATE

We can use the same tools and functions as in previous tutorials:

// Run an UPDATE. We can use with_params to compose it, too
// If new_first_name contains 'John' and employee_id contains 42, this will run:
//    UPDATE employee SET first_name = 'John' WHERE id = 42
// result contains an empty resultset: it has no rows
mysql::results result;
co_await conn.async_execute(
    mysql::with_params(
        "UPDATE employee SET first_name = {} WHERE id = {}",
        new_first_name,
        employee_id
    ),
    result
);

By default, auto-commit is enabled, meaning that when async_execute returns, the UPDATE is visible to other client connections.

Checking that the UPDATE took effect

The above query will succeed even if there was no employee with the given ID. We would like to retrieve the updated employee details on success, and emit a useful error message if no employee was matched.

We may be tempted to use results::affected_rows at first, but this doesn't convey the information we're looking for: a row may be matched but not affected. For example, if you try to set first_name to the same value it already has, MySQL will count the row as a matched, but not affected.

MySQL does not support the UPDATE ... RETURNING syntax, so we will have to retrieve the employee manually after updating it. We can add the following after our UPDATE:

// Retrieve the newly created employee.
// As we will see, this is a potential race condition
// that can be avoided with transactions.
co_await conn.async_execute(
    mysql::with_params("SELECT first_name, last_name FROM employee WHERE id = {}", employee_id),
    result
);

if (result.rows().empty())
{
    std::cout << "No employee with ID = " << employee_id << std::endl;
}
else
{
    std::cout << "Updated: " << result.rows().at(0).at(0) << " " << result.rows().at(0).at(1)
              << std::endl;
}

However, the code above contains a race condition. Imagine the following situation:

To our program, it looks like we succeeded performing the update, when we really didn't. Depending on the nature of our program, this may or may not have serious consequences, but it's something we should avoid.

Avoiding the race condition with a transaction block

We can fix the race condition using transactions. In MySQL, a transaction block is opened with START TRANSACTION. Subsequent statements will belong to the transaction block, until the transaction either commits or is rolled back. A COMMIT statement commits the transaction. A rollback happens if the connection that initiated the transaction closes or an explicit ROLLBACK statement is used.

We will enclose our UPDATE and SELECT statements in a transaction block. This will ensure that the SELECT will get the updated row, if any:

mysql::results empty_result, select_result;

// Start a transaction block. Subsequent statements will belong
// to the transaction block, until a COMMIT or ROLLBACK is encountered,
// or the connection is closed.
// START TRANSACTION returns no rows.
co_await conn.async_execute("START TRANSACTION", empty_result);

// Run the UPDATE as we did before
co_await conn.async_execute(
    mysql::with_params(
        "UPDATE employee SET first_name = {} WHERE id = {}",
        new_first_name,
        employee_id
    ),
    empty_result
);

// Run the SELECT. If a row is returned here, it is the one
// that we modified.
co_await conn.async_execute(
    mysql::with_params("SELECT first_name, last_name FROM employee WHERE id = {}", employee_id),
    select_result
);

// Commit the transaction. This makes the updated row visible
// to other transactions and releases any locked rows.
co_await conn.async_execute("COMMIT", empty_result);

// Process the retrieved rows
if (select_result.rows().empty())
{
    std::cout << "No employee with ID = " << employee_id << std::endl;
}
else
{
    std::cout << "Updated: " << select_result.rows().at(0).at(0) << " "
              << select_result.rows().at(0).at(1) << std::endl;
}

Using multi-queries

While the code we've written is correct, it's not very performant. We're incurring in 4 round-trips to the server, when our queries don't depend on the result of previous ones. The round-trips occur within a transaction block, which causes certain database rows to be locked, increasing contention. We can improve the situation by running our four statements in a single batch.

Multi-queries are a protocol feature that lets you execute several queries at once. Individual queries must be separated by semicolons.

Multi-queries are disabled by default. To enable them, set connect_params::multi_queries to true before connecting:

// The server host, username, password and database to use.
// Setting multi_queries to true makes it possible to run several
// semicolon-separated queries with async_execute.
mysql::connect_params params;
params.server_address.emplace_host_and_port(std::string(server_hostname));
params.username = std::move(username);
params.password = std::move(password);
params.database = "boost_mysql_examples";
params.multi_queries = true;

// Connect to the server
co_await conn.async_connect(params);

Multi-queries can be composed an executed using the same functions we've been using:

// Run the 4 statements in a single round-trip.
// If an error is encountered, successive statements won't be executed
// and the transaction won't be committed.
mysql::results result;
co_await conn.async_execute(
    mysql::with_params(
        "START TRANSACTION;"
        "UPDATE employee SET first_name = {} WHERE id = {};"
        "SELECT first_name, last_name FROM employee WHERE id = {};"
        "COMMIT",
        new_first_name,
        employee_id,
        employee_id
    ),
    result
);

Accessing the results is slightly different. MySQL returns 4 resultsets, one for each query. In Boost.MySQL, this operation is said to be multi-resultset. results can actually store more than one resultset. results::rows actually accesses the rows in the first resultset, because it's the most common use case.

We want to get the rows retrieved by the SELECT statement, which corresponds to the third resultset. results::at returns a resultset_view containing data for the requested resultset:

// Get the 3rd resultset. resultset_view API is similar to results
mysql::resultset_view select_result = result.at(2);
if (select_result.rows().empty())
{
    std::cout << "No employee with ID = " << employee_id << std::endl;
}
else
{
    std::cout << "Updated: " << select_result.rows().at(0).at(0) << " "
              << select_result.rows().at(0).at(1) << std::endl;
}

Using manual indices in with_params

Repeating employee_id in the parameter list passed to with_params violates the DRY principle. As with std::format, we can refer to a format argument more than once by using manual indices:

// {0} will be replaced by the first format arg, {1} by the second
mysql::results result;
co_await conn.async_execute(
    mysql::with_params(
        "START TRANSACTION;"
        "UPDATE employee SET first_name = {0} WHERE id = {1};"
        "SELECT first_name, last_name FROM employee WHERE id = {1};"
        "COMMIT",
        new_first_name,
        employee_id
    ),
    result
);

Using the static interface with multi-resultset

Finally, we can rewrite our code to use the static interface so it's safer. In multi-resultset scenarios, we can pass as many row types to static_results as resultsets we expect. We can use the empty tuple (std::tuple<>) as a row type for operations that don't return rows, like the UPDATE. Our code becomes:

// MySQL returns one resultset for each query, so we pass 4 params to static_results
mysql::static_results<
    std::tuple<>,                  // START TRANSACTION doesn't generate rows
    std::tuple<>,                  // The UPDATE doesn't generate rows
    mysql::pfr_by_name<employee>,  // The SELECT generates employees
    std::tuple<>                   // The COMMIT doesn't generate rows
> result;

co_await conn.async_execute(
    mysql::with_params(
        "START TRANSACTION;"
        "UPDATE employee SET first_name = {0} WHERE id = {1};"
        "SELECT first_name, last_name FROM employee WHERE id = {1};"
        "COMMIT",
        new_first_name,
        employee_id
    ),
    result
);

// We've run 4 SQL queries, so MySQL has returned us 4 resultsets.
// The SELECT is the 3rd resultset. Retrieve the generated rows.
// employees is a span<const employee>
auto employees = result.rows<2>();
if (employees.empty())
{
    std::cout << "No employee with ID = " << employee_id << std::endl;
}
else
{
    const employee& emp = employees[0];
    std::cout << "Updated: employee is now " << emp.first_name << " " << emp.last_name << std::endl;
}

Wrapping up

Full program listing for this tutorial is here.

You can now proceed to the next tutorial.