2
\$\begingroup\$

This is follow-up on SQL (ODBC) bind to C++ classes row-wise

The main idea behind this code is to minimize the number of ODBC API calls, because profiling shows significant amount of time is spent in intermediate layers (not waiting for IO). ODBC supports bulk processing, but it requires all buffers to be in-place (see picture at link), so I can't bind to std::string directly and I need to transform my domain objects to flat memory layout.

There are 3 minor problems with this code marked with // (A), // (B) and // (C):

  • (A): this two fold expressions only compiles with GCC and pre-release MSVC. Any suggestions for Clang?
  • (B): I didn't find an easy way to infer this type from parameter pack.
  • (C): This class exists only to differentiate between SQL parameters and SQL columns parameters pack.
namespace details {
enum class bind_type
{
 defaultType,
 timestamp,
};
template <typename T, typename V>
struct bind_desc
{
 // using owner_type = V;
 using type = T;
 std::size_t size/* = 1 / 0*/;
 T V::* value;
 bind_type odbcType;
};
#pragma region dto
struct ts_dto
{
 SQL_TIMESTAMP_STRUCT buf;
 SQLLEN len;
 static constexpr SQLSMALLINT sql_c_type = SQL_C_TYPE_TIMESTAMP;
 static constexpr SQLSMALLINT sql_type = SQL_TYPE_TIMESTAMP;
 std::string toValue() const
 {
 return len == sizeof buf
 ? TimestampToString(buf)
 : (_ASSERT(len == SQL_NULL_DATA), std::string{});
 }
 void fromValue(std::string_view v)
 {
 buf = StringToTimestamp(v);
 if (IsNullTimestamp(buf))
 len = SQL_NULL_DATA;
 else
 len = sizeof (SQL_TIMESTAMP_STRUCT);
 }
};
struct ts_nn_dto
{
 SQL_TIMESTAMP_STRUCT buf;
 static constexpr SQLSMALLINT sql_c_type = SQL_C_TYPE_TIMESTAMP;
 static constexpr SQLSMALLINT sql_type = SQL_TYPE_TIMESTAMP;
 std::string toValue() const
 {
 return TimestampToString(buf);
 }
 void fromValue(std::string_view v)
 {
 buf = StringToTimestamp(v);
 }
};
template <std::size_t col_size>
struct string_dto
{
 char buf[col_size];
 SQLLEN len;
 static constexpr SQLSMALLINT sql_c_type = SQL_C_CHAR;
 static constexpr SQLSMALLINT sql_type = SQL_VARCHAR;
 std::string toValue() const
 {
 // bufferSize >= gotlength or is null
 _ASSERTE(col_size - 1u >= std::size_t(len) || len == SQL_NULL_DATA);
 std::string ret;
 if (len != SQL_NULL_DATA)
 {
 _ASSERT(len >= 0);
 // len - the length of the data before truncation because of the data buffer being too small
 ret.assign(buf, std::clamp<std::size_t>(len, 0, col_size - 1u));
 }
 return ret;
 }
 void fromValue(std::string_view v)
 {
 _ASSERT(sizeof buf >= v.size()); // throw?
 len = v.copy(buf, sizeof buf);
 }
};
struct long_nn_dto
{
 long buf;
 static constexpr SQLSMALLINT sql_c_type = SQL_C_SLONG;
 static constexpr SQLSMALLINT sql_type = SQL_INTEGER;
 long toValue() const
 {
 return buf;
 }
 void fromValue(long v)
 {
 buf = v;
 }
};
struct long_dto
{
 long buf;
 SQLLEN len;
 static constexpr SQLSMALLINT sql_c_type = SQL_C_SLONG;
 long toValue() const
 {
 return len == sizeof buf ? buf : (_ASSERT(len == SQL_NULL_DATA), 0);
 }
};
#pragma endregion
template <typename T, std::size_t col_size, bind_type odbcType>
consteval auto to_dto()
{
 if constexpr (std::is_same_v<T, std::string>)
 {
 if constexpr (odbcType == bind_type::timestamp)
 {
 if constexpr (col_size == 0)
 return ts_nn_dto{};
 else
 return ts_dto{};
 }
 else
 return string_dto<col_size + 1>{}; // + '0円'
 }
 else if constexpr (std::is_same_v<T, long>)
 {
 if constexpr (col_size == 0)
 return long_nn_dto{};
 else
 return long_dto{};
 }
 else
 static_assert(dependent_false<T>, "implement dto for T");
}
template <bind_desc Col>
using to_dto_t = decltype(to_dto<typename decltype(Col)::type, Col.size, Col.odbcType>());
template<typename x>
concept has_len_member = requires { &x::len; };
template <typename dto>
bool BindCol(SQLHSTMT stmt, SQLUSMALLINT n, dto& dto_column)
{
 SQLPOINTER buf_ptr = [&]
 {
 if constexpr (std::is_array_v<decltype(dto_column.buf)>)
 return dto_column.buf;
 else
 return &dto_column.buf;
 }();
 SQLLEN* len_ptr = [&]() -> SQLLEN*
 {
 if constexpr (details::has_len_member<dto>)
 return &dto_column.len;
 else
 return nullptr;
 }();
 const SQLRETURN rc =
 ::SQLBindCol(stmt, n, dto::sql_c_type, buf_ptr, sizeof dto_column.buf, len_ptr);
 //show_v<sizeof dto_column.buf>{};
 return rc == SQL_SUCCESS;
}
/////////////////////////////////////////////////////////////
template <typename T, std::size_t col_size, bind_type odbcType>
consteval auto to_param_dto()
{
 if constexpr (std::is_same_v<T, std::string>)
 {
 if constexpr (odbcType == bind_type::timestamp)
 {
 if constexpr (col_size == 0)
 return ts_nn_dto{};
 else
 return ts_dto{};
 }
 else
 return string_dto<col_size>{};
 }
 else if constexpr (std::is_same_v<T, long>)
 {
 if constexpr (col_size == 0)
 return long_nn_dto{};
 else
 static_assert(dependent_false<T>, "nullable long param not implemented");
 }
 else
 static_assert(dependent_false<T>, "implement dto for T");
}
template <bind_desc Par>
using to_param_dto_t = decltype(to_param_dto<typename decltype(Par)::type, Par.size, Par.odbcType>());
template <typename dto>
bool BindParam(SQLHSTMT stmt, SQLUSMALLINT n, dto& dto_column)
{
 SQLPOINTER buf_ptr = [&]
 {
 if constexpr (std::is_array_v<decltype(dto_column.buf)>)
 return dto_column.buf;
 else
 return &dto_column.buf;
 }();
 SQLLEN* len_ptr = [&]
 {
 if constexpr (details::has_len_member<dto>)
 return &dto_column.len;
 else
 return nullptr;
 }();
 const SQLRETURN rc =
 ::SQLBindParameter(stmt, n, SQL_PARAM_INPUT, dto::sql_c_type, dto::sql_type,
 /*col_size*/sizeof dto_column.buf, /*scale*/0, buf_ptr, 0, len_ptr);
 return rc == SQL_SUCCESS;
}
}
template<typename V = void, details::bind_desc... Cols>
class OdbcBinder // (C)
{
 template<typename P, details::bind_desc... Par>
 static auto BindParams(COdbc& odbc, SQLHSTMT stmt, const std::vector<P>& v)
 {
 using DTOProw = tuple<details::to_param_dto_t<Par>...>;
 static_assert(std::is_trivial_v<DTOProw> && std::is_standard_layout_v<DTOProw>);
 std::unique_ptr<DTOProw[]> res = std::make_unique_for_overwrite<DTOProw[]>(v.size());
 constexpr auto ConvertParam = []<std::size_t... I> (DTOProw& dto, const P& t, std::index_sequence<I...>)
 {
 ((get<I>(dto).fromValue(t.*(Par.value))), ...); // (A)
 };
 constexpr auto is = std::make_index_sequence<sizeof...(Par)>{};
 for (std::size_t i = 0; i < v.size(); ++i)
 ConvertParam(res[i], v[i], is);
 SQLUSMALLINT N = 0;
 apply(
 [&](auto&... dto_row) {
 if (!(details::BindParam(stmt, ++N, dto_row) && ...))
 throw ODBCException("OB: SQLBindParameter", SQL_ERROR, odbc.LogLastError(stmt));
 }, res[0]);
 return res;
 }
 static void Exec(COdbc& odbc, SQLHSTMT stmt, std::string_view query)
 {
 SQLRETURN rc = ::SQLExecDirect(stmt, (SQLCHAR*)query.data(), static_cast<SQLINTEGER>(query.size()));
 if (!SQL_SUCCEEDED(rc))
 {
 throw ODBCException("OB: SQLExecDirect", rc, odbc.LogLastError(stmt));
 }
 }
 static std::vector<V> GetQueryResultInternal(COdbc& odbc, SQLHSTMT stmt, std::string_view query, SQLULEN limit, SQLULEN batchSize)
 {
 using DTOrow = tuple<details::to_dto_t<Cols>...>;
 //show<DTOrow>{};
 constexpr SQLULEN default_batch_size = 32 * 1024 / sizeof(DTOrow); // 32 kb at least
 if (batchSize == 0)
 batchSize = limit != 0 && limit < default_batch_size ? limit : default_batch_size;
 static_assert(std::is_trivial_v<DTOrow> && std::is_standard_layout_v<DTOrow>);
 static_assert(std::is_nothrow_move_constructible_v<V>); // for fast std::vector reallocation
 SQLRETURN rc;
 if (limit != 0)
 {
 rc = ::SQLSetStmtAttr(stmt, SQL_ATTR_MAX_ROWS, (SQLPOINTER)limit, SQL_IS_UINTEGER); _ASSERT(rc == 0);
 if (rc != SQL_SUCCESS)
 odbc.LogLastError(stmt);
 }
 Exec(odbc, stmt, query);
 const std::unique_ptr<DTOrow[]> mem = std::make_unique_for_overwrite<DTOrow[]>(batchSize);
 //std::span<DTOrow> rowArray(mem.get(), batchSize);
 SQLULEN numRowsFetched = 0;
 rc = ::SQLSetStmtAttr(stmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)batchSize, SQL_IS_UINTEGER); _ASSERT(rc == 0);
 rc = ::SQLSetStmtAttr(stmt, SQL_ATTR_ROW_BIND_TYPE, (SQLPOINTER)sizeof(DTOrow), SQL_IS_UINTEGER); _ASSERT(rc == 0);
 rc = ::SQLSetStmtAttr(stmt, SQL_ATTR_ROWS_FETCHED_PTR, &numRowsFetched, SQL_IS_POINTER); _ASSERT(rc == 0);
 // Bind columns
 SQLUSMALLINT N = 0;
 apply(
 [&](auto&... dto_row) {
 if (!(details::BindCol(stmt, ++N, dto_row) && ...))
 throw ODBCException("OB: SQLBindCol", SQL_ERROR, odbc.LogLastError(stmt));
 }, mem[0]);
 std::vector<V> results;
 results.reserve(batchSize);
 constexpr auto Convert = []<std::size_t... I>(const DTOrow & t, std::index_sequence<I...>) -> V
 {
 V value;
 ((value.*(Cols.value) = get<I>(t).toValue()), ...); // (A)
 return value;
 };
 constexpr auto is = std::make_index_sequence<sizeof...(Cols)>{};
 do
 {
 while (SQL_SUCCEEDED((rc = ::SQLFetch(stmt))))
 {
 if (rc == SQL_SUCCESS_WITH_INFO)
 odbc.LogLastError(stmt);
 for (SQLUINTEGER i = 0; i < numRowsFetched; ++i)
 {
 results.emplace_back(Convert(mem[i], is));
 }
 }
 if (rc != SQL_NO_DATA)
 {
 throw ODBCException("OB: SQLFetch", rc, odbc.LogLastError(stmt));
 }
 } while (SQL_SUCCEEDED((rc = ::SQLMoreResults(stmt))));
 if (rc != SQL_NO_DATA)
 {
 throw ODBCException("OB: SQLMoreResults", rc, odbc.LogLastError(stmt));
 }
 if (results.capacity() > results.size() * 3)
 results.shrink_to_fit();
 return results;
 }
public:
 OdbcBinder() = delete;
 OdbcBinder(const OdbcBinder&) = delete;
 OdbcBinder(OdbcBinder&&) = delete;
 template<typename P, details::bind_desc... Par>
 static void ExecuteWithParams(COdbc& odbc, const std::vector<P>& v, std::string_view query)
 {
 autoHSTMT stmt{ odbc.GetHstmt() };
 auto pDto = BindParams<P, Par...>(odbc, stmt, v);
 Exec(odbc, stmt, query);
 SQLRETURN rc;
 while (SQL_SUCCEEDED((rc = ::SQLMoreResults(stmt)))) /* empty */;
 if (rc != SQL_NO_DATA)
 {
 throw ODBCException("OB: SQLMoreResults", rc, odbc.LogLastError(stmt));
 }
 }
 template<typename P, details::bind_desc... Par>
 static std::vector<V> GetQueryResultWithParams(COdbc& odbc, const std::vector<P>& v, std::string_view query, SQLULEN limit = 0, SQLULEN batchSize = 0)
 {
 autoHSTMT stmt{ odbc.GetHstmt() };
 auto pdto = BindParams<P, Par...>(odbc, stmt, v);
 return GetQueryResultInternal(odbc, stmt, query, limit, batchSize);
 }
 static std::vector<V> GetQueryResult(COdbc& odbc, std::string_view query, SQLULEN limit = 0, SQLULEN batchSize = 0)
 {
 autoHSTMT stmt{ odbc.GetHstmt() };
 return GetQueryResultInternal(odbc, stmt, query, limit, batchSize);
 }
};
namespace details {
template<typename T>
concept not_varsize = std::integral<T> || std::floating_point<T> || std::same_as<T, SQL_TIMESTAMP_STRUCT>;
}
template <typename V>
consteval auto sized(std::string V::* ptr, std::size_t col_size)
{
 return details::bind_desc<std::string, V>{ col_size, ptr };
}
template <details::not_varsize T, typename V>
consteval auto not_null(T V::* ptr)
{
 return details::bind_desc<T, V>{ 0, ptr };
}
template <details::not_varsize T, typename V>
consteval auto nullable(T V::* ptr)
{
 return details::bind_desc<T, V>{ 1, ptr };
}
template <typename V>
consteval auto not_null_ts(std::string V::* ptr)
{
 return details::bind_desc<std::string, V>{ 0, ptr, details::bind_type::timestamp };
}
template <typename V>
consteval auto nullable_ts(std::string V::* ptr)
{
 return details::bind_desc<std::string, V>{ 1, ptr, details::bind_type::timestamp };
}

Usage example:

struct Incident
{
 long id;
 std::string name;
 std::string comments;
 /* ... */
 std::string timeSend;
 std::string timeClosed;
 long num;
};
int main()
{
 auto odbc = COdbc{}; // create connection, etc
 std::vector<Incident> vec =
 OdbcBinder<Incident // (B)
 , not_null(&Incident::id)
 , sized(&Incident::name, 25)
 , sized(&Incident::comments, 100)
 , not_null_ts(&Incident::timeSend)
 , nullable_ts(&Incident::timeClosed)
 , nullable(&Incident::num)
 >::GetQueryResult(odbc, "select ID, COMMENTS, " /* ... */ "TIME_CLOSED from INCIDENTS");
 OdbcBinder<>::ExecuteWithParams<Incident // (B)
 , not_null(&Incident::id)
 , sized(&Incident::name, 25)
 , sized(&Incident::comments, 100)
 , not_null_ts(&Incident::timeSend)
 , nullable_ts(&Incident::timeClosed)
 >(odbc, vec, "insert into INCIDENTS values (?, ?, ?, ?, ?)");
}

Full code with ODBC API stubs: https://godbolt.org/z/c9oxq7GMc

asked Nov 25, 2021 at 14:51
\$\endgroup\$
4
  • \$\begingroup\$ Are there some missing #include lines that got missed? \$\endgroup\$ Commented Nov 25, 2021 at 15:54
  • \$\begingroup\$ Which version of Clang did you try? \$\endgroup\$ Commented Nov 25, 2021 at 19:03
  • \$\begingroup\$ @TobySpeight Full code with includes and API stubs on godbolt link in the end. Odbc includes are platform dependant and std imports are namespace qualified and obvious (string/vector/type_traits). The only missing code is trivially constructible tuple (included in linked code), but have same API as std::tuple and irrelevant to the topic. \$\endgroup\$ Commented Nov 25, 2021 at 22:37
  • \$\begingroup\$ @G.Sliepen 13.0 and godbolt trunk (clang version 14.0.0 (https://github.com/llvm/llvm-project.git 7bd87a03fdf1559569db1820abb21b6a479b0934)). You can open clang tab on the right and look at errors. <source>:518:27: error: member reference base type 'details::bind_desc' is not a structure or union ((value.*(Cols.value) = get<I>(t).toValue()), ...); and <source>:447:44: error: member reference base type 'details::bind_desc' is not a structure or union ((get<I>(dto).fromValue(t.*(Par.value))), ...); // (A) \$\endgroup\$ Commented Nov 25, 2021 at 22:44

0

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.