Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct
QSqlQuery, addBindValue, LargeBlob problem?
SPlatten last edited by 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.
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.
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
SPlatten last edited by 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.
So hard to search for
HeideSQL max_allowed_packet? The first hit gives you the config option...
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.
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.