Invalid QSqlRecord after performing another query to DB

[crew4ok]

Hi everyone!

I have some problems with using QtSql.
So here's the code first:

QSqlQuery q1(db_connection), q2(db_connection);
q1.exec("SELECT * from users");
while(q1.next())
{
    q2.exec("delete from table1");
    if (!q2.isValid())
        qDebug() << "q2 is not valid";
 
    if (q1.isActive() && q1.isValid())
        qDebug() << q1.record();
}

QSqlQuery q1(db_connection), q2(db_connection);
q1.exec("SELECT * from users");
while(q1.next())
{
    q2.exec("delete from table1");
    if (!q2.isValid())
        qDebug() << "q2 is not valid";

    if (q1.isActive() && q1.isValid())
        qDebug() << q1.record();
}

To copy to clipboard, switch view to plain text mode 

So there are two queries to one database: q1 is absolutely correct and should return several result rows, but q1 is some wrong query.
The question is why QSqlQuery::record() of q1 returns empty data after executing query q2?

I'm using MySQL 5.5.
I repeated this behaviour on qt 4.7.1 and qt 4.8.1 in Windows and Linux.

P.S.: Sorry for my english.

[crew4ok]

Any suggestions? :/

[ChrisW67]

Works fine here with Sqlite but not MySql... it is going to be a MySQL quirk.

Here is my take:

#include <QtCore>
#include <QtSql>
#include <QDebug>
 
 
int main(int argc, char *argv[])
{
    QCoreApplication app(argc, argv);
 
    QSqlDatabase db =  QSqlDatabase::addDatabase("QMYSQL");
    db.setDatabaseName("testdb");
    db.setUserName("user");
    db.setPassword("password");
    if (db.open()) {
        // Create a test table
        db.exec("CREATE TABLE zzz_test (a int, b varchar(10))");
        db.exec("INSERT INTO zzz_test VALUES (1, 'Apple')");
        db.exec("INSERT INTO zzz_test VALUES (2, 'Banana')");
        db.exec("INSERT INTO zzz_test VALUES (3, 'Cherry')");
 
        QSqlQuery q1(db);
        QSqlQuery q2(db);  // Fails badly if declared here
        if (q1.exec("SELECT * from zzz_test")) {
            while(q1.next()) {
                // QSqlQuery q2(db);  // fails less badly here because it is destroyed at the end of the loop
                qDebug() << "Before q2" << q1.record() << q1.lastError().text();
 
                // Any failing query
                if (!q2.exec("select * from zzz_nonexistent"))
                    qDebug() << "q2 failed" << q2.lastError().text();
                // q2.clear(); // Adding this fixes the next line
 
                qDebug() << "After q2" << q1.record() << q1.lastError().text();
            }
        }
 
        db.exec("DROP TABLE zzz_test");
    }
 
    return 0;
}

#include <QtCore>
#include <QtSql>
#include <QDebug>


int main(int argc, char *argv[])
{
    QCoreApplication app(argc, argv);

    QSqlDatabase db =  QSqlDatabase::addDatabase("QMYSQL");
    db.setDatabaseName("testdb");
    db.setUserName("user");
    db.setPassword("password");
    if (db.open()) {
        // Create a test table
        db.exec("CREATE TABLE zzz_test (a int, b varchar(10))");
        db.exec("INSERT INTO zzz_test VALUES (1, 'Apple')");
        db.exec("INSERT INTO zzz_test VALUES (2, 'Banana')");
        db.exec("INSERT INTO zzz_test VALUES (3, 'Cherry')");

        QSqlQuery q1(db);
        QSqlQuery q2(db);  // Fails badly if declared here
        if (q1.exec("SELECT * from zzz_test")) {
            while(q1.next()) {
                // QSqlQuery q2(db);  // fails less badly here because it is destroyed at the end of the loop
                qDebug() << "Before q2" << q1.record() << q1.lastError().text();

                // Any failing query
                if (!q2.exec("select * from zzz_nonexistent"))
                    qDebug() << "q2 failed" << q2.lastError().text();
                // q2.clear(); // Adding this fixes the next line

                qDebug() << "After q2" << q1.record() << q1.lastError().text();
            }
        }

        db.exec("DROP TABLE zzz_test");
    }

    return 0;
}

To copy to clipboard, switch view to plain text mode 

I'd be inclined to raise a bug.