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. QSqlQuery, addBindValue, LargeBlob problem?
Forum Updated to NodeBB v4.3 + New Features

QSqlQuery, addBindValue, LargeBlob problem?

Scheduled Pinned Locked Moved Unsolved General and Desktop
14 Posts 3 Posters 1.1k Views 2 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.
  • SPlattenS Offline
    SPlattenS Offline
    SPlatten
    wrote on last edited by SPlatten
    #1

    I am constructing a call to a stored procedure, one of the parameters was previously a BLOB, I've changed it now to a LONGBLOB and now there seems to be a problem.

    C++ Code:

                            QSqlQuery queryBlock;
                            queryBlock.prepare("SELECT addBlock(?,?,?,?,?);");
    qdbg() << "muintDatasetID: " << muintDatasetID;
    qdbg() << "   strFilename: " << strFilename;
    qdbg() << "  muint64Block: " << muint64Block;
    qdbg() << "   bytaryChunk: " << bytaryChunk.length();
    qdbg() << " uint8Checksum: " << uint8Checksum;
                            queryBlock.addBindValue(muintDatasetID);
                            queryBlock.addBindValue(strFilename);
                            queryBlock.addBindValue(muint64Block);
                            queryBlock.addBindValue(bytaryChunk, QSql::Binary);
                            queryBlock.addBindValue(uint8Checksum);
                            if ( Trainer::queryDB(queryBlock) == true )
    

    My stored procedure:

    CREATE DEFINER=`root`@`localhost` FUNCTION `addBlock`(
          `_biDataset` BIGINT,
          `_txtFilename` TEXT,
          `_intBlockNo` INT,
          `_binChunk` LONGBLOB,
          `_intChecksum` INT
    )
    RETURNS bigint(20)
    LANGUAGE SQL
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT 'Adds a single data block'
    exitProc:BEGIN
    #Function:        addBlock
    #Parameters:      _biDataset     Optional, Dataset In/Out number
    #                 _txtFilename      File name of RDF dataset
    #                 _intBlockNo       Block number (base 0)
    #                 _binChunk         Large Binary Blob of Data
    #                 _intChecksum      8 bit CRC
    #Notes:           Creates single record of data block
    #History:         2021/05/19  Written by Simon Platten
          #Validate parameters
          IF _txtFilename IS NULL OR
             _intBlockNo IS NULL OR
                _binChunk IS NULL OR
                _intChecksum IS NULL THEN
          #No, abort!
                RETURN -1;
          END IF;
          IF _biDataset IS NULL THEN
          #Look up the dataset id
                SELECT
                      `biPK` INTO _biDataset
                FROM
                      datasets
                WHERE
                      txtFilename=_txtFilename;          
          END IF;
          #Was dataset located?
          IF _biDataset IS NULL THEN
          #No, abort!
                RETURN -2;
          END IF;
          #Insert new record
          INSERT INTO `rdf` (
                `biDataset`,
                `intBlockNo`,
                `intChecksum`,
                `binChunk`
          ) VALUES (
                _biDataset,
                _intBlockNo,
                _intChecksum,
                _binChunk
          );
          RETURN LAST_INSERT_ID();
    END
    

    Output to console:

    muintDatasetID: 1
       strFilename: 20211004_11h42_LabTest_tvg5000_0.rdf
      muint64Block: 0
       bytaryChunk: 34390236
     uint8Checksum: 18
    

    This code previously worked when I was dealing with a BLOB, now I've changed the stored procedure and table types from BLOB to LONGBLOB it errors, with no sensible error message I can post. What could the problem?

    Kind Regards,
    Sy

    artwawA 1 Reply Last reply
    0
    • SPlattenS SPlatten

      I am constructing a call to a stored procedure, one of the parameters was previously a BLOB, I've changed it now to a LONGBLOB and now there seems to be a problem.

      C++ Code:

                              QSqlQuery queryBlock;
                              queryBlock.prepare("SELECT addBlock(?,?,?,?,?);");
      qdbg() << "muintDatasetID: " << muintDatasetID;
      qdbg() << "   strFilename: " << strFilename;
      qdbg() << "  muint64Block: " << muint64Block;
      qdbg() << "   bytaryChunk: " << bytaryChunk.length();
      qdbg() << " uint8Checksum: " << uint8Checksum;
                              queryBlock.addBindValue(muintDatasetID);
                              queryBlock.addBindValue(strFilename);
                              queryBlock.addBindValue(muint64Block);
                              queryBlock.addBindValue(bytaryChunk, QSql::Binary);
                              queryBlock.addBindValue(uint8Checksum);
                              if ( Trainer::queryDB(queryBlock) == true )
      

      My stored procedure:

      CREATE DEFINER=`root`@`localhost` FUNCTION `addBlock`(
            `_biDataset` BIGINT,
            `_txtFilename` TEXT,
            `_intBlockNo` INT,
            `_binChunk` LONGBLOB,
            `_intChecksum` INT
      )
      RETURNS bigint(20)
      LANGUAGE SQL
      DETERMINISTIC
      CONTAINS SQL
      SQL SECURITY DEFINER
      COMMENT 'Adds a single data block'
      exitProc:BEGIN
      #Function:        addBlock
      #Parameters:      _biDataset     Optional, Dataset In/Out number
      #                 _txtFilename      File name of RDF dataset
      #                 _intBlockNo       Block number (base 0)
      #                 _binChunk         Large Binary Blob of Data
      #                 _intChecksum      8 bit CRC
      #Notes:           Creates single record of data block
      #History:         2021/05/19  Written by Simon Platten
            #Validate parameters
            IF _txtFilename IS NULL OR
               _intBlockNo IS NULL OR
                  _binChunk IS NULL OR
                  _intChecksum IS NULL THEN
            #No, abort!
                  RETURN -1;
            END IF;
            IF _biDataset IS NULL THEN
            #Look up the dataset id
                  SELECT
                        `biPK` INTO _biDataset
                  FROM
                        datasets
                  WHERE
                        txtFilename=_txtFilename;          
            END IF;
            #Was dataset located?
            IF _biDataset IS NULL THEN
            #No, abort!
                  RETURN -2;
            END IF;
            #Insert new record
            INSERT INTO `rdf` (
                  `biDataset`,
                  `intBlockNo`,
                  `intChecksum`,
                  `binChunk`
            ) VALUES (
                  _biDataset,
                  _intBlockNo,
                  _intChecksum,
                  _binChunk
            );
            RETURN LAST_INSERT_ID();
      END
      

      Output to console:

      muintDatasetID: 1
         strFilename: 20211004_11h42_LabTest_tvg5000_0.rdf
        muint64Block: 0
         bytaryChunk: 34390236
       uint8Checksum: 18
      

      This code previously worked when I was dealing with a BLOB, now I've changed the stored procedure and table types from BLOB to LONGBLOB it errors, with no sensible error message I can post. What could the problem?

      artwawA Offline
      artwawA Offline
      artwaw
      wrote on last edited by
      #2

      @SPlatten Are you certain that both the DB you are connected to and the driver/connector support such construct? My gut feeling would be that id DB supports the driver has a problem.

      For more information please re-read.

      Kind Regards,
      Artur

      SPlattenS 1 Reply Last reply
      1
      • artwawA artwaw

        @SPlatten Are you certain that both the DB you are connected to and the driver/connector support such construct? My gut feeling would be that id DB supports the driver has a problem.

        SPlattenS Offline
        SPlattenS Offline
        SPlatten
        wrote on last edited by
        #3

        @artwaw , is there anyway I can find out? I'm using HeidiSQL to review the database and that looks ok.

        Kind Regards,
        Sy

        artwawA 1 Reply Last reply
        0
        • SPlattenS SPlatten

          @artwaw , is there anyway I can find out? I'm using HeidiSQL to review the database and that looks ok.

          artwawA Offline
          artwawA Offline
          artwaw
          wrote on last edited by
          #4

          @SPlatten said in QSqlQuery, addBindValue, LargeBlob problem?:

          is there anyway I can find out

          Documentation to both, I suppose?

          For more information please re-read.

          Kind Regards,
          Artur

          SPlattenS 1 Reply Last reply
          0
          • artwawA artwaw

            @SPlatten said in QSqlQuery, addBindValue, LargeBlob problem?:

            is there anyway I can find out

            Documentation to both, I suppose?

            SPlattenS Offline
            SPlattenS Offline
            SPlatten
            wrote on last edited by
            #5

            @artwaw, MariaDB certainly supports LARGEBLOB, no errors in HeidiSQL, so it could be the SQL driver I guess...

            Kind Regards,
            Sy

            artwawA 1 Reply Last reply
            0
            • SPlattenS SPlatten

              @artwaw, MariaDB certainly supports LARGEBLOB, no errors in HeidiSQL, so it could be the SQL driver I guess...

              artwawA Offline
              artwawA Offline
              artwaw
              wrote on last edited by
              #6

              @SPlatten How large is the blob in your case? I think I remember reading the advice about large blobs in MariaDB that one should be careful since it is easy to run of memory in the stack or something.
              In other words: might be it is worth to change to QSqlQuery *queryBlock = new QSqlQuery() etc. Again, one of the possibilities, I am not certain what fails there.

              For more information please re-read.

              Kind Regards,
              Artur

              SPlattenS 2 Replies Last reply
              0
              • artwawA artwaw

                @SPlatten How large is the blob in your case? I think I remember reading the advice about large blobs in MariaDB that one should be careful since it is easy to run of memory in the stack or something.
                In other words: might be it is worth to change to QSqlQuery *queryBlock = new QSqlQuery() etc. Again, one of the possibilities, I am not certain what fails there.

                SPlattenS Offline
                SPlattenS Offline
                SPlatten
                wrote on last edited by
                #7

                @artwaw , it will vary, to begin with I'm using small examples, see the size in the post: 34390236

                Kind Regards,
                Sy

                1 Reply Last reply
                0
                • artwawA artwaw

                  @SPlatten How large is the blob in your case? I think I remember reading the advice about large blobs in MariaDB that one should be careful since it is easy to run of memory in the stack or something.
                  In other words: might be it is worth to change to QSqlQuery *queryBlock = new QSqlQuery() etc. Again, one of the possibilities, I am not certain what fails there.

                  SPlattenS Offline
                  SPlattenS Offline
                  SPlatten
                  wrote on last edited by SPlatten
                  #8

                  @artwaw , the message that appears in dialog:

                  Lost connection to MySQL server during query QMYSQL3: Unable to execute statement
                  

                  And from the event viewer Application:

                  Aborted connection 3 to db: 'training' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)
                  

                  However in my code I check the array size for < ULONG_MAX which is 4GB before I try to submit it.

                  Kind Regards,
                  Sy

                  Christian EhrlicherC artwawA 2 Replies Last reply
                  0
                  • SPlattenS SPlatten

                    @artwaw , the message that appears in dialog:

                    Lost connection to MySQL server during query QMYSQL3: Unable to execute statement
                    

                    And from the event viewer Application:

                    Aborted connection 3 to db: 'training' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)
                    

                    However in my code I check the array size for < ULONG_MAX which is 4GB before I try to submit it.

                    Christian EhrlicherC Offline
                    Christian EhrlicherC Offline
                    Christian Ehrlicher
                    Lifetime Qt Champion
                    wrote on last edited by
                    #9

                    @SPlatten said in QSqlQuery, addBindValue, LargeBlob problem?:

                    max_allowed_packet

                    So hard to search for HeideSQL max_allowed_packet? The first hit gives you the config option...

                    Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                    Visit the Qt Academy at https://academy.qt.io/catalog

                    SPlattenS 1 Reply Last reply
                    1
                    • SPlattenS SPlatten

                      @artwaw , the message that appears in dialog:

                      Lost connection to MySQL server during query QMYSQL3: Unable to execute statement
                      

                      And from the event viewer Application:

                      Aborted connection 3 to db: 'training' host: 'localhost' (Got a packet bigger than 'max_allowed_packet' bytes)
                      

                      However in my code I check the array size for < ULONG_MAX which is 4GB before I try to submit it.

                      artwawA Offline
                      artwawA Offline
                      artwaw
                      wrote on last edited by
                      #10

                      @SPlatten said in QSqlQuery, addBindValue, LargeBlob problem?:

                      'max_allowed_packet' bytes

                      Configuration of the server doesn't permit the size you send.

                      For more information please re-read.

                      Kind Regards,
                      Artur

                      SPlattenS 1 Reply Last reply
                      0
                      • Christian EhrlicherC Christian Ehrlicher

                        @SPlatten said in QSqlQuery, addBindValue, LargeBlob problem?:

                        max_allowed_packet

                        So hard to search for HeideSQL max_allowed_packet? The first hit gives you the config option...

                        SPlattenS Offline
                        SPlattenS Offline
                        SPlatten
                        wrote on last edited by
                        #11

                        @Christian-Ehrlicher , found this https://docs.oracle.com/cd/E19509-01/820-6323/gicxk/index.html, modified my.ini, your so pissy, where you abused as a child?

                        Kind Regards,
                        Sy

                        Christian EhrlicherC 1 Reply Last reply
                        0
                        • artwawA artwaw

                          @SPlatten said in QSqlQuery, addBindValue, LargeBlob problem?:

                          'max_allowed_packet' bytes

                          Configuration of the server doesn't permit the size you send.

                          SPlattenS Offline
                          SPlattenS Offline
                          SPlatten
                          wrote on last edited by
                          #12

                          @artwaw , so I modified my.ini and restarted MariaDB service, now it appears to be ok, thank's for your help.

                          Kind Regards,
                          Sy

                          1 Reply Last reply
                          0
                          • SPlattenS SPlatten

                            @Christian-Ehrlicher , found this https://docs.oracle.com/cd/E19509-01/820-6323/gicxk/index.html, modified my.ini, your so pissy, where you abused as a child?

                            Christian EhrlicherC Offline
                            Christian EhrlicherC Offline
                            Christian Ehrlicher
                            Lifetime Qt Champion
                            wrote on last edited by
                            #13

                            @SPlatten said in QSqlQuery, addBindValue, LargeBlob problem?:

                            your so pissy

                            Because you're to lazy (or whatever) to search for the simplest things before you write a new question which every kid could resolve in less than a minute...

                            Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                            Visit the Qt Academy at https://academy.qt.io/catalog

                            SPlattenS 1 Reply Last reply
                            2
                            • Christian EhrlicherC Christian Ehrlicher

                              @SPlatten said in QSqlQuery, addBindValue, LargeBlob problem?:

                              your so pissy

                              Because you're to lazy (or whatever) to search for the simplest things before you write a new question which every kid could resolve in less than a minute...

                              SPlattenS Offline
                              SPlattenS Offline
                              SPlatten
                              wrote on last edited by
                              #14

                              @Christian-Ehrlicher , this forum is a resource full of knowledgeable people, just like a Google search is also a resource, how and what I choose to do is my choice.

                              Kind Regards,
                              Sy

                              1 Reply Last reply
                              0

                              • Login

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