MySQL Prepared Statements¶
This document contains implementation notes and information for developers that may intend to use the prepared statement C++ API for MySQL queries.
Thread Safety¶
Ideally threads would have their own database connections but the current zone connections may be used concurrently in other threads. The mysql::PreparedStmt
class locks the DBcore
connection mutex in calls to Prepare
, Execute
, and when ~PreparedStmt
destruction closes the stmt in an attempt to make it safe to use with server connections.
The MySQL C API documentation provides no specific guidance for using prepared statements on a connection shared by multiple threads. The only real guarantee for safety is to synchronize the connection for the lifetime of the mysql::PreparedStmt
.
Warning
Prepared statements should only be used on the main thread even if the connection is synchronized. This is because server logging is performed internally which is not synchronized.
Info
The #hotfix command may be the only real use of querying in another thread.
Regular queries are synchronized by locking the connection mutex for the query until the results are buffered (mysql_real_query
and mysql_store_result
). mysql::PreparedStmt
is designed to prepare, execute (possibly many times), fetch, and close separately. The following are only observed guidelines for making these safe to use on connections shared with other threads and may not be accurate:
-
Synchronizing with the
DBcore
connection mutex must be done before any use ofPrepare()
orExecute()
. The lock can be released between these calls. -
The connection mutex needs to be locked when
PreparedStmt
closes its statement during destruction (mysql_stmt_close
). -
If the
buffer_results
option is enabled thenFetch()
should be safe to use without locking the connection since it accesses results stored on the client. -
If result buffering is disabled then the connection cannot be used after
Execute
until all rows are fetched or freed from the result set. Failure to do so will cause aCommands out of sync
error if another query is attempted on the same connection. This makes it impossible to use prepared statements safely without buffering results when used with multithreaded server connections.Note
Unbuffered prepared statements would be possible to use safely if the connection mutex was exposed in the
DBcore
public API but the caller would be responsible for locking it before execute and holding it until all rows were fetched. The connection mutex is recursive so there would be no risk of deadlock from internalPreparedStmt
functions also locking it. AlternativelyPreparedStmt
could be given an option on construction to hold a lock on the connection mutex for its lifetime.
Error Handling¶
Prepared statements will throw a std::runtime_error
exception for errors. This differs from regular server queries which return an empty object and requires checking for success. This means usage must be wrapped in try/catch but simplifies error handling internally.
Options¶
The StmtOptions
class contains options that may be changed to increase performance but care must be taken if the database connection is used concurrently.
buffer_results
-
Enabled by default. Disabling may improve performance.
Stores the entire result set on client by calling
mysql_stmt_store_result
after executing a prepared statement.This transfers the full result set over the network and uses more memory but gives access to total row count and
max_length
of columns.If disabled memory usage is reduced and each row is fetched over the network. Disabling may also require output buffers for strings to be re-allocated while fetching since
max_length
will be unavailable.Warning
This option must be enabled if other queries could occur on the same connection before all results are fetched or freed. Failure to do so will cause a
Commands out of sync
error. use_max_length
-
Enabled by default. Requires
buffer_results
to be enabled. Disabling may improve performance when storing results.This causes the
max_length
of fields to be calculated when a prepared statement is executed so output buffers for strings may be pre-allocated.If disabled, output buffers for string columns may need to be re-allocated while fetching.
See note in official
mysql_stmt_store_result
documentation for more information.
Column Values¶
mysql::StmtColumn
getters return a std::optional<T>
to make it easier to handle columns that might return a NULL
value. If the caller knows the column cannot be null, then it may just dereference the optional to get the value without the overhead of checking. Otherwise it should check if the optional has a value or use value_or()
to return a fallback default value.
Example¶
#include <../common/mysql_stmt.h>
void foo()
{
try
{
mysql::PreparedStmt stmt = content_db.Prepare("select * from spells_new where id = ? or Name = ?");
mysql::StmtResult result = stmt.Execute({ 100, "Illusion: Feir'Dal" });
int total_rows = result.RowCount();
while (mysql::StmtRow row = stmt.Fetch())
{
int32_t id = *row.Get<int32_t>(0); // get id by col index, we know this can't be NULL so dereference
std::string name = row["name"].value_or(""); // get str by field name, value may be NULL
}
}
catch (const std::exception& ex)
{
// handle failure, the error is already logged with LogMySQLError
}
}
API¶
DBcore::Prepare¶
Prepare(string query)
-
Prepares a statement and returns a
mysql::PreparedStmt
object.Throws
std::runtime_error
if an error occurs.
mysql::PreparedStmt¶
Types
param_t
std::variant
of supported argument types for MySQL prepared statement executionusing param_t = std::variant<int8_t, uint8_t, int16_t, uint16_t, int32_t, uint32_t, int64_t, uint64_t, float, double, bool, std::string_view, std::nullptr_t>;
Functions
Execute()
Execute(const std::vector<T>& inputs)
Execute(const std::vector<param_t>& inputs)
-
Executes the prepared statement with specified input arguments and returns a
mysql::StmtResult
object.Throws
std::runtime_error
if an error occurs. Fetch()
-
Fetches the next row and returns a
mysql::StmtRow
object. The returned object will evaluate asfalse
when there are no more rows to fetch.Throws
std::runtime_error
if an error occurs.
mysql::StmtResult¶
Represents the result of executing a prepared statement. Holds a non-owning view of column data and should not be used if the prepared statement is executed again.
ColumnCount()
- Returns the number of fields in a result set for a
SELECT
query. LastInsertID()
- Returns the last insert id of an
AUTO_INCREMENT
column generated by anINSERT
query. RowCount()
- Returns the number of rows in a result set for a
SELECT
query. If buffering is disabled for the prepared statement then this will return 0. RowsAffected()
- Returns the number of rows affected by an
UPDATE
,DELETE
, orINSERT
query.
mysql::StmtRow¶
Provides a non-owning view of column values in a result set. This object overrides operator bool()
to evaluate as false if it does not contain a fetched row.
ColumnCount()
- Returns number of fields in the row.
GetColumn<T>(size_t index)
GetColumn<T>(std::string_view name)
- Returns a
mysql::StmtColumn
pointer to the column. Returnsnullptr
if the column doesn't exist. Get<T>(size_t index)
Get<T>(std::string_view name)
- Returns a
std::optional<T>
with the column value as a numericT
. Returnsstd::nullopt
if the column doesn't exist, the value is NULL, or the field type is unsupported. SeeStmtColumn::Get<T>()
. GetStr(size_t index)
GetStr(std::string_view name)
- Returns a
std::optional<std::string>
with the column value as a string. Returnsstd::nullopt
if the column doesn't exist, the value is NULL, or the field type is unsupported. SeeStmtColumn::GetStr()
.
mysql::StmtColumn¶
Stores a column value buffer.
Get<T>()
-
Returns a
std::optional<T>
with the column value as a numericT
. Returnsstd::nullopt
if the column value isNULL
or the field type is unsupported.Warning
This will truncate number values to the specified
T
type so the caller is responsible for requesting the proper type based on database field type.Time and Date fields are converted to
time_t
before casting to the desiredT
type.Bit fields are converted to
uint64_t
before casting to the desiredT
type.If
T
isbool
then string field types returnfalse
for empty strings andtrue
otherwise.If
T
is notbool
then string field types return0
(T
is zero-initialized) if the string cannot be converted to a number. GetBuf()
- Returns a
std::span<const uint8_t>
view of the column value buffer. This may be used to get the raw bytes and length of the value without any conversions. GetStr()
-
Returns a
std::optional<std::string>
with the column value as a string. Returnsstd::nullopt
if the column value isNULL
or the field type is unsupported.Time and Date fields are formatted as strings specified in the MySQL C API.
Bit fields are formatted as a
uint64_t
converted to a string. GetStrView()
- Returns a
std::optional<std::string_view>
view of the column string value. Returnsstd::nullopt
if the column value isNULL
or not a string. This may be used to avoid making a copy of strings if callers do not need to store it. Index()
- Returns the index of the column in the result set.
IsNull()
- Returns bool specifying if the column value is
NULL
. IsUnsigned()
- Returns bool specifying if the column field type is unsigned.
Name()
- Returns the field name.
Type()
- Returns the
buffer_type
of the field.