Qt QSqlQuery return in json

Question

I've made a sqlquery method in my application that basically gets a SQL command and returns the result in json, the issue is that this creates invalid json when filled with " and other problematic characters..

I tried to create a QObject first then serializing it into a JSON, but I can't achieve it.

How do you make this method generate a valid json even with data containing " signs?

QString Api::SQLQuery(const QString & sqlquery)
{
QSqlQuery query;

bool firstline = true;
query.setForwardOnly(true);
if(query.exec(sqlquery))
{
    QString answer = "[";
    while(query.next())
        {
            if(firstline){firstline = false;}else {answer += ",";}

            answer += "{";
            for(int x=0; x < query.record().count(); ++x)
            {
                if(x != 0){answer += ",";}
                answer += "\""+query.record().fieldName(x) +"\":\""+ query.value(x).toString()+"\"";
            }
            answer += "}";
        }
    answer += "]";
    return answer;
}
else
{
    return query.lastError().text() ;
}

}

Solution :

Thanks to the answers this is the correct method:

QString Api::SQLQuery(const QString & sqlquery) {
QSqlQuery query;
  query.setForwardOnly(true);
  if (!query.exec(sqlquery))return QString();

  QJsonDocument  json;
  QJsonArray     recordsArray;

  while(query.next()) 
  {
     QJsonObject recordObject;
        for(int x=0; x < query.record().count(); x++)
        {
        recordObject.insert( query.record().fieldName(x),QJsonValue::fromVariant(query.value(x)) );
        }
     recordsArray.push_back(recordObject);
  }
  json.setArray(recordsArray);
  
  return json.toJson();
}

evilruff · Accepted Answer · 2013-08-08T12:20:02.747

Small design note.. I would advise to review design regarding error handling. You are returning QString from your function which can be either proper JSON document or just error text. So, you actually mixing different result set types within one language type - String. So you need to make some extra checks in the code around to understand what's actually happened.

Qt 5.x sample:

QString Api::SQLQuery(const QString & sqlquery) {
  QSqlQuery query;

  query.setForwardOnly(true);
  if (!query.exec(sqlquery))
      return QString();

  QJsonDocument  json;
  QJsonArray     recordsArray;

  while(query.next()) {
     for(int x=0; x < query.record().count(); x++) {
         QJsonObject        recordObject;

     recordObject.insert( query.record().fieldName(x), 
               QJsonValue::fromVariant(query.value(x)) );   
     }
     recordsArray.push_back(recordObject);
  }
  json.setArray(recordsArray);

  return json.toJson();

}

Thanks! I figured out how to implement it the way I had it in mind. 2 corrections : json.setObject should be json.setArray and the push_back into the for loop should be outside of the loop, but in the while loop. I added the corrected code in the original post. thanks again. — Vincent Duprez, Aug 08 '13 at 11:31

score 1 · Answer 2 · edited May 23 '17 at 12:13

I'd suggest to use a proper Json implementation to get the escaping of quotes etc. right.

If you're using Qt5: Qt5 comes with QJsonDocument bundled as part of qtbase.

If you're using Qt4: There's no Json support built-in, but you can use third-party libraries such as qjson.

If you really really can't use a proper lib, you can do it yourself and escape the special characters manually (Here's a list).

E.g.

QString escapeForJson(QString s) {
    s = s.replace(QLatin1String("\""), QLatin1String("\\\"));
    …
    return s;
}

Qt QSqlQuery return in json

2 Answers2