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.


  • Qt Champions 2018

    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.


  • Qt Champions 2018

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


  • Qt Champions 2018

    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?


  • Qt Champions 2018

    @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 :(


  • Qt Champions 2018

    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