MySQL with ODBC on a MacBook

Willi2793

Hi,

after I did it for profession I now do it as hobby. And I'm just moving from Windows to MacOs. I installed Qt 6.7.1, MySQL 8.3 and ODBC 8.4 and of course the newest version of iODBC. I know try a very simple Select and get an error I don't understand.

I don't use any special options and the output is:

DB is open
""
Nach Query:  true
"[iODBC][Driver Manager]Optional feature not implemented, HYC00 QODBC: Unable to fetch next"

The complete Source-Code is:

#include <QCoreApplication>
#include <QLocale>
#include <QTranslator>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QDebug>
#include <QSqlError>

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

    QTranslator translator;
    const QStringList uiLanguages = QLocale::system().uiLanguages();
    for (const QString &locale : uiLanguages) {
        const QString baseName = "SQL-Test_" + QLocale(locale).name();
        if (translator.load(":/i18n/" + baseName)) {
            a.installTranslator(&translator);
            break;
        }
    }

    QSqlDatabase sql = QSqlDatabase::addDatabase("QODBC","TestConn");
    sql.setDatabaseName("DRIVER={MySQL ODBC 8.4 Unicode Driver};DATABASE=willi_willi_chips;SERVER=localhost");
    sql.setUserName("uuuuu");
    sql.setPassword("ppppp");
    sql.setHostName("localhost");
    if (sql.open()){
        qDebug() << "DB is open";
        qDebug() << sql.connectOptions();
        QSqlQuery query(sql);
        query.setForwardOnly(true);
        bool err = query.exec("SHOW TABLES");
        qDebug() << "Nach Query: " << err;
        if (err) {
            bool nerr = query.next();
            QSqlError serr(query.lastError());
            qDebug() << serr.text();
        }
    } else {
        QSqlError error = sql.lastError();
        qDebug() << error.databaseText();
        qDebug() << "DB is not open";
    }

    return 0;
}

Anybody an idea how I can access my DB?

Thx in advance,
Willi

ChrisW67

@Willi2793 Does it work with a simple SQL select statement e.g., select * from sometable ?

BTW: QSqlQuery::exec() returns true on success and you put it in a variable called err. Later, err == true means there was no error? Exploded my brain temporarily.

Willi2793

No, even with a simple SELECT it's the complete same behavior.

I know, thx. The name of the variable is a bit tricky. Sorry

ChrisW67

@Willi2793 OK.
You do not actually test the value of nerr before you print query.lastError(). Did it actually report failure?

If you do not use setForwardOnly()?

The error is, I think, coming from the ODBC layer.
Is there a reason you cannot use the native MySql driver and Qt Plugin rather?

Willi2793

@ChrisW67 "nerr" is "false". I verified it again.

when I remove setForwardOnly() there is no difference.

I totally agree that it's an issue in the ODBC layer. Not quite sure If really ODBC or iODBC.

Good question why I want to use ODBC. It's already in the distribution. I already tried to create the MySQL-plugin but als have problems there.

I want to solve the ODBC-issue first and than try to get the plugin created.

BTW: thx for your time

Christian Ehrlicher

When you look at the source code ( https://code.qt.io/cgit/qt/qtbase.git/tree/src/plugins/sqldrivers/odbc/qsql_odbc.cpp#n1055 ) I would guess the driver does not support SQLFetchScroll() but claims to do so (see check for SQLFetchScroll in https://code.qt.io/cgit/qt/qtbase.git/tree/src/plugins/sqldrivers/odbc/qsql_odbc.cpp#n2233

Don't see what Qt can do here when the driver reports wrong capabilities.

Willi2793

@Christian-Ehrlicher Thx for pointing this out. I run the Debugger to the Point where SQLFetchScroll() is called and indeed I get there an error. But according to the documentation this function is supported.

Documentation (on page 83)

My idea was that I have a configuration-issue somewhere

Willi2793

So, I was now able to create the QMYSQL plugin. There I have another issue but this should be discussed in another topic. But I want to try some things before by myself.

So I set this to "Solved" (if I find how) even I think it's not really solved.

Thx for all the help :)