5
\$\begingroup\$

I created it to map buffers returned by ODBC driver to my objects directly without creating DataSet like storage. It works quite nice, but I have some questions. UPD: I resoled those questions, just sharing code.

For those who unfamiliar with ODBC: after calling to SQLFetch driver fills buffers specified by SQLBindCol and NULL indicator in *Length field in batch of ROW_ARRAY_SIZE. You can ignore parts related to ODBC.

The main idea behind this code is to minimize number of API calls, because profiler shows significant amount of time is spent in intermediate layers (not waiting for IO). Сurrent code is an order of magnitude faster than rebinding (each row) directly to std::string buffer, and around twice as fast than SQLGetData approach.

//-----
template<class... Ts> struct Visitor: Ts... { using Ts::operator()...; }; // for std::visit
template<class... Ts> Visitor(Ts...)->Visitor<Ts...>; // deduction guide for Visitor
//-----

template <typename T, typename V, SQLULEN ROW_ARRAY_SIZE = 100>
class OdbcBinder final
{
private:
    using pll = std::tuple<long T::*, SQLLEN T::*, long V::*>;
    using pcs = std::tuple<char(T::*)[], SQLLEN T::*, std::string V::*, size_t>;
    using pts = std::tuple<SQL_TIMESTAMP_STRUCT T::*, SQLLEN T::*, std::string V::*>;
    using mapvar = std::variant<pll, pcs, pts>;

    SQLUSMALLINT N = 0;
    Odbc& m_odbc;
    const autoHSTMT stmt;
    // TODO: use std::span, remove ROW_ARRAY_SIZE
    const std::unique_ptr<std::array<T, ROW_ARRAY_SIZE>> in;

    std::vector<mapvar> mappings;

public:
    explicit OdbcBinder(Odbc& odbc):
        m_odbc(odbc),
        stmt(odbc.GetHstmt()),
        in(std::make_unique<std::array<T, ROW_ARRAY_SIZE>>())
    {}

    template <std::size_t ArrSize>
    void BindCol(char(T::* ptrin)[ArrSize], SQLLEN T::* ptrinLen, std::string V::* ptrout)
    {
        static_assert(sizeof(ptrin) == sizeof(char(T::*)[100]) && alignof (decltype(ptrin)) == alignof(char(T::*)[100]));
        // no decay to unbound member array. TODO: Resolved in C++ 20
        BindCol(reinterpret_cast<char (T::*)[]>(ptrin), ArrSize, ptrinLen, ptrout);
    }

    // non template version (to reduce code size)
    void BindCol(char (T::* ptrin)[], size_t ArrSize, SQLLEN T::* ptrinLen, std::string V::* ptrout)
    {
        mappings.emplace_back(std::make_tuple(ptrin, ptrinLen, ptrout, ArrSize));

        SQLRETURN rc = SQLBindCol(stmt, ++N, SQL_C_CHAR, &((*in)[0].*ptrin), ArrSize, &((*in)[0].*ptrinLen));
        if (rc != SQL_SUCCESS)
            m_odbc.LogLastError(stmt);
    }

    void BindCol(SQL_TIMESTAMP_STRUCT T::* ptrin, SQLLEN T::* ptrinLen, std::string V::* ptrout)
    {
        mappings.emplace_back(std::make_tuple(ptrin, ptrinLen, ptrout));

        SQLRETURN rc = SQLBindCol(stmt, ++N, SQL_C_TIMESTAMP, &((*in)[0].*ptrin), sizeof((*in)[0].*ptrin), &((*in)[0].*ptrinLen));
        if (rc != SQL_SUCCESS)
            m_odbc.LogLastError(stmt);
    }

    void BindCol(long T::* ptrin, SQLLEN T::* ptrinLen, long V::* ptrout)
    {
        mappings.emplace_back(std::make_tuple(ptrin, ptrinLen, ptrout));

        SQLRETURN rc = SQLBindCol(stmt, ++N, SQL_C_LONG, &((*in)[0].*ptrin), sizeof((*in)[0].*ptrin), &((*in)[0].*ptrinLen));
        if (rc != SQL_SUCCESS)
            m_odbc.LogLastError(stmt);
    }

    [[nodiscard]]
    std::vector<V> GetResult(std::string_view sql, SQLULEN limit = 0)
    {
        return useOracleWorkaround() ? GetResult<SQLLEN>(sql, limit) : GetResult<SQLUSMALLINT>(sql, limit);
    }

private:
    bool useOracleWorkaround() const
    {
        if (m_odbc.Lang() == Odbc::DbType::Oracle)
        {
#ifdef _WIN64
            return true;
#else
            if (m_odbc.driverVersion < 12)
                return true;
#endif // _WIN64
        }
        return false;
    }

    // @param limit fetch only N rows, 0 - disable
    template<typename OracleWorkaroundType>
    [[nodiscard]]
    std::vector<V> GetResult(std::string_view sql, SQLULEN limit = 0) const
    {
        SQLULEN numRowsFetched = 0;
        // MSSQL conforms to standard and use SQLUSMALLINT (2 byte)
        // Oracle 11 x32 driver writes 4 byte array
        // Oracle 12+ x32 are conforming
        // Oracle x64 drivers writes 8 byte
        //SQLUSMALLINT rowStatusArray[ROW_ARRAY_SIZE];
        OracleWorkaroundType rowStatusArray[ROW_ARRAY_SIZE]{};

        SQLRETURN rc = SQL_ERROR;

        rc = SQLSetStmtAttr(stmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)ROW_ARRAY_SIZE, SQL_IS_UINTEGER); _ASSERT(rc == 0);
        rc = SQLSetStmtAttr(stmt, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER)sizeof(T), SQL_IS_UINTEGER); _ASSERT(rc == 0);
        rc = SQLSetStmtAttr(stmt, SQL_ATTR_ROW_STATUS_PTR, rowStatusArray, SQL_IS_POINTER); _ASSERT(rc == 0);
        rc = SQLSetStmtAttr(stmt, SQL_ATTR_ROWS_FETCHED_PTR, &numRowsFetched, SQL_IS_POINTER); _ASSERT(rc == 0);

        if (limit != 0)
        {
            rc = SQLSetStmtAttr(stmt, SQL_ATTR_MAX_ROWS, (SQLPOINTER)limit, SQL_IS_UINTEGER); _ASSERT(rc == 0);
            if (rc != SQL_SUCCESS)
                m_odbc.LogLastError(stmt);
        }

        std::vector<V> results;

        rc = SQLExecDirect(stmt, (SQLCHAR*)sql.data(), static_cast<SQLINTEGER>(sql.size()));
        if (!SQL_SUCCEEDED(rc) && rc != SQL_NO_DATA)
        {
            m_odbc.LogLastError(stmt);

            return results;
        }

        results.reserve(limit != 0 ? limit : ROW_ARRAY_SIZE / 4);

        do
        {
            while (SQL_SUCCEEDED(rc = SQLFetch(stmt)))
            {
                if (rc == SQL_SUCCESS_WITH_INFO)
                    m_odbc.LogLastError(stmt);

                for (SQLUINTEGER i = 0; i < numRowsFetched; ++i)
                {
                    if (rowStatusArray[i] != SQL_ROW_SUCCESS &&
                        rowStatusArray[i] != SQL_ROW_SUCCESS_WITH_INFO)
                        continue;

                    V value;
                    const T& info = (*in)[i];

                    const auto visitor = Visitor{
                        [&](const pll& tup)
                        {
                            const auto& [ptrin, ptrinLen, ptrout] = tup;
                            if (info.*ptrinLen != SQL_NULL_DATA)
                                value.*ptrout = info.*ptrin;
                            else
                                value.*ptrout = 0;
                        },
                        [&](const pcs& tup)
                        {
                            const auto& [ptrin, ptrinLen, ptrout, size] = tup;
                            _ASSERTE(/*bufferLength*/size - 1 >= /*gotlength*/size_t(info.*ptrinLen) || /*is null*/info.*ptrinLen == SQL_NULL_DATA);

                            switch (info.*ptrinLen)
                            {
                            case SQL_NULL_DATA:
                                (value.*ptrout).clear();
                                break;
                            case SQL_NO_TOTAL: // should not happen
                                _ASSERTE(SQL_NO_TOTAL, false);
                                (value.*ptrout).assign(info.*ptrin, size - 1);
                                break;
                            default:
                                // info.*ptrinLen the length of the data before truncation because of the data buffer being too small
                                (value.*ptrout).assign(info.*ptrin, std::clamp<SQLLEN>(info.*ptrinLen, 0, size - 1));
                                break;
                            }
                        },
                        [&](const pts& tup)
                        {
                            const auto& [ptrin, ptrinLen, ptrout] = tup;
                            if (info.*ptrinLen != SQL_NULL_DATA)
                                value.*ptrout = TimestampToString(info.*ptrin);
                            else
                                (value.*ptrout).clear();
                        }
                    };

                    for (auto&& v : mappings)
                        std::visit(visitor, v);

                    results.push_back(std::move(value));
                }
            }
            if (rc != SQL_NO_DATA)
            {
                m_odbc.LogLastError(stmt);
                // no partial results
                results.clear();
                return results;
            }
        } while (SQL_SUCCEEDED(rc = SQLMoreResults(stmt)));

        if (rc != SQL_NO_DATA)
        {
            m_odbc.LogLastError(stmt);
            // no partial results
            results.clear();
        }

        return results;
    }

public:

    OdbcBinder(const OdbcBinder&) = delete; // copy ctr
    OdbcBinder(OdbcBinder&&) noexcept = delete; // move ctr
    OdbcBinder& operator=(const OdbcBinder&) = delete; // copy asign
    OdbcBinder& operator=(OdbcBinder&&) noexcept = delete; // move asign
};

Usage:

struct INCIDENT_INFO {
    long id;
    long idLength;
    char comments[1024];
    long commentsLength;
    /* ... */
    SQL_TIMESTAMP_STRUCT timeSend;
    long timeSendLength;
};
    
class Incident {
    long id;
    std::string comments;
    /* ... */
    std::string timeSend;
};

int main()
{
    // initialize connection
    auto odbc = <...>;
    OdbcBinder<INCIDENT_INFO, Incident> binder(odbc);

    // map by convention
    //#define BINDER_MAP(field_name, entity_class, buffer_class) & buffer_class :: field_name, & buffer_class :: field_name ## Length, & entity_class :: field_name
    //binder.BindCol(BINDER_MAP(id, Incident, INCIDENT_INFO)); // ID - 0

    binder.BindCol(&INCIDENT_INFO::id, &INCIDENT_INFO::idLength, &Incident::id); // ID - 0
    binder.BindCol(&INCIDENT_INFO::comments, &INCIDENT_INFO::commentsLength, &Incident::comments); // COMMENTS - 1
    /* .... */
    binder.BindCol(&INCIDENT_INFO::timeSend, &INCIDENT_INFO::timeSendLength, &Incident::timeSend); // TIME_SEND - 31
    
    std::vector<Incident> v = binder.GetResult("select ID, COMMENTS, " /* .... */ "TIME_SEND from INCIDENTS");
}
\$\endgroup\$
2
2
\$\begingroup\$

This is a separate answer. In the first one, I focused on simplifying the API, but as was pointed out, calling SQLGetData() for all the columns in all the rows is slowing things down. In this answer I also want to show a way to simplify things, but this time it's just making things easier for the user of your code.

Hide the DTO from the user of your class

In your code, the caller is responsible for providing two classes; one being the DTO that holds the output generated by the ODBC library, and the second being the class that you are actually interested in. It would be great if the caller would not have to deal with the DTO, so ideally you can just write:

class Incident {
    long id;
    std::string comments;
    /* ... */
    std::string timeSend;
};

int main()
{
    auto odbc = ...;
    OdbcBinder<Incident> binder(odbc,
        &Incident::id,
        &Incident::comments,
        ...
        &Incident::timeSend
    );
    
    std::vector<Incident> v = binder.GetResult(...);
}

The idea being that the constructor of OdbcBinder sees all the columns you want to bind up front, so it can create a DTO type. It will have to look like this:

template <typename V, typename... ColTypes>
class OdbcBinder final {
    Odbc& m_odbc;
    std::tuple<ColTypes V::*...> m_columns;

    using T = std::tuple<typename DTOType<ColTypes>::type...>;
    std::vector<T> in(ROW_ARRAY_SIZE);
    ...
    OdbcBinder(Odbc& odbc, ColTypes V::*... columns):
        m_odbc(odbc), m_columns(columns...)
    {
        // Bind columns
        ...
    }
};

Amazingly enough, in C++17 and later, the compiler can even deduce all template parameter types from that constructor. To create a tuple that will act as the DTO type we need to map from C++ column types to what ODBC expects. The helper template DTOType is there to do that:

template<typename> struct DTOType;

template<> struct DTOType<long> {
    using type = std::pair<long, SQLLEN>;
    static constexpr SQLSMALLINT sql_type = SQL_C_LONG;
    static long toValue(const type &in) {
        return in.first;
    }
};

template<> struct DTOType<std::string> {
    using type = std::pair<char[1024], SQLLEN>;
    static constexpr SQLSMALLINT sql_type = SQL_C_CHAR;
    static std::string toValue(const type *in) {
        return {in.first, in.second - 1};
    }
};

...

The difficulty is now when you have something like the timestamps. Either ensure you use a distinct C++ type for them, like std::chrono::time_point, or you have to find some other way to convey the desired conversion (I'll ignore this issue for now).

Now that the column types are part of the class type, all member functions have access to them, so we no longer need to use a vector of variants to store the column type information. We still need to tell ODBC how to bind the columns though, and that's why there still is a BindCol() that we call in the constructor. Now we have to loop over the elements of the tuple T and the member-to-pointers that were passed to the constructor. We can do that like so:

OdbcBinder(Odbc& odbc, ColTypes V::*... columns):
    m_odbc(odbc), m_columns(columns...)
{
    std::apply([&](auto&... dto_columns) {
        (BindCol(dto_columns, columns) && ...);
    }, in[0]);
}

Here we use std::apply() on the first element of the array in, which is a tuple, so we can use a lambda to access each element of the tuple in a fold expression. This fold expression references both the parameter pack in dto_columns as well as the parameter pack columns from the constructor itself, which have the same size. We pass the column from the DTO type as well as the pointer-to-member of the final type to BindCol(), which looks like this:

template<typename DTOColumn, typename ColType>
bool BindCol(DTOColumn &dto_column, ColType V::* column) {
    SQLRETURN rc = SQLBindCol(stmt, ++N,
                              DTOType<ColType>::sql_type,
                              &dto_column.first,
                              sizeof dto_column.first,
                              &dto_column.second);
    return rc == SQL_SUCCESS;
}

Now that we have done the SQLBindCol() statements, we can do a query and fetch the results. To handle the final conversion from the DTO to the desired C++ object we do a similar trick. First, let's simplify GetResult() a bit:

std::vector<V> GetResult(std::string_view sql) {
    ...
    std::vector<V> results;
    rc = SQLExecDirect(stmt, (SQLCHAR*)sql.data(), static_cast<SQLINTEGER>(sql.size()));
    ...
    for (SQLUINTEGER i = 0; i < numRowsFetched; ++i) {
        auto is = std::make_index_sequence<std::tuple_size_v<T>>;
        results.push_back(Convert(in[i], is));
    }
    ...
}

Now we can do the conversion of a single DTO object in this function:

template<std::size_t... I>
V Convert(const T& t, std::index_sequence<I...>) {
    V value;
    (value::*std::get<I>(m_columns) = DTOType<ColTypes>::toValue(std::get<I>(t)), ...);
    return v;
}

I used a std::index_sequence here to help iterate over the tuple t as well as the tuple m_columns simultaneously, as std::apply() only takes one tuple as an argument.

This also very template-heavy, but adding new conversion types should just require adding another variant of struct DTOType.

\$\endgroup\$
7
  • \$\begingroup\$ std::tuple<long, long> is neither std::is_standard_layout nor std::is_trivial, is it ok? \$\endgroup\$ Nov 5 '21 at 16:25
  • \$\begingroup\$ That should not be a problem. The only issue might be that you might lose a bit of performance if you plan to resize the vector in often, which might require moving it in memory. \$\endgroup\$
    – G. Sliepen
    Nov 5 '21 at 16:38
  • \$\begingroup\$ I updated code in question \$\endgroup\$ Nov 16 '21 at 18:02
  • \$\begingroup\$ I have rolled back that last update per site policy \$\endgroup\$ Nov 16 '21 at 18:19
  • \$\begingroup\$ here is resulting code pastebin.com/MNU5K7P9 \$\endgroup\$ Nov 17 '21 at 17:10
2
\$\begingroup\$

Keep it simple

There is a lot going on in your code, making heavy use of templates, variant types and pointers-to-members. This is hard to read and understand, and while you might have everything fresh in memory now, if you have to come back to this code after half a year you will probably have a hard time yourself trying to figure out how it works again. So while the thing you have implemented is clearly possible, it might be worthwhile to explore if there are simpler approaches to this problem.

As you mentioned:

I need to map between 3 buffers {ODBC internal buffer}->{DTO with in place buffer}->{Entity}

The idea behind SQLBindCol() is that it automates going from the ODBC internal buffer to the Data Transfer Object (DTO) format. However, the DTO supported is just a plain old C struct, and you want to have the results in a C++ class with std::strings and perhaps even other non-C types. The drawback of SQLBindCol() is that you have to call it before calling SQLFetch(), and then you have to convert from the DTO to your C++ objects. It would help if we could skip the intermediate DTO step, but unfortunately that is not possible.

Alternatively, if there is some way to do all the conversion after fetching, we can do the conversions all in one place. That is in fact possible by using SQLGetData(). Your code wouldn't be calling SQLBindCol() at all, and GetResult() would be changed to look like this:

rc = SQLExecDirect(hstmt, (SQLCHAR*)sql.data(), static_cast<SQLINTEGER>(sql.size()));
...
std::vector<V> results;

while (SQL_SUCCEEDED(rc = SQLFetch(stmt)) {
    ...
    results.push_back(mapper(stmt));
}

Where the function mapper() could look like this:

Incident mapper(const autoHSTMT &stmt) {
    Incident value;
    value.id = GetLong(stmt, 1);
    value.comments = GetString(stmt, 2);
    ...
    value.timeSend = GetTimestamp(stmt, 3);
    return value;
}

And this in turn uses helper functions to convert a single value. For example, to convert a timestamp:

std::string GetTimestamp(const autoHSTMT &stmt, SQLUSMALLINT column) {
    SQL_TIMESTAMP_STRUCT timestamp;
    SQLLEN length;
    SQLRETURN rc = SQLGetData(stmt, column, SQL_C_TIMESTAMP, (SQLPOINTER)&timestamp, 0, &length);

    if (!SQL_SUCCEEDED(rc))
        throw std::runtime_error("Could not get column data");

    if (length != SQL_NULL_DATA)
        return TimestampToString(timestamp);
    else
        return {};
}

Most of the members of class OdbcBinder are just there to support GetResult(). Without the need for binding, you could write a free function that does what you want:

template<typename Mapper>
auto GetQueryResult(Odbc& odbc, const std::string &query,
                    Mapper&& mapper, SQLULEN limit = 0)
{
    ...
    // Deduce the value type from the return type of mapper()
    using V = decltype(mapper(stmt));
    std::vector<V> results;
    ...
    while (SQL_SUCCEEDED(rc = SQLFetch(stmt)) {
        ...
        results.push_back(mapper(stmt));
    }
    ...
    return results;
}

With this in place, writing a mapper function should just be a few lines more than all the binder.BindCol() statements you had to write, and it is much more flexible.

It is possible to make it even nicer to write mapper functions; for example one could consider creating a wrapper class that represents a row and that has operator>>() overloads that would allow you to write code like:

Incident mapper(const Row &row) {
    Incident value;
    row >> value.id >> value.comments >> ... >> value.timeSend;
    return value;
}

Such a wrapper could look like:

class Row {
    const autoHSTMT &stmt;
    SQLUSMALLINT column{};
public:
    Row(const autoHSTMT &stmt): stmt(stmt) {}

    Row &operator>>(long &value) {
        SQLLEN length;
        SQLRETURN rc = SQLGetData(stmt, ++column, SQL_C_LONG, (SQLPOINTER)&value, 0, &length);
        ...
    }
    ...
};

Use struct instead of std::tuple where possible

A std::tuple is an easy way to group several values together in a quick way, but the drawback is that the members of a tuple don't have names. If you can use a struct instead, prefer that. So instead of:

using pll = std::tuple<long T::*, SQLLEN T::*, long V::*>;

Write:

struct pll {
    long T::* ptrin;
    SQLLEN T::* ptrinLen;
    long V::* ptrout;
};

Then inside BindCol() you can just write:

mappings.emplace_back(ptrin, ptrinLen, ptrout);

In fact, the above line will work with the std::tuple type as well. More importantly, you don't have to unpack the tuples in the visitors anymore, and this removes possible mistakes: for example, it's easy to get the order of the variables wrong in a structured binding without the compiler complaining.

Be careful managing state

There is a potential problem in your code, because there is state associated with stmt, and conditionally changing that state might result in problems. Take for example SQL_ATTR_MAX_ROWS. You only change this attribute if you pass a non-zero value for limit to GetResult(). But consider what happens if one calls GetResult() twice, once with a non-zero value for limit, and a second time with zero for limit. The second time the attribute is still left at the non-zero value.

Error handling

The function GetResult() always returns a result, even if the query could not be executed, or if there was a problem fetching some of the rows. It is hard for the caller to distinguish between a perfectly fine query that results in zero rows, versus a query that should have returned rows but failed.

Consider throwing exceptions or returning a std::optional so the caller can distinguish between success and failure.

\$\endgroup\$
6
  • 1
    \$\begingroup\$ I need to map between 3 buffers {ODBC internal buffer}->{DTO with in place buffer}->{Entity}. Those std::variant's were holding that transforamtion. You suggest to decouple it, but now I need specify both {ODBC internal buffer}->{DTO with in place buffer} and {DTO with in place buffer}->{Entity} in different places. \$\endgroup\$ Nov 3 '21 at 9:30
  • \$\begingroup\$ I updated question with refactored code \$\endgroup\$ Nov 3 '21 at 9:58
  • \$\begingroup\$ I missed that you had to convert twice. I've updated my answer with a possible solution for this, I hope it is helpful. \$\endgroup\$
    – G. Sliepen
    Nov 3 '21 at 20:28
  • 1
    \$\begingroup\$ At first, thanks for ideas (use conversion constructor for conversion, row binding DSL, max_rows state). At second, the main idea behind this code was to minimize number of API calls, cause profiler shows significant amount is spent in intermediate layers (not waiting for IO). Сurrent code is an order of magnitude faster than rebinding (each row) directly to std::string buffer, and around twice as fast than SQLGetData approach in spite of this excessive memmoving. \$\endgroup\$ Nov 4 '21 at 1:05
  • \$\begingroup\$ In my case typical result set is around 20 columns and 200-300k rows. So it would be 20*200'000+200(SetPos) api calls vs 20 (beside SQLFetch) \$\endgroup\$ Nov 4 '21 at 1:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.