QSqlQuery in Qt6: in-place vs prepared

dviktor

According to the source, under the hood setQuery() for in-place queries just calls another overloaded setQuery():

void QSqlQueryModel::setQuery(const QString &query, const QSqlDatabase &db)
{
    setQuery(QSqlQuery(query, db));
}

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

dviktor

Using positional parameters binding gives the same result. Also tested on Windows build - pretty much the same...

PS
This is the dump of QSqlQuery contents after exec()ing it:

1 "H**01" "m**ring" QDate("2025-08-06") QDate("2026-08-05") 252
3 "H**03" "m**ring" QDate("2025-08-06") QDate("2026-08-05") 252
4 "H**04" "m**ring" QDate("2025-08-06") QDate("2026-08-05") 252
5 "H**05" "m**ring" QDate(Invalid) QDate(Invalid) 0

So, basically, parameter binding works - resulting query contains all necessary lines. However, for some reason, model isn't able to get this data from original query. I've even tried to tailor my own reimplementation of data() to the simplest return QSqlQueryModel::data(item, role); - and still no luck...

dviktor

OK, I was able to boil it down to the MariaDB/Qt typing. I've replaced DATE type in database with INT and changed my SQL query accordingly (direct difference instead of DATEDIFF) and it worked fine! Also I remember that on Qt5 (IIRC 5.12) original query worked fine. Seems 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

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.