[SOLVED QSQLLITE] Problem retrieving records

[skruffynerherder]

I have a problem retrieving data from a sqllite database, lasterror().text returns with "No query Unable to fetch row", running the query directly in the database gives me no issues and works.

QStringList groups;
    QString SQL =   "SELECT groups.name FROM groups "
                    "INNER JOIN shiptypes ON groups.id = shiptypes.groupid "
                    "INNER JOIN ships ON shiptypes.id = ships.shiptype "
                    "INNER JOIN reports ON ships.id = reports.ship "
                    "INNER JOIN posts ON posts.id = reports.postid "
                    "WHERE posts.stamp = :stamp";
    QSqlQuery groupQuery(connection);
    groupQuery.prepare(SQL);
    groupQuery.bindValue(":stamp",reportKey);
    if (!groupQuery.exec()) {
        errorCode = 510;
        error = true;
        errorMessage = groupQuery.lastError().text();
    } else {
        while (groupQuery.next()) {
            groups.append(groupQuery.value(0).toString());
        }
    }

QStringList groups;
    QString SQL =   "SELECT groups.name FROM groups "
                    "INNER JOIN shiptypes ON groups.id = shiptypes.groupid "
                    "INNER JOIN ships ON shiptypes.id = ships.shiptype "
                    "INNER JOIN reports ON ships.id = reports.ship "
                    "INNER JOIN posts ON posts.id = reports.postid "
                    "WHERE posts.stamp = :stamp";
    QSqlQuery groupQuery(connection);
    groupQuery.prepare(SQL);
    groupQuery.bindValue(":stamp",reportKey);
    if (!groupQuery.exec()) {
        errorCode = 510;
        error = true;
        errorMessage = groupQuery.lastError().text();
    } else {
        while (groupQuery.next()) {
            groups.append(groupQuery.value(0).toString());
        }
    }

To copy to clipboard, switch view to plain text mode 

I've checked reportKey and it contains the correct value for WHERE clause, so that isn't an issue. I've also checked database connection and it connects successfully with database. Any suggestions?

[wysota]

What does QSqlQuery::prepare() return?

[skruffynerherder]

It returns false

bool prep = groupQuery.prepare(SQL);

bool prep = groupQuery.prepare(SQL);

To copy to clipboard, switch view to plain text mode 

...

std::cout <<"<br />prep: ";
std::cout <<std::boolalpha <<prep <<"<br />";

std::cout <<"<br />prep: ";
std::cout <<std::boolalpha <<prep <<"<br />";

To copy to clipboard, switch view to plain text mode 

---

Added after 6 minutes:

Changing the prepare statement to ODBC style placeholders (?) instead of oracle style placeholders gives me another kind of error message "Parameter count mismatch"

QStringList groups;
    QSqlQuery groupQuery(connection);
    bool prep = groupQuery.prepare("SELECT groups.name FROM groups "
                                   "INNER JOIN shiptypes ON groups.id = shiptypes.groupid "
                                   "INNER JOIN ships ON shiptypes.id = ships.shiptype "
                                   "INNER JOIN reports ON ships.id = reports.ship "
                                   "INNER JOIN posts ON posts.id = reports.postid "
                                   "WHERE posts.stamp = ?");
    groupQuery.bindValue(0,reportKey);
    if (!groupQuery.exec()) {
        errorCode = 510;
        error = true;
        errorMessage = groupQuery.lastError().text();
    } else {
        while (groupQuery.next()) {
            groups.append(groupQuery.value(0).toString());
        }
    }

QStringList groups;
    QSqlQuery groupQuery(connection);
    bool prep = groupQuery.prepare("SELECT groups.name FROM groups "
                                   "INNER JOIN shiptypes ON groups.id = shiptypes.groupid "
                                   "INNER JOIN ships ON shiptypes.id = ships.shiptype "
                                   "INNER JOIN reports ON ships.id = reports.ship "
                                   "INNER JOIN posts ON posts.id = reports.postid "
                                   "WHERE posts.stamp = ?");
    groupQuery.bindValue(0,reportKey);
    if (!groupQuery.exec()) {
        errorCode = 510;
        error = true;
        errorMessage = groupQuery.lastError().text();
    } else {
        while (groupQuery.next()) {
            groups.append(groupQuery.value(0).toString());
        }
    }

To copy to clipboard, switch view to plain text mode 

prep still returns false

[wysota]

Maybe your connection object is invalid.

[skruffynerherder]

I use insert and select statements with the same object and that works just fine ...

---

Added after 23 minutes:

Hmm, might stumbled upon a solution. I created the sqllite database with SQliteStudio 2.1.2 and if the driver in QT uses a sqllite version that is not the same as the one Sqllitestudio uses there might be some conflicts reading/writing data to the database? I am going to create some code routines in my application to create the database using QT driver and then see what happens. Will report back

[skruffynerherder]

Nope, that didn't solve my issues either

---

Added after 31 minutes:

I solved it.

I created a new db instance on the stack and called db.open(), first THEN would it execute the SQL statements even though the connection db object which I have allocated for the entire object works elsewhere and I didn't have to call db.open() there. GAAAAAAHH! /o\