QSqlQuery SELECT

gabor53

Hi,
I use QSQLITE. I would like to choose a record from a database based in the ID number. I have the following:

  QSqlQuery query_getImage ("SELECT Pic FROM Items WHERE ID == FriendID");

FriendID is a QString. How should I write this line correctly so it recognizes FriendID as a QString?
Thank you.

jsulm

@gabor53 Do you mean FriendID is a QString variable?
You should take a look at the documentation. It even contains examples which answer your question: http://doc.qt.io/qt-5/qsqlquery.html

QSqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
                  "VALUES (:id, :forename, :surname)");
query.bindValue(":id", 1001);
query.bindValue(":forename", "Bart");
query.bindValue(":surname", "Simpson");
query.exec();

In this example id, forename and surename are replaced by int and string (you can use your variable instead of a string literal).

VRonin

SQL does not use ==

 QSqlQuery query_getImage;
query_getImage.prepare("SELECT Pic FROM Items WHERE ID = :friendID");
query_getImage.bindValue(":friendID",FriendID);
if(query_getImage.exec()){
// success
}

if FriendID is a string but you want it to go into the query as an int use query_getImage.bindValue(":friendID",FriendID.toInt()); on the other hand if FriendID is int and you want it to go in as a string use query_getImage.bindValue(":friendID",QString::number(FriendID));

gabor53

@VRonin
I did the following:

 QSqlQuery query_getImage.prepare ("SELECT Pic FROM Items WHERE ID = :FriendID");
query_getImage.bindValue (":ID",FriendID);
            if(query_getImage.exec())
                {
                    qDebug() << "The query is active.";
                }
            else
                {
                    qDebug() << "The query is NOT active." << query_getImage.lastError ();
                }

I get the following error messages:

C:\Programming\Projects\Folkfriends_1_0\mainwindow.cpp:123: error: expected initializer before '.' token
QSqlQuery query_getImage.prepare ("SELECT Pic FROM Items WHERE ID = :FriendID");
^
C:\Programming\Projects\Folkfriends_1_0\mainwindow.cpp:124: error: 'query_getImage' was not declared in this scope
query_getImage.bindValue (":ID",FriendID);
^
What did I miss?

VRonin

you have to separate the declaration from the use

 QSqlQuery query_getImage;
 query_getImage.prepare ("SELECT Pic FROM Items WHERE ID = :FriendID");

also query_getImage.bindValue (":ID",FriendID); is wrong, it should be query_getImage.bindValue (":FriendID",FriendID);

mjsurette

@gabor53
Another idiom I use if I'm only running a query once is

QSqlQuery query_getImage();
QString qryTxt= "SELECT Pic FROM Items WHERE ID == '%1'; ";
query_getImage.exec(qryTxt.arg(FriendID));

Mike

VRonin

@mjsurette please don't! especially if FriendID can be somehow manipulated by the user. See here for the reason why. I'll just quote the first line:

[It] can destroy your database

mjsurette

@VRonin

Thanks for the heads up.

Mike.

gabor53

The following worked for me:

QSqlQuery query_getImage;
            query_getImage.prepare ("SELECT Pic FROM Items WHERE ID = :FriendID");
            query_getImage.bindValue (":FriendID",FriendID);

            if(query_getImage.exec())
                {
                    qDebug() << "The query query_getImage is active.";
                }
            else
                {
                    qDebug() << "The query is NOT active." << query_getImage.lastError ();
                }

            query_getImage.first ();
            QByteArray pixArray;
            pixArray = query_getImage.value(0).toByteArray ();

            qDebug() << "pixArray size: " << pixArray.size ();
            QPixmap Pixmap;
            Pixmap.loadFromData (pixArray);

Thank you for all your help.