QT 5.8 image from database error



  • Hi!

    I stored an image (ca.: 1.6 Mb) on MSSQL2012 Express database, when i query it, i get an error:

    QSqlQuery query(db);
    	query.prepare("select picture from ads");
    		
    	if (query.exec())
    	{
    		while (query.next())
    		{
    			pixdata = query.value(0).toByteArray();
    			qDebug() << "image:" << pixdata.toHex();
    		}
    	} else qDebug() << query.lastError().text();
    	pixmap.loadFromData(pixdata);
    

    The pixdata is only 8 byte long, the qDebug output: image: "89504e470d0a1a0a".
    Query the pixmap from the sqsh or management studio, I get all of the data: 0x89504E470D0A1A0A0000000D494844520000078000000438080200000067B1... etc.
    Interestingly, the first missing byte is NULL.

    Thanks, in advance!


  • Lifetime Qt Champion

    Hi,

    How did you store this picture in the first place ?
    What type of fields are you using ?



  • Hi SGaist!

    I uploaded the file to a varbinary(max) column.



  • I load images with that method from the same version of server successfully.

    The problem you are experiencing is just an unfortunate coincidence: toHex() returns QByteArray, qDebug() << tries to display it as it was an ascii string so stops when it reaches '\0'.

    qDebug() << "image size:" << pixdata.size(); should return the correct amount of bytes.

    pixmap.loadFromData(pixdata); is maybe in the wrong place? there it will load the image of the last row of the table



  • @VRonin pixdata.size() is 8.
    I have only one record in the database.



  • @Kutyus said in QT 5.8 image from database error:

    pixdata

    What type is pixdata ?



  • @VRonin pixdata is QByteArray, pixmap is QPixmap.


  • Lifetime Qt Champion

    Might be a silly question but did you check that the data was correctly saved in the database ?



  • @SGaist I modified the query to:

    query.prepare("select DATALENGTH(picture), picture from ads");
    

    and

    qDebug() << "image size:" << query.value(0).toInt();
    

    The image size is 1715323, it is the uploaded file size in bytes, and the sqsh and management studio restore more than 8 bytes.



  • Ok, after some digging, the problem is with varbinary(MAX) change it to varbinary(8000) and everything works. of course this is a problem for you as picture > 8000 bytes.


    EDIT.
    I was trying to replicate a minimal example to submit a bug report but couldn't reproduce (Qt 5.5 and 5.9).
    What version of Qt are you using?

    CREATE TABLE [dbo].[TestBinary](
    	[TestMax] [varbinary](max) NULL,
    	[TestNum] [varbinary](8000) NULL
    ) 
    
    INSERT INTO TestBinary
               (TestMax
               ,TestNum)
         VALUES
               (0x89504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B189504E470D0A1A0A0000000D494844520000078000000438080200000067B1
               ,0x89504E470D0A1A0A0000000D494844520000078000000438080200000067B1
    		   )
    
    #include <QApplication>
    #include <QSqlDatabase>
    #include <QSqlQuery>
    #include <QDebug>
    bool openDB(QSqlDatabase& db)
    {
        if (!db.isValid()) {
            db = QSqlDatabase::addDatabase("QODBC", "MyDatabase");
            db.setDatabaseName(<INSERT HERE YOUR CONNECTION STRING>);
        }
        if (db.isOpen())
            return true;
        if (db.open())
            return true;
        return false;
    }
    int main(int argc, char *argv[])
    {
        QApplication app(argc,argv);
        QSqlDatabase db;
        if(!openDB(db))
            return -1;
        QSqlQuery testQuery(db);
        testQuery.prepare("SELECT TestMax,TestNum FROM TestBinary");
        if(!testQuery.exec())
             return -1;
        if(!testQuery.next())
             return -1;
        const QByteArray TestMax = testQuery.value(0).toByteArray();
        const QByteArray TestNum = testQuery.value(1).toByteArray();
        qDebug() << "varbinary(MAX) size: " << TestMax.size();
        qDebug() << "varbinary(8000) size: " << TestNum.size();
        return 0;
    }
    

    correctly outputs:

    varbinary(MAX) size:  8122
    varbinary(8000) size:  31
    


  • @VRonin I used a tds plugin earlier, I have rebuild Qt with odbc driver, but I can not connect to the database, which driver you use?



  • @Kutyus ODBC from online installer of Qt 5.5 and 5.9 on Windows for MSVC2013



  • @VRonin Ahh, I use a Banana Pi with linux.
    I connected with odbc over freetds, but the pixdata size is 4096 :(
    I try to upgrade freetds now. I think it is a driver bug.



  • @VRonin Not working, i compiled the freetds 1.00.47, and i recompiled the tds plugin, but the pixdata is only 8 byte :(



  • Ok, now that we know it's a plugin problem we can see if we can do something about it.

    The first thing that comes to mind is storing your binarydata in a varchar (no need for unicode) using base64.

    This is easy if you use QByteArray for both reading and writing, just use toBase64()



  • @VRonin Thank you for tip, the trick with base64 encoded file (to varchar(max) field) is working.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.