Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

QTSQLITE beginner question about "how to..."



  • Hi,

    I'm studying SQLITE and I think I understood the principle idea of working with it in Qt.
    I need to make a query that in SQLITE is written like:
    SELECT trackid, name, albumid
    FROM tracks
    WHERE albumid = (
    SELECT albumid
    FROM albums
    WHERE title = 'Let There Be Rock');

    I want to do this in Qt with QSqlQuery.addBindValue
    Let's suppose that QString data = "'Let There Be Rock'"

    I do the following:

        QString data = "'Let There Be Rock'";
        QSqlQuery query;
        query.prepare("SELECT trackid, name, albumid "
                      "FROM tracks "
                      "WHERE albumid = ("
                      "SELECT albumid "
                      "FROM albums "
                      "WHERE title = VALUES (?));");
        query.addBindValue(data);
        if (!query.exec()){
            qDebug() << "error query";
            return -1;
        }
        while (query.next()) {
            QString country = query.value(1).toString();
            qDebug() << country;
        }
    

    But this doen't work. How to make QtSql query with addBindValue to a WHERE expression?



  • @Please_Help_me_D

    What result do you get? Nothing? Wrong data? Is the binding or the SQL statement not working?

    Have you tried it with placeholder binding?
    Replace the ? with :title and then:
    bindValue(":title", "Your Title");

    Edit:

    Just remove the VALUES in your WHERE clause and try again. Cant test it atm, but I think it's wrong there.
    This will probably work:
    WHERE title = :title (keyword binding)
    or
    WHERE title = ? (positional binding)



  • @Please_Help_me_D

    What result do you get? Nothing? Wrong data? Is the binding or the SQL statement not working?

    Have you tried it with placeholder binding?
    Replace the ? with :title and then:
    bindValue(":title", "Your Title");

    Edit:

    Just remove the VALUES in your WHERE clause and try again. Cant test it atm, but I think it's wrong there.
    This will probably work:
    WHERE title = :title (keyword binding)
    or
    WHERE title = ? (positional binding)



  • @Pl45m4 thank you for reply
    I get empty result.

    You are right that I should remove VALUES. I did it and I tried to write the QString().arg() code that works:

        QString data = "'Let There Be Rock'";
        QSqlQuery query;
        query.prepare(QString("SELECT trackid, name, albumid "
                      "FROM tracks "
                      "WHERE albumid = ("
                      "SELECT albumid "
                      "FROM albums "
                      "WHERE title = %1);").arg(data));
        if (!query.exec()){
            qDebug() << "error query";
            return -1;
        }
        while (query.next()) {
            QString country = query.value(1).toString();
            qDebug() << country;
        }
    

    Also now the bindings started to work!
    Here is the code:

        QString data = "Let There Be Rock";
        QSqlQuery query;
        query.prepare("SELECT trackid, name, albumid "
                      "FROM tracks "
                      "WHERE albumid = ("
                      "SELECT albumid "
                      "FROM albums "
                      "WHERE title = ?);");
        query.addBindValue(data);
        if (!query.exec()){
            qDebug() << "error query";
            return -1;
        }
        while (query.next()) {
            QString country = query.value(1).toString();
            qDebug() << country;
        }
    

    Here is my observation: in the first case I write:

    QString data = "'Let There Be Rock'" // single internal quotes (I don't know how they are called)
    

    and in the second code:

    QString data = "Let There Be Rock" // there is no these single internal quotes
    

    I understand that in original SQLITE we should write these internal single quotes ''. Then that means that query.addBindValue(data); internally add these single quotes, right?



  • @Please_Help_me_D said in QTSQLITE beginner question about "how to...":

    I understand that in original SQLITE we should write these internal single quotes ''. Then that means that query.addBindValue(data); internally add these single quotes, right?

    Yeah, correct. You dont need the single quote characters.
    They are needed when adding a SQL string/ varchar inside the QString from QQuery.

    This would even cause multiple errors, I guess.
    (no valid query, because query text (string) ends with second " (right before the f) and even when it would execute the query, it would lead to an (SQL) error.)

    query.exec ("SELECT * FROM table WHERE name = "foo";");
    

    So use single quotes for varchars inside a query and just a standard QString (no extra quotes), when binding the string

    In your first case (QString.arg ()) you need the single quotes because it just replaces %1 with the text in your first argument... It's the same as writing directly inside that query, but with the only difference that you have variable arguments instead.



  • @Pl45m4 thank you for explanation!


Log in to reply