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();
}

Answer 1

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();

}