4. How to Serialize SQL Result

How to Serialize SQL Result


  • What is the easiest way of serializing an SQL result? SQL has highly structured design for data and when I retrieve data after an SQL query I want to serialize it to send though network.

    Most basic SQL query is like this:

        QSqlQuery query("SELECT * FROM artist");
    int fieldNo = query.record().indexOf("country");
    while (query.next()) {
        QString country = query.value(fieldNo).toString();
        doSomething(country);
    }

    I can iterate in this query and create a JSON document but I feel like there is another option since data is structured.

    0 1 Reply
  • Lifetime Qt Champion

    Hi
    If by easy, you mean less code, i think QDataStream might be
    the winner.
    Like

    QByteArray block;
QDataStream out(&block, QIODevice::WriteOnly);

 while (query.next()) {
         for all cols // not right syntax.  ;)
           out << query.value(col)
    }

    What do you need the data for on the other side ?

    This will basically send it as a stream of variants.
    The Other side must know the types to convert to.
    You can send as concrete types if you wish.

    1 1 Reply

  • @maydin
    A possible alternative to converting a result set to JSON yourself in the client might be to get the database to produce JSON in the first place. For exaple, if you are using MySQL, see https://stackoverflow.com/questions/41758870/how-to-convert-result-table-to-json-array-in-mysql or https://dba.stackexchange.com/questions/192208/mysql-return-json-from-a-standard-sql-query. I have not checked how Qt accepts returned JSON, you would have to make sure that bit is OK.

    0

  • @mrjj Its almost what I want but I cannot read the data back.

    I send Strings to stream like this: out << query.value(col).toString()

    But when I want to read them back like this in >> a_string >> version >> other_string, a_string reads everything in stream and looks weird. I checked both QByteArray blocks before sending and after receiving, they are exactly same in qDebug() output.
    I am using Qt 5.12 and both programs are compiled with this.

    0 1 Reply
  • Lifetime Qt Champion

    @maydin
    Hi
    And you are using a QDataStream in both ends ?
    Normally using << >> with Qt types will add size information but you must make sure
    a_string is a QString.
    a std::string will not read it correctly etc.

    0 1 Reply

  • @mrjj I added codes to demonstrate. Keep in mind that, I sent block to another function to add function code in sender. In receiver after parsing function codes, passed QDataStream reference but I don't think this matters.

    Sender

    QByteArray block;
QDataStream out(&block, QIODevice::WriteOnly);
out.setVersion(QDataStream::Qt_5_12);
out << QString("sample string");
out << (qint32) 123;
qDebug() << "sended block" << block;

    Receiver

    in.setVersion(QDataStream::Qt_5_12);
QByteArray block;
in >> block;
qDebug() << "received block" << block;

    qDebug outputs:

    sended block   "\x00\x00\x00\x1A\x00s\x00""a\x00m\x00p\x00l\x00""e\x00 \x00s\x00t\x00r\x00i\x00n\x00g\x00\x00\x00{"
received block "\x00\x00\x00\x1A\x00s\x00""a\x00m\x00p\x00l\x00""e\x00 \x00s\x00t\x00r\x00i\x00n\x00g\x00\x00\x00{"

    Now original receiver result:

    in.setVersion(QDataStream::Qt_5_12);
QString a_string;
qint32 a_number;
in >> a_string >> a_number;
qDebug() << "variables:" << a_string << a_number;

    Output:
    variables: "\u0000\u001Asample string\u0000{" 0

    0 1 Reply

  • @maydin
    Does this have something to do with your QByteArray block;? Have a read of the answer at https://stackoverflow.com/a/53967747. If it's not, then are you somehow in the situation of that OP's own solution at https://stackoverflow.com/a/53974859 ?

    0 1 Reply

  • @JonB Our problem is similar but not same. In both situation, data is there, inside block. But he tries to cast serialized binary data to a QString, that's why he cannot see the string. He should have used >> operator to create QString from QDataStream. Actually I did like that but somehow QString ate all data inside block.

    If you check binary representation of block there is 1A before first letter 's'. 1A is aqual to 26 in decimal. My string sample string has 13 characters. 26 shows byte count probably.

    Actually this is mentioned in Qt 4 serializing page but not in Qt 5 page.
    https://doc.qt.io/archives/qt-4.8/datastreamformat.html

    I thought >> operator of QDataStream does this job for us but it seems it doesn't. I need to handle this situation by myself it seems.

    1
Log in to reply