Bulk SELECT fetches

fetch() retrieves one row from a SELECT resultset at a time. This is fine for small queries, but each call to fetch() involves non-trivial amount of overhead, that's not very efficient when processing large results. Using fetch_vectors() is more effective with large resultsets. fetch_vectors() implements fetching of multi-row resultsets.

auto stmt=conn->execute("SELECT int_col, varchar_col, FROM tmptbl1 ORDER BY int_col");

std::pair<std::vector<int>, std::vector<x::sql::bitflag>> n;
std::pair<std::vector<std::string>, std::vector<x::sql::bitflag>> str;

size_t c;

while ((c=stmt->fetch_vectors(1000, 0, n, 1, str)) > 0)
{
    for (size_t i=0; i<c; ++i)
    {
        // ...
    }
}

fetch_vectors() differs from fetch() as follows.

The vectors specify the value for their corresponding columns, in each row. They are column vectos. Indirectly specifying columns with fetch_vectors() is similar how its done with fetch(), except that the map value is a std::pair of vectors:

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

// Side effect of operator[] is to insert these columns, that's
// what we want here:

auto &int_col=columns["int_col"];
auto &varchar_col=columns["varchar_col"];

size_t c;

while ((c=stmt->fetch_vectors(1000, columns) > 0)
{
    std::vector<int> &n=int_col.first;
    std::vector<x::sql::bitflag> &n_null=int_col.second;
    std::vector<std::string> &str=varchar_col.first;
    std::vector<x::sql::bitflag> &str_null=varchar_col.second;

    for (size_t i=0; i<c; ++i)
    {
        // If n_null[i] or str_null[i], the corresponding value is null,
        // otherwise see n[i] and str[i]
    }
}

fetch_vectors() is better than with fetch() for large resultsets, and fetch_vectors_all() is better than fetch_vectors():

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

// Side effect of operator[] is to insert these columns, that's
// what we want here:

auto &int_col=columns["int_col"];
auto &varchar_col=columns["varchar_col"];

stmt->fetch_vectors_all(1000,
    [&]
    (size_t c)
    {
        std::vector<int> &n=int_col.first;
        std::vector<x::sql::bitflag> &n_null=int_col.second;
        std::vector<std::string> &str=varchar_col.first;
        std::vector<x::sql::bitflag> &str_null=varchar_col.second;

        for (size_t i=0; i<c; ++i)
        {
           // If n_null[i] or str_null[i], the corresponding value is null,
           // otherwise see n[i] and str[i]
        }
   }, columns);

fetch_vectors_all() takes an additional lambda/functor parameter after the row size, and before the column parameters. fetch_vectors_all() is equivalent to repeatedly calling fetch_vectors() until it returns 0, and invoking the lambda/functor each time, passing the number of retrieved rows as its sole parameter.

The lambda/functor presumably captures the returned value vectors by reference, however the lambda/functor must not modify the value vectors, it can only read them. Modifying the actual values in the vectors is fine, if necessary, just that the vectors themselves cannot be resized, cleared, etc... fetch_vectors_all() returns the entire resultset, in chunks, with a minimum of overhead. All requested columns are tied to the given vector buffers once, and are not rebound with every fetch().

The lambda/functor is allowed to throw an exception, to abort processing of the resultset.

Note

The underlying ODBC API is a C API. Full buffers must be allocated for each fetched value. Retrieving 1000 rows of a VARCHAR(100) column requires allocating char[100*1000] worth of memory. Afterwards, 1000 values gets copied to the 1000 std::strings, which uses additional memory.

The char[100*1000] buffer gets freed only after the x::sql::statement goes out of scope and gets destroyed, or when another call to one of the fetch methods gets made. All buffers used by a previous fetch call get freed before allocating buffers for the next fetch.

The temporary buffers are needed only for C++ specific value types, namely std::strings and bool resultset values. Natural numeric values — ints, floats, and other C-compatible types — do not require intermediate buffers.