The dynamic interface

To use the dynamic interface, use the results class. results is an in-memory representation of a resultset. We can depict it like this (this is actually a simplified representation, since some statements may return more than one resultset).

We can see that results::rows returns a matrix-like object, containing the retrieved rows. This section is dedicated on diving deeper on how to use these objects.

Rows and fields

This matrix-like structure is composed of variant-like objects called fields. Field objects are capable of representing any value retrieved from MySQL.

This library defines the following classes to work with rows and fields:

field: The smallest unit of data. A single "cell" in a MySQL table. This is an owning, variant-like type.
field_view: Like field, but non-owning.
row: An owning, vector-like collection of fields.
row_view: Like row, but non-owning.
rows: An owning, matrix-like collection of fields. Represents several rows of the same size in an optimized way.
rows_view: Like rows, but non-owning.

results::rows returns a rows_view object. The memory for the rows is owned by the results object. Indexing the returned view also returns view objects:

// Populate a results object
results result;
conn.execute("SELECT 'Hello world'", result);

// results::rows() returns a rows_view. The underlying memory is owned by the results object
rows_view all_rows = result.rows();

// Indexing a rows_view yields a row_view. The underlying memory is owned by the results object
row_view first_row = all_rows.at(0);

// Indexing a row_view yields a field_view. The underlying memory is owned by the results object
field_view first_field = first_row.at(0);  // Contains the string "Hello world"

Views behave similarly to std::string_view. You must make sure that you don't use a view after the storage it points to has gone out of scope. In this case, you must not use any of the views after the results object has gone out of scope.

As it happens with std::string_view, you can take ownership of a view using its owning counterpart:

// You may use all_rows_owning after result has gone out of scope
rows all_rows_owning{all_rows};

// You may use first_row_owning after result has gone out of scope
row first_row_owning{first_row};

// You may use first_field_owning after result has gone out of scope
field first_field_owning{first_field};

Using fields

field and field_view are specialized variant-like types that can hold any type you may find in a MySQL table. Once you obtain a field, you can access its contents using the following functions:

You can query a field's type by using kind, which returns a field_kind enum.
You can query whether a field contains a certain type with field::is_xxx.
You can get the underlying value with field::as_xxx and field::get_xxx. The as_xxx functions are checked (they will throw an exception if the actual type doesn't match), while the get_xxx are unchecked (they result in undefined behavior on type mismatch).
You can stream fields and compare them for equality.

For example:

results result;
conn.execute("SELECT 'abc', 42", result);

// Obtain a field's underlying value using the is_xxx and get_xxx accessors
field_view f = result.rows().at(0).at(0);  // f points to the string "abc"
if (f.is_string())
{
    // we know it's a string, unchecked access
    string_view s = f.get_string();
    std::cout << s << std::endl;  // Use the string as required
}
else
{
    // Oops, something went wrong - schema mismatch?
}

// Alternative: use the as_xxx accessor
f = result.rows().at(0).at(1);
std::int64_t value = f.as_int64();  // Checked access. Throws if f doesn't contain an int
std::cout << value << std::endl;    // Use the int as required

NULL values are represented as field objects having kind() == field_kind::null. You can check whether a value is NULL or not using is_null. This is how NULLs are typically handled:

results result;

// Create some test data
conn.execute(
    R"%(
        CREATE TEMPORARY TABLE products (
            id VARCHAR(50) PRIMARY KEY,
            description VARCHAR(256)
        )
    )%",
    result
);
conn.execute("INSERT INTO products VALUES ('PTT', 'Potatoes'), ('CAR', NULL)", result);

// Retrieve the data. Note that some fields are NULL
conn.execute("SELECT id, description FROM products", result);

for (row_view r : result.rows())
{
    field_view description_fv = r.at(1);
    if (description_fv.is_null())
    {
        // Handle the NULL value
        // Note: description_fv.is_string() will return false here; NULL is represented as a separate
        // type
        std::cout << "No description for product_id " << r.at(0) << std::endl;
    }
    else
    {
        // Handle the non-NULL case. Get the underlying value and use it as you want
        // If there is any schema mismatch (and description was not defined as VARCHAR), this will
        // throw
        string_view description = description_fv.as_string();

        // Use description as required
        std::cout << "product_id " << r.at(0) << ": " << description << std::endl;
    }
}

MySQL to C++ type mappings

Every MySQL type is mapped to a single C++ type. The following table shows these mappings:

`field_kind`	C++ type	MySQL types	`is` accessor	`as` accessor	`get` accessor
`int64`	`std::int64_t`	`TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `BIGINT`	`is_int64`	`as_int64`	`get_int64`
`uint64`	`std::uint64_t`	Unsigned `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `BIGINT`, `YEAR`, `BIT`	`is_uint64`	`as_uint64`	`get_uint64`
`string`	`string_view` for `field_view` `std::string` for `field`	`CHAR`, `VARCHAR`, `TEXT` (all sizes), `ENUM`, `SET`, `DECIMAL`, `NUMERIC`, `JSON`	`is_string`	`as_string`	`get_string`
`blob`	`blob_view` for `field_view` `blob` for `field`	`BINARY`, `VARBINARY`, `BLOB` (all sizes), `GEOMETRY`	`is_blob`	`as_blob`	`get_blob`
`float_`	`float`	`FLOAT`	`is_float`	`as_float`	`get_float`
`double_`	`double`	`DOUBLE`	`is_double`	`as_double`	`get_double`
`date`	`date`	`DATE`	`is_date`	`as_date`	`get_date`
`datetime`	`datetime`	`DATETIME`, `TIMESTAMP`	`is_datetime`	`as_datetime`	`get_datetime`
`time`	`time`	`TIME`	`is_time`	`as_time`	`get_time`
`null`		Any of the above, when they're `NULL`	`is_null`

No character set conversion is applied on strings. They are provided as the server sends them. If you've issued a "SET NAMES <charset-name>" statement, strings will be encoded according to <charset-name>. For details, see this section.

The field class

field_view is to field what std::string_view is to std::string.

field_views are cheap to create and to copy, as they are small objects and don't perform any memory allocations. They are also immutable. On the other hand, fields may be more expensive to create and copy, as they may perform memory allocations. fields are mutable.

field and field_view use the same underlying types for scalars. For strings and blobs, field uses the owning types std::string and blob, while field_view uses the reference types string_view and blob_view.

field accessors return references, which allow you to mutate the underlying object:

field f("my_string");            // constructs a field that owns the string "my_string"
std::string& s = f.as_string();  // s points into f's storage
s.push_back('2');                // f now holds "my_string2"

You can also mutate a field using the assignment operator. This allows you to also change the underlying type of a field:

field f("my_string");  // constructs a field that owns the string "my_string"
f = 42;                // destroys "my_string" and stores the value 42 as an int64

Multi-resultset and multi-function operations

You can use both with the dynamic interface. Please refer to the sections on multi-resultset operations and multi-function operations for more information.

MySQL to C++ type mapping reference

The following table reflects mapping from database types to C++ types. The range column shows the range of values that MySQL admits for that type. This library guarantees that any field retrieved from the database honors that range. The column_type column shows what metadata::type would return for a column of that type.

MySQL type	`field_kind`	C++ type	Range	`column_type`	Considerations
`TINYINT`	`int64`	`std::int64_t`	`-0x80` to `0x7f`	`tinyint`	1 byte integer
`TINYINT` `UNSIGNED`	`uint64`	`std::uint64_t`	`0` to `0xff`	`tinyint`	1 byte integer
`SMALLINT`	`int64`	`std::int64_t`	`-0x8000` to `0x7fff`	`smallint`	2 byte integer
`SMALLINT` `UNSIGNED`	`uint64`	`std::uint64_t`	`0` to `0xffff`	`smallint`	2 byte integer
`MEDIUMINT`	`int64`	`std::int64_t`	`-0x800000` to `0x7fffff`	`mediumint`	3 byte integer
`MEDIUMINT` `UNSIGNED`	`uint64`	`std::uint64_t`	`0` to `0xffffff`	`mediumint`	3 byte integer
`INT`	`int64`	`std::int64_t`	`-0x80000000` to `0x7fffffff`	`int_`	4 byte integer
`INT` `UNSIGNED`	`uint64`	`std::uint64_t`	`0` to `0xffffffff`	`int_`	4 byte integer
`BIGINT`	`int64`	`std::int64_t`	`-0x8000000000000000` to `0x7fffffffffffffff`	`bigint`	8 byte integer
`BIGINT` `UNSIGNED`	`uint64`	`std::uint64_t`	`0` and `0xffffffffffffffff`	`bigint`	8 byte integer
`YEAR`	`uint64`	`std::uint64_t`	[`1901`, `2155`], plus zero	`year`	1 byte integer type used to represent years Zero is often employed to represent invalid year values. We represent zero year as a numeric 0.
`BIT`	`uint64`	`std::uint64_t`	Depends on the bitset width. Max `0` to `0xffffffffffffffff`.	`bit`	A bitset between 1 and 64 bits wide.
`FLOAT`	`float_`	`float`	IEEE 754 `float` range	`float_`	4 byte floating point type
`DOUBLE`	`double_`	`double`	IEEE 754 `double` range	`double_`	8 byte floating point type
`DATE`	`date`	`date`	[`min_date`, `max_date`] (some MySQL implementations may allow a narrower range), plus invalid and zero dates (see `ALLOW_INVALID_DATES` and strict SQL mode).	`date`
`DATETIME`	`datetime`	`datetime`	[`min_datetime`, `max_datetime`] (some MySQL implementations may allow a narrower range), plus invalid and zero datetimes (see `ALLOW_INVALID_DATES` and strict SQL mode).	`datetime`	Time point type without time zone, with a resolution of one microsecond.
`TIMESTAMP`	`datetime`	`datetime`	[`min_datetime`, `max_datetime`] (the actual MySQL supported range is usually narrower, but we don't enforce it in the client), plus zero timestamps (see strict SQL mode).	`timestamp`	Time point type with a resolution of one microsecond.
`TIME`	`time`	`time`	[`min_time`, `max_time`]	`time`	Signed time duration, with a resolution of one microsecond.
`CHAR`	`string`	`string_view` or `std::string`		`char_`	Fixed-size character string.
`VARCHAR`	`string`	`string_view` or `std::string`		`varchar`	Variable size character string with a maximum size.
`TEXT` (all sizes)	`string`	`string_view` or `std::string`		`text`	Variable size character string.
`ENUM`	`string`	`string_view` or `std::string`		`enum_`	Character string with a fixed set of possible values (only one possible).
`SET`	`string`	`string_view` or `std::string`		`set`	Character string with a fixed set of possible values (many possible).
`JSON`	`string`	`string_view` or `std::string`		`json` (MySQL) or `text` (MariaDB)	A serialized JSON value of any type. Note that `metadata::type` is different depending on the DB system. MySQL has a dedicated `JSON` type, while in MariaDB `JSON` is an alias for `LONGTEXT`. JSON values are represented as strings by this library in both cases.
`DECIMAL`/`NUMERIC`	`string`	`string_view` or `std::string`	Depends on the column definition	`decimal`	A fixed precision numeric value. In this case, the string will contain the textual representation of the number (e.g. the string `"20.52"` for `20.52`). This type is mapped to a string to avoid losing precision.
`BINARY`	`blob`	`blob_view` or `blob`		`binary`	Fixed-size blob.
`VARBINARY`	`blob`	`blob_view` or `blob`		`varbinary`	Variable size blob with a maximum size.
`BLOB` (all sizes)	`blob`	`blob_view` or `blob`		`blob`	Variable size blob.
`GEOMETRY`	`blob`	`blob_view` or `blob`		`geometry`	Any of the spatial data types. The string contains the binary representation of the geometry type.