Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. How to Serialize SQL Result
Forum Updated to NodeBB v4.3 + New Features

How to Serialize SQL Result

Scheduled Pinned Locked Moved Unsolved General and Desktop
8 Posts 3 Posters 2.5k Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • M Offline
    M Offline
    maydin
    wrote on last edited by
    #1

    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.

    JonBJ 1 Reply Last reply
    0
    • mrjjM Offline
      mrjjM Offline
      mrjj
      Lifetime Qt Champion
      wrote on last edited by
      #2

      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.

      M 1 Reply Last reply
      1
      • M 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.

        JonBJ Offline
        JonBJ Offline
        JonB
        wrote on last edited by
        #3

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

        1 Reply Last reply
        0
        • mrjjM 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.

          M Offline
          M Offline
          maydin
          wrote on last edited by
          #4

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

          mrjjM 1 Reply Last reply
          0
          • M 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.

            mrjjM Offline
            mrjjM Offline
            mrjj
            Lifetime Qt Champion
            wrote on last edited by
            #5

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

            M 1 Reply Last reply
            0
            • mrjjM 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.

              M Offline
              M Offline
              maydin
              wrote on last edited by
              #6

              @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

              JonBJ 1 Reply Last reply
              0
              • M 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

                JonBJ Offline
                JonBJ Offline
                JonB
                wrote on last edited by
                #7

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

                M 1 Reply Last reply
                0
                • JonBJ 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 ?

                  M Offline
                  M Offline
                  maydin
                  wrote on last edited by
                  #8

                  @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 Reply Last reply
                  1

                  • Login

                  • Login or register to search.
                  • First post
                    Last post
                  0
                  • Categories
                  • Recent
                  • Tags
                  • Popular
                  • Users
                  • Groups
                  • Search
                  • Get Qt Extensions
                  • Unsolved