Results 1 to 7 of 7

Thread: Inserting PNG into Database - Escaping String

  1. #1
    Join Date
    Mar 2011
    Posts
    4
    Thanks
    2
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Inserting PNG into Database - Escaping String

    Hi all,

    I am trying to insert a PNG image into a mysql database using Qt.
    I am facing the problem to escape special characters into image and
    I cannot find any function in Qt related to this.

    I am attaching the piece of my code for this.

    Qt Code:
    1. QBuffer buffer(&ba);
    2. buffer.open(QIODevice::WriteOnly);
    3. image->save(&buffer, "PNG"); //image is a QImage
    4.  
    5. QString sql = QString( "INSERT INTO myTable ( blob ) VALUES ( %1 )").arg( ba.data() )
    To copy to clipboard, switch view to plain text mode 

    and when exec() this sql query, I get an error. Most probably I need to escape `ba.data()'
    plz help
    Last edited by high_flyer; 16th March 2011 at 14:31. Reason: code tags

  2. #2
    Join Date
    Jan 2006
    Location
    Munich, Germany
    Posts
    4,714
    Thanks
    21
    Thanked 418 Times in 411 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: Inserting PNG into Database - Escaping String

    have look below in the comments of this link, there is a Qt example for this:
    http://dev.mysql.com/doc/refman/5.0/en/blob.html
    ==========================signature=============== ==================
    S.O.L.I.D principles (use them!):
    https://en.wikipedia.org/wiki/SOLID_...iented_design)

    Do you write clean code? - if you are TDD'ing then maybe, if not, your not writing clean code.

  3. #3
    Join Date
    Mar 2011
    Posts
    4
    Thanks
    2
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: Inserting PNG into Database - Escaping String

    Hey thanks a lot for your reply I did something very similar but it is still not working..

    I am attaching the new code for you ....

    Qt Code:
    1. QBuffer buffer(&ba);
    2. buffer.open(QIODevice::WriteOnly);
    3. img->save(&buffer, "PNG"); \\ img is a QImage
    4.  
    5.  
    6. QString sql = QString( "INSERT INTO `TABLE` (`ImageData`) VALUES(:IMAGE) " );
    7.  
    8.  
    9.  
    10. //make query
    11. QSqlQuery query = QSqlQuery();
    12. query.prepare(sql);
    13. query.bindValue(":IMAGE", ba.data());
    14.  
    15. if( query.exec(sql) == false )
    16. {
    17. QMessageBox::critical(this, QString("Error in Query"), query.lastError().databaseText() );
    18. return;
    19. }
    To copy to clipboard, switch view to plain text mode 
    Last edited by high_flyer; 16th March 2011 at 16:55. Reason: code tags

  4. #4
    Join Date
    Aug 2009
    Location
    Greece
    Posts
    69
    Thanks
    2
    Thanked 14 Times in 14 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Inserting PNG into Database - Escaping String

    Change
    query.bindValue(":IMAGE", ba.data());
    to
    query.bindValue(":IMAGE", ba);

    and make sure that the blob supports the size of the image. In MySQL it is very possible that instead of BLOB you may need LONGBLOB. In SQLite I think there's no problem.
    The above code works fine for me for a long time now

  5. #5
    Join Date
    Jan 2006
    Location
    Munich, Germany
    Posts
    4,714
    Thanks
    21
    Thanked 418 Times in 411 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows

    Default Re: Inserting PNG into Database - Escaping String

    Try:
    Qt Code:
    1. query.bindValue(":IMAGE", ba);
    To copy to clipboard, switch view to plain text mode 
    ==========================signature=============== ==================
    S.O.L.I.D principles (use them!):
    https://en.wikipedia.org/wiki/SOLID_...iented_design)

    Do you write clean code? - if you are TDD'ing then maybe, if not, your not writing clean code.

  6. #6
    Join Date
    Mar 2011
    Posts
    4
    Thanks
    2
    Qt products
    Qt4
    Platforms
    Unix/X11

    Default Re: Inserting PNG into Database - Escaping String

    Hi again,

    I have done as suggested by `high_flyer', `Rhayader'.
    When I do query.exec()

    I have following error :::
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':IMAGE, CURRENT_DATE() )' at line 1

    And my query string looks like this-
    INSERT INTO `Annotation` (`PatientName`, `Modality`, `StudyDate`, `SeriesNumber`, `InstanceNumber`, `AnnotationNote`, `AnnotationData`, `AnnotationDate`) VALUES ( 'a_1tumour_1node ', 'M_PT', '2008-04-03', '0', '99', '', :IMAGE, CURRENT_DATE() )


    Added after 14 minutes:


    I also tried


    Qt Code:
    1. QString sql = QString( "INSERT INTO `Annotation` "
    2. "(`PatientName`, `Modality`, `StudyDate`, `SeriesNumber`, `InstanceNumber`, "
    3. "`AnnotationNote`, `AnnotationData`, `AnnotationDate`) "
    4. "VALUES ( :PATIENTNAME, :MODALITY, :STUDYDATE, :SERIESNUMBER, :INSTANCENUMBER,"
    5. ":ANNOTATIONNOTE, :IMAGE, :ANNOTATIONDATE") ;
    6.  
    7.  
    8. //make query
    9. QSqlQuery query = QSqlQuery();
    10. query.prepare(sql);
    11. query.bindValue(":PATIENTNAME", patientName);
    12. query.bindValue(":MODALITY", modality);
    13. query.bindValue(":STUDYDATE", studyDate);
    14. query.bindValue(":SERIESNUMBER", seriesNumber);
    15. query.bindValue(":INSTANCENUMBER", sliceNumber);
    16. query.bindValue(":ANNOTATIONNOTE", QString(""));
    17. query.bindValue(":IMAGE", ba);
    18. query.bindValue(":ANNOTATIONDATE", QString("CURRENT_DATE()"));
    19.  
    20.  
    21. if( query.exec(sql) == false )
    22. {
    23. QMessageBox::critical(this, QString("Error in Query"), query.lastError().databaseText() );
    24. return;
    25. }
    To copy to clipboard, switch view to plain text mode 



    But still it is not working and giving error
    Last edited by high_flyer; 17th March 2011 at 13:58. Reason: code tags

  7. #7
    Join Date
    Aug 2009
    Location
    Greece
    Posts
    69
    Thanks
    2
    Thanked 14 Times in 14 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Inserting PNG into Database - Escaping String

    If your MySQL column is of DATE type use
    query.bindValue(":ANNOTATIONDATE", QDate::currentDate()");

    If the column is a VARCHAR you can use the QDate::toString(const QString & format) functions.
    Probably it is something like QDate::currentDate().toString("yyyy-MM-dd") but I cannot test it now.
    Check the QDate documentation for more.

  8. The following user says thank you to Rhayader for this useful post:

    bhavya (17th March 2011)

Similar Threads

  1. Escaping %1 with QString::arg()
    By marcvanriet in forum Newbie
    Replies: 2
    Last Post: 20th January 2011, 02:38
  2. Replies: 2
    Last Post: 13th February 2010, 19:33
  3. inserting content in database
    By codeman in forum Qt Programming
    Replies: 2
    Last Post: 3rd June 2009, 17:47
  4. Problem while inserting the data into database
    By sudheer168 in forum Qt Programming
    Replies: 2
    Last Post: 13th December 2008, 13:22
  5. inserting string > 127 characters to MS Access
    By jh in forum Qt Programming
    Replies: 0
    Last Post: 12th May 2006, 18:11

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.