QSqlQuery in Qt6: in-place vs prepared

dviktor

yes, because raw query dump shows all expected entries, it looks like parameter binding at its own is not a problem, but it somehow interferences when model tries to retrieve data. Regarding conversion to string and other potential problems with model: I've even tried to get rid of my custom model at all and assign plain QSqlQueryModel to my view without column hiding - effectively, raw dump with QTableView - and the problem with DATE columns still persisted. So that's not related to my custom model, and as I said earlier - change to INT instead of DATE "fixes" it. I'll post minimals tomorrow together with data dumps for both DATE and INT columns. Thank you for suggestions!

JonB

@dviktor said in QSqlQuery in Qt6: in-place vs prepared:

it looks like parameter binding at its own is not a problem, but it somehow interferences when model tries to retrieve data

This should really not be possible. It might affect which rows are returned if the parameter is used in a WHERE, but that is a different matter.

and the problem with DATE columns still persisted

Confirm what QVariant::metaType() returns for your SQL DATE columns. I would expect it not to return a string but either QDate or QDateTime?
Print out the value.toString(). Is it really parseable as a Qt::ISODate?
As I said, temporarily change the SQL side to return a string of its date and examine that.
As a random thought, any chance you are getting USA-style-date-strings and then trying to convert them as though they were ISO dates? That would produce invalid dates on some values but not others, where the day number is regarded as a month number and is greater than 12.

dviktor

Finally, I was able to make the simplest reproducible example.
CMakeLists.txt
main.cpp
main.h contains only DB credentials, I will not post it here.

Final result:

As you can see I have 12 tests: 6 for DATE (4 textual dump + 2 views) and 6 for INT (4 textual dump + 2 views) types for columns in MariaDB. Also there are table definitions.

From the results it can be seen that the only problematic case is DATE + prepared query + QTableView. For the simplicity I haven't used custom model at all - just stayed with QSqlQueryModel. Also re-checking the contents of separate queries and models shows that their data is valid and contains expected records. But the view doesn't display it properly for some reason

dviktor

If I change my basic SQL query statement (the first line of it) from:
"SELECT DISTINCT e.id, e.name, e.type, v.ts, v.upto, v.d_left "
to:
"SELECT DISTINCT e.id, e.name, e.type, DATE_FORMAT(v.ts, '%d.%m.%Y'), DATE_FORMAT(v.upto, '%d.%m.%Y'), v.d_left "
(remember that I still use plain QSqlQueryModel so no custom data conversions involved) then all works fine!

So, it looks like QSqlQueryModel misbehaves somehow in connection with QTableView when some of the columns are of QDate type

Christian Ehrlicher

Your test still does not create and fill the table. Fix it if you want help from me.

dviktor

@Christian-Ehrlicher I've made direct dump of my test database. You can quickly feed it to the MariaDB with:
cat dbtest.sql | mariadb -u root -p -D dbtest
assuming dbtest database already created

PS: added '05' strings to make search return the same results
PPS: '05' was used as search string in my MRE

Christian Ehrlicher

This is working fine for me:

#include <QtSql>
#include <QtWidgets>

int main(int argc, char* argv[])
{
    QApplication a(argc, argv);
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setDatabaseName("testdb");
    db.setUserName("");
    db.setPassword("");
    db.setHostName("");
    db.setConnectOptions("MYSQL_OPT_SSL_VERIFY_SERVER_CERT=FALSE");
    if (!db.open()) {
        qDebug() << db.lastError();
        return 1;
    }
    QSqlQuery query;
    query.exec("DROP TABLE IF EXISTS test");
    if (!query.exec("CREATE TABLE test (id int, ts date)")) {
        qDebug() << query.lastError();
        return 1;
    }
    QString insertSql1 = "INSERT INTO test (id, ts) VALUES (1, now())";
    if (!query.exec(insertSql1)) {
        qDebug() << query.lastError();
        return 1;
    }
    QTableView tv1;
    auto model1 = new QSqlQueryModel;
    model1->setQuery(QSqlQuery("SELECT * FROM test WHERE id = 1"));
    tv1.setModel(model1);
    tv1.show();

    QTableView tv2;
    auto model2 = new QSqlQueryModel;
    QSqlQuery q;
    q.prepare("SELECT * FROM test WHERE id = :id");
    q.bindValue(":id", 1);
    q.exec();
    model2->setQuery(std::move(q));
    tv2.setModel(model2);
    tv2.show();

    int ret = a.exec();
    query.exec("DROP TABLE IF EXISTS test");
    db.close();
    return ret;
}

Please provide a minimal, compilable example to reproduce your problem.

dviktor

I've already attached all source code files as well as database dump. I can surely reproduce the problem described on attached dataset

Christian Ehrlicher

I told you what I need and also showed you that it works correctly so... Prove me wrong

dviktor

I don't argue that it may work on this simplest two-column example without any issues but that's not my case. My SQL query is a bit more sophisticated and includes JOINs as well as calculated columns (with MAX, DATEDIFF etc). Checking with bare SQL request via mariadb command line client or phpMyAdmin console shows that the query line itself is ok - I get all expected rows and columns.

And as it shown on the screenshots above the problem goes away if I wrap DATE-typed columns with DATE_FORMAT so I effectively get string objects instead of date. So that's why I've decided it's a bug somewhere in Qt. I've also prepared minimal database sample which exposes erratic behavior. Moreover, the database itself may be provided by third-party service and not by means of Qt-aided creation. Who knows - may be there are some difference in a way database were prepared.

JonB

@dviktor
I looked at your "minimal" repro. The queries are horrendously long and complex, and require many tables with many columns. If I were you and I wanted help/someone to look at it I would spend my time reducing to an absolute minimum, e.g. does it require a JOIN at all, does it show up with one JOIN, do I really need a calculated column, do I need MAX() or other "aggregate", does it matter whether a column is DATE versus DATETIME, does the WHERE clause matter, etc. I would hope to produce the absolute minimum code and database where I could say: "if you run this query it works fine but as soon as I make just this one little change it goes wrong". Up to you.