Is QSqlQuery not support json type in mysql ?

[funnydog]

I have a json type data in mysql, and when i retrieve the data like this:

    QSqlQuery   query;
    query.prepare("SELECT * from mytable");
    book ok query.exec(); // ok, always return true
    bool active   = query.isActive();// ok, always return true
    bool selected = query.isSelect();// ok, always return true
    bool valid= query.isValid();// error, always return false
    while (query.next())// error, query.next() always return false
    {
       // some logic code
    }

I cannot retrieve any data. However, once I change the json type to TEXT in mysql, everything is ok and I can get the data.
So, is QSqlQuery not support json type in mysql ?
My Qt version is 5.12. Mysql version is 5.7.20.

[SGaist]

Hi and welcome to devnet,

AFAIK, no, if you really need it, you will have to modify the MYSQL backend so that it can also handles that datatype.

[funnydog]

@SGaist Thanks for the answer. What do you mean that "modify the MYSQL backend" ?

[Christian Ehrlicher]

You hvae to modify the Qt source code (qsql_mysql.cpp)

[JonB]

@funnydog
All these complications! Without having to modify Qt/MySQL code, can't you just (untested) SELECT ..., CAST(json_column AS TEXT), ... in your statement (or, you could even put a VIEW in at the MySQL side if you don't want to have to do that each time, or don't know which columns are JSON at the client), and then handle it as JSON in your client code? It's a touch more code if you want to be able to write it back to the database, but you haven't asked for that.

[Christian Ehrlicher]

Strange that MYSQL is so stupid here. With PostgreSql it works out-of-the-box (it's returned as a QString)

[JonB]

@Christian-Ehrlicher
Well that's down to the QMYSQL driver rather than MySQL itself, isn't it?

[Christian Ehrlicher]

No, in PostgreSql it's returned as string type rather than a special json(b) type so it works out-of-the-box

[JonB]

@Christian-Ehrlicher
Then it's PostgreSql which is "stupid" here, because it's not putting the special interpretation on JSON compared to plain text which MySQL does :)

[Christian Ehrlicher]

Internally it's json and you can do queries on it, and that's the case for a very long time already: https://www.postgresql.org/docs/9.3/functions-json.html
If you don't pass valid json to such a column you also get an sql error.