...one of the most highly
regarded and expertly designed C++ library projects in the
world.
— Herb Sutter and Andrei
Alexandrescu, C++
Coding Standards
This section covers using server-side prepared statements. You should use them whenever a query contains parameters not known at compile-time.
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.
To execute a statement, use any of the following functions:
connection::execute_statement
or connection::async_execute_statement
,
which execute the statement and read the generated rows.
connection::start_statement_execution
and connection::async_start_statement_execution
,
which initiate a statement execution as a multi-function operation.
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:
?
placeholders by order.
std::tuple
.
You can pass in built-in integers, float
,
double
, date
,
datetime
,
time
,
field_view
and field
objects as parameters.
show_in_store
is passed
as an int
to execute_statement()
,
but is defined as a TINYINT
(1 byte integer) in the table. As long as the passed integer is in range,
MySQL will perform the required conversions. Otherwise, execute_statement()
will fail with an error (no undefined
behavior is invoked).
You can also pass field_view
s
and field
s
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.
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.