How to Serialize SQL Result

maydin

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.

mrjj

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.

JonB

@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.

maydin

@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.

mrjj

@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.

maydin

@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

JonB

@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 ?

maydin

@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.

Feedback Wanted: Qt Hub

How to Serialize SQL Result