Boost C++ Libraries

Using stored procedures

Stored procedures can be called using the CALL SQL statement. You can use CALL statements from both text queries and prepared statements, in a similar way to other SQL statements. Contrary to other statements, CALL may generate more than one resultset.

For example, given a stored procedure like this:

CREATE PROCEDURE get_employees(IN pin_company_id CHAR(10))
BEGIN
    START TRANSACTION READ ONLY;
    SELECT id, name, tax_id FROM company WHERE id = pin_company_id;
    SELECT first_name, last_name, salary FROM employee WHERE company_id = pin_company_id;
    COMMIT;
END

A statement like CALL get_employees('my_company') will generate three resultsets:

Every resultset contains its own rows, metadata, last insert ID, affected rows and so on.

Calling procedures using the dynamic interface

The same results class we've been using supports storing more than one resultset. You can execute a CALL statement as any other SQL statement:

// We're using the dynamic interface. results can stored multiple resultsets
results result;

// The procedure parameter, employee_id, will likely be obtained from an untrusted source,
// so we will use a prepared statement
statement get_employee_stmt = conn.prepare_statement("CALL get_employees(?)");

// Obtain the parameters required to call the statement, e.g. from a file or HTTP message
std::int64_t employee_id = get_employee_id();

// Call the statement
conn.execute(get_employee_stmt.bind(employee_id), result);

// results can be used as a random-access collection of resultsets.
// result.at(0).rows() returns the matched companies, if any
rows_view matched_company = result.at(0).rows();

// We can do the same to access the matched employees
rows_view matched_employees = result.at(1).rows();

// Use matched_company and matched_employees as required

In this context, results can be seen as a random-access collection of resultsets. You can access resultsets by index using results::at and results::operator[]. These operations yield a resultset_view, which is a lightweight object pointing into memory owned by the results object. You can take ownserhip of a resultset_view using the resultset class. For example:

// result is actually a random-access collection of resultsets.
// The INSERT is the 2nd query, so we can access its resultset like this:
boost::mysql::resultset_view insert_result = result.at(1);

// A resultset has metadata, rows, and additional data, like the last insert ID:
std::int64_t post_id = insert_result.last_insert_id();

// The SELECT result is the third one, so we can access it like this:
boost::mysql::resultset_view select_result = result.at(2);

// select_result is a view that points into result.
// We can take ownership of it using the resultset class:
boost::mysql::resultset owning_select_result(select_result);  // valid even after result is destroyed

// We can access rows of resultset objects as usual:
std::int64_t num_posts = owning_select_result.rows().at(0).at(0).as_int64();

Calling procedures using the static interface

The static_results class supports operations that return multiple resultsets, too. As with other SQL statements, we need to define the row types in our resultsets in advance:

// Describes the first resultset
struct company
{
    std::string id;
    std::string name;
    std::string tax_id;
};
BOOST_DESCRIBE_STRUCT(company, (), (id, name, tax_id))

// Describes the second resultset
struct employee
{
    std::string first_name;
    std::string last_name;
    boost::optional<std::uint64_t> salary;
};
BOOST_DESCRIBE_STRUCT(employee, (), (first_name, last_name, salary))

// The last resultset will always be empty.
// We can use an empty tuple to represent it.
using empty = std::tuple<>;

We can now use static_results, passing it as many template arguments as resultsets we expect. The library will check that the correct number of resultsets are actually returned by the server, and will parse them into the row types that we provided:

// We must list all the resultset types the operation returns as template arguments
static_results<company, employee, empty> result;
conn.execute("CALL get_employees('HGS')", result);

// We can use rows<0>() to access the rows for the first resultset
if (result.rows<0>().empty())
{
    std::cout << "Company not found" << std::endl;
}
else
{
    const company& comp = result.rows<0>()[0];
    std::cout << "Company name: " << comp.name << ", tax_id: " << comp.tax_id << std::endl;
}

// rows<1>() will return the rows for the second resultset
for (const employee& emp : result.rows<1>())
{
    std::cout << "Employee " << emp.first_name << " " << emp.last_name << std::endl;
}

Use static_results::rows with an explicit index to access each resultset's data. You can also use explicit indices with the other accessor functions, like static_results::meta and static_results::last_insert_id.

For more information about the static interface, please refer to this section.

Determining the number of resultsets

To know the number of resultsets to expect from a CALL statement, use these rules:

Some examples:

-- Calling proc1 produces only 1 resultset because it only contains statements that
-- don't retrieve data
CREATE PROCEDURE proc1(IN pin_order_id INT, IN pin_quantity INT)
BEGIN
    START TRANSACTION;
    UPDATE orders SET quantity = pin_quantity WHERE id = pin_order_id;
    INSERT INTO audit_log (msg) VALUES ("Updated order...");
    COMMIT;
END

-- Calling proc2 produces 3 resultsets: one for the orders SELECT, one for the
-- line_items SELECT, and one for the CALL statement
CREATE PROCEDURE proc2(IN pin_order_id INT)
BEGIN
    START TRANSACTION READ ONLY;
    SELECT * FROM orders WHERE id = pin_order_id;
    SELECT * FROM line_items WHERE order_id = pin_order_id;
    COMMIT;
END

Output parameters

You can get the value of OUT and INOUT parameters in stored procedures by using prepared statement placeholders for them. When doing this, you will receive another resultset with a single row containing all output parameter values. This resultset is located after all resultsets generated by SELECTs, and before the final, empty resultset.

For example, given this procedure:

CREATE PROCEDURE create_employee(
    IN  pin_company_id CHAR(10),
    IN  pin_first_name VARCHAR(100),
    IN  pin_last_name VARCHAR(100),
    OUT pout_employee_id INT
)
BEGIN
    START TRANSACTION;
    INSERT INTO employee (company_id, first_name, last_name)
        VALUES (pin_company_id, pin_first_name, pin_last_name);
    SET pout_employee_id = LAST_INSERT_ID();
    INSERT INTO audit_log (msg) VALUES ('Created new employee...');
    COMMIT;
END

You can use:

// To retrieve output parameters, you must use prepared statements. Text queries don't support this
// We specify placeholders for both IN and OUT parameters
statement stmt = conn.prepare_statement("CALL create_employee(?, ?, ?, ?)");

// When executing the statement, we provide an actual value for the IN parameters,
// and a dummy value for the OUT parameter. This value will be ignored, but it's required by the
// protocol
results result;
conn.execute(stmt.bind("HGS", "John", "Doe", nullptr), result);

// Retrieve output parameters. This row_view has an element per
// OUT or INOUT parameter that used a ? placeholder
row_view output_params = result.out_params();
std::int64_t new_employee_id = output_params.at(0).as_int64();

results::out_params simplifies the process.

Semicolon-separated queries

It is possible to run several semicolon-separated text queries in a single connection::execute call. For security, this capability is disabled by default. Enabling it requires setting handshake_params::multi_queries before connecting:

// The username and password to use
boost::mysql::handshake_params params(
    mysql_username,         // username, as a string
    mysql_password,         // password, as a string
    "boost_mysql_examples"  // database
);

// Allows running multiple semicolon-separated in a single call.
// We must set this before calling connect
params.set_multi_queries(true);

// Connect to the server specifying that we want support for multi-queries
conn.connect(endpoint, params);

// We can now use the multi-query feature.
// This will result in three resultsets, one per query.
results result;
conn.execute(
    R"(
        CREATE TEMPORARY TABLE posts (
            id INT PRIMARY KEY AUTO_INCREMENT,
            title VARCHAR (256),
            body TEXT
        );
        INSERT INTO posts (title, body) VALUES ('Breaking news', 'Something happened!');
        SELECT COUNT(*) FROM posts;
    )",
    result
);

Note that statements like DELIMITER do not work using this feature. This is because DELIMITER is a pseudo-command for the mysql command line tool, not actual SQL.

You can also use the static interface with multi-queries. It works the same as with stored procedures.