PDA

View Full Version : Inserting PNG into Database - Escaping String



bhavya
16th March 2011, 14:15
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.



QByteArray ba;
QBuffer buffer(&ba);
buffer.open(QIODevice::WriteOnly);
image->save(&buffer, "PNG"); //image is a QImage

QString sql = QString( "INSERT INTO myTable ( blob ) VALUES ( %1 )").arg( ba.data() )



and when exec() this sql query, I get an error. Most probably I need to escape `ba.data()'
plz help

high_flyer
16th March 2011, 14:37
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

bhavya
16th March 2011, 16:27
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 ....


QByteArray ba;
QBuffer buffer(&ba);
buffer.open(QIODevice::WriteOnly);
img->save(&buffer, "PNG"); \\ img is a QImage


QString sql = QString( "INSERT INTO `TABLE` (`ImageData`) VALUES(:IMAGE) " );



//make query
QSqlQuery query = QSqlQuery();
query.prepare(sql);
query.bindValue(":IMAGE", ba.data());

if( query.exec(sql) == false )
{
QMessageBox::critical(this, QString("Error in Query"), query.lastError().databaseText() );
return;
}

Rhayader
16th March 2011, 16:59
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

high_flyer
16th March 2011, 17:01
Try:

query.bindValue(":IMAGE", ba);

bhavya
17th March 2011, 12:51
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



QString sql = QString( "INSERT INTO `Annotation` "
"(`PatientName`, `Modality`, `StudyDate`, `SeriesNumber`, `InstanceNumber`, "
"`AnnotationNote`, `AnnotationData`, `AnnotationDate`) "
"VALUES ( :PATIENTNAME, :MODALITY, :STUDYDATE, :SERIESNUMBER, :INSTANCENUMBER,"
":ANNOTATIONNOTE, :IMAGE, :ANNOTATIONDATE") ;


//make query
QSqlQuery query = QSqlQuery();
query.prepare(sql);
query.bindValue(":PATIENTNAME", patientName);
query.bindValue(":MODALITY", modality);
query.bindValue(":STUDYDATE", studyDate);
query.bindValue(":SERIESNUMBER", seriesNumber);
query.bindValue(":INSTANCENUMBER", sliceNumber);
query.bindValue(":ANNOTATIONNOTE", QString(""));
query.bindValue(":IMAGE", ba);
query.bindValue(":ANNOTATIONDATE", QString("CURRENT_DATE()"));


if( query.exec(sql) == false )
{
QMessageBox::critical(this, QString("Error in Query"), query.lastError().databaseText() );
return;
}




But still it is not working and giving error

Rhayader
17th March 2011, 16:48
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.