cppdb and JSON data type

[dreaming-augustin]

MySQL 5.7 introduced a new JSON data type. cppcms also has a JSON class.

The cppdb documentation apparently says nothing about supporting JSON. Can we use operator>> and operator<< with a cppcms::json object to directly get or save JSON data into a MySQL JSON field?

If not, what would be required to get cppdb to conveniently bind MySQL JSON data to a cppcms JSON object?

[masaoliou]

While I have no knowledge of mySQL, this is my experience with PostgreSQL. CppDB calls the underlying libpq which takes and returns characters strings. Therefore, CppDB users are always supposed to send characters strings to PostgreSQL and to receive characters strings from PostgreSQL.

With that said, if CppDB works with mySQL the same way as with PostgreSQL, CppDB users are expected to always send/recieve JSON data in character strings format to/from CppDB.

[dreaming-augustin]

Thanks. I am about to make some tests and document a proper workaround.

It would be nice if the following could work out of the box:

cppdb::result res;
cppdb::session  sql(connection_string);
res = sql << "SELECT json_field FROM my_table WHERE id = 123";
if (res.next()) {
    cppcms::json::value json_field;
    res >> json_field;
}

but as expected, we get an error:

/usr/include/cppdb/frontend.h:567:4: error: no matching function for call to 'cppdb::result::fetch(cppcms::json::value&)

[dreaming-augustin]

For fetching json values, the solution in to create a temporary std::string, use it to create a std::istringstream and feed it to the json::value:

#include <sstream>
#include <cppcms/json.h>
#include <cppdb/frontend.h>

cppdb::result res;
cppdb::session  sql(connection_string);
res = sql << "SELECT json_field FROM my_table WHERE id = 123";
if (res.next()) {
    std::string string_field;
    res >> string_field;
    std::istringstream iss(string_field);
    cppcms::json::value json_field;
    iss >> json_field;
}

It's a verbose workaround, but it works.