QSqlQuery SELECT



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


  • Moderators

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



  • 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));



  • @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?



  • 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);



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



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



  • @VRonin

    Thanks for the heads up.

    Mike.



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


Log in to reply
 

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