How to write SQL query without knowing argument number in compile time

[hwc0919]

When using libpq, we can call PQexecParams with arbitrary number of arguments. How can we achieve this in drogon?

DbClient 的接口为函数模板，似乎只能在编译时就确定参数个数？ 目前正在考虑从别的框架转向 drogon, 我的使用场景是这样的: 在 app 启动时从数据库获取pg函数的元数据，做字符串拼接，根据请求数据匹配相应的函数。请求的解析通过加载新的动态库更新，pg函数可以直接在数据库更新，这样无需重新编译 server 就能适用新的业务逻辑。所以在运行时才能确定参数个数。不知道有没有办法实现？

[hwc0919]

I did something like this and it worked!!

  // Sync and blocking method
  const Result execSqlSync1(const std::string &sql,
                           const char * const * pcszArguments, int i32Args) noexcept(false)
  {
      Result r(nullptr);
      {
          auto binder = *this << sql;
          // (void)std::initializer_list<int>{
          //     (binder << std::forward<Arguments>(args), 0)...};
          for (int i = 0; i < i32Args; ++i)
          {
              LOG_DEBUG << "binder input " << pcszArguments[i] << '\n';
              binder << std::string(pcszArguments[i]);
          }

          // Use blocking mode
          binder << Mode::Blocking;

          binder >> [&r](const Result &result) { r = result; };
          binder.exec();  // exec may be throw exception;
      }
      return r;
  }

But I still don't know how to deal with different data types. Only find overload operator<< for strings.

[an-tao]

I did something like this and it worked!!

  // Sync and blocking method
  const Result execSqlSync1(const std::string &sql,
                           const char * const * pcszArguments, int i32Args) noexcept(false)
  {
      Result r(nullptr);
      {
          auto binder = *this << sql;
          // (void)std::initializer_list<int>{
          //     (binder << std::forward<Arguments>(args), 0)...};
          for (int i = 0; i < i32Args; ++i)
          {
              LOG_DEBUG << "binder input " << pcszArguments[i] << '\n';
              binder << std::string(pcszArguments[i]);
          }

          // Use blocking mode
          binder << Mode::Blocking;

          binder >> [&r](const Result &result) { r = result; };
          binder.exec();  // exec may be throw exception;
      }
      return r;
  }

But I still don't know how to deal with different data types. Only find overload operator<< for strings.

operator << can accept multiple type of parameters. and when you use blocking mode of the binder, it may throw exceptions when executing SQL.

[hwc0919]

Thank you for your reply! By 'dealing with different data types', I mean something like pg bigint, int, int[], datetime or other user-defined types. We know that PQexecParams requires an oid array as parameter , I'm looking for something similar in drogon.

BTW, this is a wonderful framework, I'm looking forward to rebuilding my server upon it.

[an-tao]

Please refer the SqlBinder class; BTW, you don't need to change the DbClient source code, just create a binder with DbClient object. like this:

try{
auto binder = (*dbClientPtr) << sql;
for (int i = 0; i < i32Args; ++i)
          {
              LOG_DEBUG << "binder input " << pcszArguments[i] << '\n';
              binder << std::string(pcszArguments[i]);
          }

          // Use blocking mode
          binder << Mode::Blocking;

          binder >> [&r](const Result &result) { r = result; };
          binder.exec();  // exec may be throw exception;
}
catch (const std::exception &err)
{
...} 

[hwc0919]

想用二进制格式传输数组，看了一下源码似乎并不支持。如果想传入数组参数，只能以 literal 的方式传入 ？

[an-tao]

pg的数组比较麻烦，还支持嵌套，目前没有原生支持，你要写成'{...}'字符串的形式。

[hwc0919]

希望可以支持用户传入oid数组，然后用户可以选择自己把数据序列化成 byte array 传给 sqlBinder.

现在的oid数组是直接传了null.

https://github.com/drogonframework/drogon/blob/991873cf604f551027d2b26f3ee911ad6421e790/orm_lib/src/postgresql_impl/PgConnection.cc#L258-L263

[an-tao]

考虑一下怎么搞。。。

[hwc0919]

我重写了一个 PgClient，基本照抄只保留基本功能(因为想只依赖trantor, 所以没有选择继承)，但是参数和结果都用二进制方式 ( paramTypes 自定义, resultFormat = 1 )，请求和结果接入了自己用的序列化库，已经可以正常工作了，基本满足了我的需求。我觉得如果你打算支持二进制的话，可以新增一套接口，比如 execBinSql(std::shared_ptr<SqlBinCmd>) ，把SqlBinCmd暴露给用户, 用户自己填写请求.

[an-tao]

我一开始也是用二进制格式的，但是好像数组类型的字段没调通，就放弃了，二进制属于底层运作方式，可以不用暴露在接口上。