Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

QSqlQuery, addBindValue, LargeBlob problem?



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



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



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



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

    is there anyway I can find out

    Documentation to both, I suppose?



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



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



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



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


  • Lifetime Qt Champion

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



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

    'max_allowed_packet' bytes

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



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



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


  • Lifetime Qt Champion

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



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


Log in to reply