Boost C++ Libraries

...one of the most highly regarded and expertly designed C++ library projects in the world. Herb Sutter and Andrei Alexandrescu, C++ Coding Standards

This is the documentation for an old version of Boost. Click here to view this page for the latest version.
PrevUpHomeNext

Prepared statements

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
results result;
conn.query(
    R"%(
        CREATE TEMPORARY TABLE products (
            id INT PRIMARY KEY AUTO_INCREMENT,
            description VARCHAR(256),
            price INT NOT NULL,
            show_in_store TINYINT
        )
    )%",
    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_ssl_connection& conn,
    const statement& stmt,
    string_view description,
    int price,
    bool show_in_store
)
{
    results result;
    conn.execute_statement(
        stmt,
        std::make_tuple(description, price, static_cast<int>(show_in_store)),
        result
    );
}

Some observations:

You can also pass field_views and fields as parameters. This is handy to insert NULL values:

// 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_ssl_connection& conn,
    const statement& stmt,
    std::optional<string_view> description,
    int price,
    bool show_in_store
)
{
    // if description has a value, description_param will have kind() == field_kind::string
    // and will point to it. Otherwise, description_param.kind() == field_kind::null
    auto description_param = description ? field_view(*description) : field_view();

    // Execute the insert
    results result;
    conn.execute_statement(
        stmt,
        std::make_tuple(description_param, price, static_cast<int>(show_in_store)),
        result
    );
}

For a full reference on the types you can pass as parameters when executing a statement, see this section.

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.


PrevUpHomeNext