QSqlQuery in Qt6: in-place vs prepared

Christian Ehrlicher

Please provide a minimal compilable example which does not involve a model but also creates the table

JonB

@dviktor
TL;DR: You have posted 3 "pages" of information/questions and I at least do not know exactly what you are asking or where your problem lies. And please don't paste links to external screenshots which are not even accessible to people other than you.

For example, you seem to spend most of your time talking about parameters and binding and then write "So, basically, parameter binding works".... Does your problem relate to binding? Does it relate to using a query with LIKE and binding?

So, it looks like the problem lies within QSqlQuery's prepare(), bindValue() and exec() sequence for parameter binding

I doubt it.

Here are some of the things I think you should do:

QSqlQuery::prepare() return a value for you to check --- so do so. As does exec() --- so do that too.
Remove everything to do with a view/table view and concentrate just on the model/SQL query.
If there is a problem potentially with a date being invalid, change your SQL query to use whatever MariaDB has to turn a date into text (some sort of CONVERTTOTEXT(SQL_date_column_or_expression)) and return it as such, so that you can see what is there.

I will stop here as you have just made a new post..... Let me look at that....

JonB

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

I've replaced DATE type in database with INT and changed my SQL query accordingly (direct difference instead of DATEDIFF)

In the course of your questions you jump between various SQL queries and show output which does not seem to relate to the queries you show. DATEDIFF() has never returned a SQL DATE, it returns an INT. Have you somehow told your model that this column is a QDate or SQL DATE when in fact it is an integer? How come you show certain rows with a valid QDate and others where it is invalid? I don't see how that would arise, either the column is a date or an integer but not a mixture across different rows.

like starting from some specific Qt version the usage of parameter binding together with DATE/QDate changed somehow and now QSqlQueryModel gives invalid QDate instances

What binding? Parameter binding relates to input parameters used e.g. in a WHERE clause. Sorry but it cannot influence columns returned, nor affect something about whether that data is a date or not....

I would first have no view and no model and verify what you get back with plain QSqlQuerys. When that is working I would move to QSqlQueryModel but still no view and verify that. Finally I would attach a QTableView to the model and verify that. Baby steps each time....

dviktor

My intention was to edit original post but I've spent more than 3600 seconds limit for edits so I've just added new info in separate posts.
So I'll try to summarize current observations and results:

My original scheme contained just two columns with DATE type - columns ts and upto in MariaDB. DATEDIFF is used just to calculate difference between these columns and return it as integer.
Client written in Qt handles columns ts and upto as DATE and tries to convert it to human-readable form with QDateTime::fromString(value.toString(), Qt::ISODate).toString("dd.MM.yyyy");. Difference column is treated as-is and used only for background settings to warn the user
I've done debug dumps of query contents right after exec()ing it and all of expected values were in place for both in-place and prepared queries. However, if I try to debug-print them from EquipmentModel::data then I see different behavior in case of in-place and prepared queries.
If I replace DATE in database scheme to INT and DATEDIFF() to direct difference then things start to work fine - no more Invalid values seen in data() method.

JonB

@dviktor
So can we agree that input parameters and binding are not in any way an issue and we can ignore that part of your writings?

QDateTime::fromString(value.toString(), Qt::ISODate): this can fail depending on the exact content/format of value.toString(). Also what type is value to begin with? If it's a QDate I don't see the point of converting it to a string and then trying to parse that as an ISO date. You should look at what QVariant::typeName() and QVariant::metaType() return on the value. As I wrote earlier, if you are saying some date values seem to be valid while others are not, write a query which returns it as a string from the database and examine that.

If you claim that all rows returned are valid if examined directly but not when it goes through QSqlQueryModel and data() then produce a minimal reproducer which shows exactly this happening. If your table holds just two rows, one which works and one which does not, that would be great.

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.