C, C++, dates, datetimes

from the Artful MySQL Tips List


Some types (ints, floats &c) map easily to C, C# and C++ data types. Others not so much. A MySQL developer posted this explanation on the MySQL Conector/C++ forum ...

For some MySQL types, like numeric and string, we do conversions from MySQL representation to the corresponding C++ type, because the type to convert to is obvious in these cases. However, there are other MySQL types for which it is not so clear what C++ type should be used to represent the value, for example the DECIMAL type for fixed precision numbers (not that such types do not exist, but it is not clear if/which one should be supported). For the moment the situation we have is that for all MySQL value types that do not have the obvious automatic conversion to C++ type, user is responsible for converting it to the type of his choice. It can be done by asking the server to do the conversion (for example to string), like Luis has suggested. The other option is to get the raw binary representation of the value using Value::getRawBytes() and work with that, which of course requires understanding of how different types of MySQL values are represented in the protocol. For temporary types you can find this information here: https://dev.mysql.com/doc/internals/en/x-protocol-messages-messages.html#x-protocol-messages-resultsets;.

We believe that there is no single "right" way of representing the different possible values and in the end the user has to decide what exact representation to use. Certainly the task of converting the internal representation to user's type of choice could be made simpler, for example using template techniques, and we are thinking about such solutions. But at the moment the only way is to either look at the internal representation or ask server to do the conversion.

A user posted this problem report ...

Query:

SELECT 
  id, 
  CAST(date_update as char) 
    as date_update_string, date_update 
FROM table_name 
LIMIT 1
Simple implementation for decoding raw bytes:
uint64_t DecodeOne(uint8_t *in, size_t * len)
{
    uint64_t r = 0;

    do {
        uint64_t val = *in;
        r = (r << 7) | (uint64_t)(val & 127);
        in++;
        (*len)--;
    } while ((*len > 0) && (uint64_t(*in) & 128));

    return r;
}
void DecodeBuffer(uint8_t * buffer, size_t size)
{
    std::vector reverseBuffer;
    for (size_t i = 1; i <= size; i++) {
        reverseBuffer.push_back(buffer[size - i]);
    }

    size_t tmpSize = size;
    uint8_t * reverseData = reverseBuffer.data();
    while (tmpSize > 0) {
        std::cout << DecodeOne(&reverseData[size - tmpSize], &tmpSize) << " ";
    }
    std::cout << "\n";
}
Reading the "date_update_string" field in both implementations has the same result: "2010-09-17 11:38:26" - Its OK.

Reading "date_update" field using Plain C api:

mysqlx_get_bytes (row, idx, 0, data, size);
DecodeBuffer(data, size);
size == 7 and output is "26 38 11 17 9 2010"

Reading "date_update" field using CPP api:

std::pair res = value.getRawBytes();
auto * data = res.first;
auto * size = res.second;
DecodeBuffer(data, size);

size == 6 and output is "38 11 17 9 2010"
Why does size differ and where are the seconds in the CPP example?

The user concludes, "it is impossible to use C ++ Api for datetime fields." It's been posted as a bug report but Oracle has blocked public access to it. Go figure.


Return to the Artful MySQL Tips page