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

PrevUpHomeNext

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:

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.


PrevUpHomeNext