QTSQLITE beginner question about "how to..."

[Please_Help_me_D]

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?

[Pl45m4]

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

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

[Pl45m4]

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

[Please_Help_me_D]

@Pl45m4 thank you for explanation!