Fetching SELECT results

x::sql::statement stmt=
    conn->execute("SELECT name, price FROM books");

std::string name;
std::pair<float, x::sql::bitflag> price;

while (stmt->fetch("name", name, "price", price))
{
    if (price.second)
    {
       // ... price is NULL
    }
    else
    {
       // price.first is the price
    }

    // ...
}

fetch() retrieves the next row of a SELECT resultset, returning true; or false when there are no more rows. fetch()'s parameters specify the columns to retrieve. Specify the name of each column, and either an lvalue for the column's value, or an lvalue of a std::pair with the column's value going into the pair's first value, and a x::sql::bitflag in the pair's second value, which gets set to a non-zero value> when the column's value is NULL.

The column value lvalue types can be a mixture of lvalues to different types: natural integer types, floats, doubles, x::ymd and x::hms (which are converted to equivalent SQL data types), and std::strings, for character text columns. "Bit" data type, if supported by the database, get fetched into a x::sql::bitflag. Column values can also be mpf_class values from the GMP library.

The columns can be identified by column number instead of a name. This is slightly faster:

while (stmt->fetch(0, name, 1, price))
{
    // ...
}

Note

Column numbers are 0-based. The first column in the resultset is column #0.

const std::vector<x::sql::statement::base::column> &columns=stmt->get_columns();

for (const x::sql::statement::base::column : columns)
{
    std::cout << column.name << std::endl;
}

get_columns() returns a vector of x::sql::statement::base::columns, which describe each column in the statement's resultset. Instead of explicitly enumerating each column in the parameters for fetch(), this allows the list of parameters to be supplied indirectly:

const auto &columns=stmt->get_columnmap();

std::map<std::string, std::pair<std::string, x::sql::bitflag>> cols;

for (const auto &col:columns)
{
    cols.insert(std::make_pair(col.first, std::make_pair("", 0)));
}

while (tables->fetch(table))
{
    // ...
}

get_columnmap() is an alternative to get_columns() which returns an x::sql::statement::base::columnmap_t, which is a std::map keyed by case-insensitive column.name, with the value being x::sql::statement::base::column (a reference to one, actually). This is convenient for accessing column metadata by name.

Passing a std::map parameter to fetch() results in fetch() returning columns identified by the std::map's keys. The map can be keyed by std::strings (identifying columns by their name) or by size_ts (identifying columns by their 0-based column numbers).

The map's value must be a std::pair. The value of each column goes into the first pair value, for non-NULL values, with the second pair value, a x::sql::bitflag set to zero. A non zero x::sql::bitflag specifies NULL values.

The above example fetches all columns into std::strings. All values in a map, of course, must be of the same type, however fetch() accepts maps together with explicit columns, or other maps:

std::map<int, std::pair<std::string, x::sql::bitflag>> titledesc;

std::map<std::string, std::pair<float, x::sql::bitflag>> prices;

std::pair<int, x::sql::bitflag> instock;

titledesc.insert({0, {"", 0}});
titledesc.insert({1, {"", 0}});

prices.insert({"price", {0, 0}});
prices.insert({"discount", {0, 0}});

while (tables->fetch(titledesc, prices, "instock", instock))
{
    // ...
}

This examples fetches the first two columns in the resultset by column number, columns 0 and 1, as character strings; the price and discount fields as floats, and an integer instock field, which may be NULL.