SELECT resultsx::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)) { // ... }
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.