QSqlQuery worked on Linux but not Windows - Parameter mismatch

kesterpm

The following code worked in Linux. I'm trying to test my solution on windows now. I'm using sqlite. This code get's a parameter mismatch error on the exec() statement.

QSqlQuery select("select parameter_value from application_settings where parameter_name = ?");
select.bindValue(0, t_parametername);
if (select.exec())
{
    if (select.next())
        return select.value(0).toString();
    else
        return QString();
}
else
{
    QMessageBox::critical(nullptr, QObject::tr("Database Access Failed"), QString("Failed to access a saved setting. You may need to restart Project Notes.\n\nError:\n%1").arg(select.lastError().text()) );
    return QString();
}

kesterpm

OK, that helped. I'll need to change to using prepare() everywhere. I am using 5.15.2 on linux as well, but this is a sqlite issue. See my code change below. The error that prepare() caused was "RIGHT and FULL OUTER JOINs are currently not supported." My sqlite database had a view with a RIGHT JOIN. I removed it and everything works now. The join was on a view I wasn't even using here.

QSqlQuery select;
if (!select.prepare("select parameter_value from application_settings where parameter_name = ?"))
    qDebug() << "Prepare Failed: " << select.lastError();
select.bindValue(0, t_parametername);

if (select.exec())
{
    if (select.next())
        return select.value(0).toString();
    else
        return QString();
}
else
{
    qDebug() << "Last Error: " <<  select.lastError().databaseText() << "\n" << select.lastError().driverText() << "\n" << select.lastError().nativeErrorCode();

    QMessageBox::critical(nullptr, QObject::tr("Database Access Failed"), QString("Failed to access a saved setting. You may need to restart Project Notes.\n\nError:\n%1").arg(select.lastError().text()) );
    return QString();
}

mrjj

Hi and welcome to the forums

Try with

QSqlQuery select;
select.prepare("select parameter_value from application_settings where parameter_name = ?");
select.bindValue(0, t_parametername);
if (select.exec()) ...

As seen here
https://doc.qt.io/qt-6/qsqlquery.html#QSqlQuery-1

IF constructed with an non empty string, its executed.

kesterpm

thanks, I just tried that. It gives the same result.

hskoglund

If you do it the unsafe way:

QSqlQuery select(QString("select parameter_value from application_settings where parameter_name = '%1'").arg(t_parametername));
if (select.exec())
...

does it still fail?

kesterpm

It doesn't. That solution works fine. I've done something similar. I'm wondering if it is something with my build environment. I'm just starting to do work in Windows. I built the bulk of the application in Linux. I'm just starting to port it over to windows.

Christian Ehrlicher

What Qt version do you use on Linux and Windows?
Also are you sure you opened the sqlite database from the correct location?

kesterpm

I'm using Qt 5.15.2. I'm sure I have the correct database because using the exec() method works. Everywhere in my code where the bind method is used it fails. I'm compiling with MSVC 16.11.33130.400 (amd64).

Christian Ehrlicher

Please use QSqlQuery::prepare() instead the QSqlQuery ctor and check the return value of prepare(). I'm not sure if QSqlQuery(QString) is meant to be used with bound values since as the documentation states, the given query is executed directly which will fail and then the query will be in an invalid state.
Are you sure you have 5.15.2 also on Linux?

kesterpm

OK, that helped. I'll need to change to using prepare() everywhere. I am using 5.15.2 on linux as well, but this is a sqlite issue. See my code change below. The error that prepare() caused was "RIGHT and FULL OUTER JOINs are currently not supported." My sqlite database had a view with a RIGHT JOIN. I removed it and everything works now. The join was on a view I wasn't even using here.

QSqlQuery select;
if (!select.prepare("select parameter_value from application_settings where parameter_name = ?"))
    qDebug() << "Prepare Failed: " << select.lastError();
select.bindValue(0, t_parametername);

if (select.exec())
{
    if (select.next())
        return select.value(0).toString();
    else
        return QString();
}
else
{
    qDebug() << "Last Error: " <<  select.lastError().databaseText() << "\n" << select.lastError().driverText() << "\n" << select.lastError().nativeErrorCode();

    QMessageBox::critical(nullptr, QObject::tr("Database Access Failed"), QString("Failed to access a saved setting. You may need to restart Project Notes.\n\nError:\n%1").arg(select.lastError().text()) );
    return QString();
}