QODBC driver not loaded [Excel]

MoonRaiser

Hi
I tried to launch example from here https://wiki.qt.io/Handling_Microsoft_Excel_file_format with ODBC
In the code below i have a problem with

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "xlsx_connection");;

Full code is here

#include <QCoreApplication>
#include <QtSql/QSql>
#include <QtSql/QSqlDriver>
#include<QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlRecord>
#include <QtSql/QSqlError>


#include <QDebug>

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "xlsx_connection");
    db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString("c:\\Users\\Anton\\Documents\\Qt\\build-qt_excel-Desktop_Qt_5_12_3_MinGW_64_bit-Debug\\test.xlsx"));
    if(db.open())
    {
        qDebug()<<"opened";

        QSqlQuery query("select * from [" + QString("Sheet1") + "$A1:B5]"); // Select range, place A1:B5 after $
        if (!query.exec())
        {
            qDebug()<<query.lastError();
            QStringList driversList = QSqlDatabase::drivers();
            qDebug() << "drivers list= " << driversList;
        }
        while (query.next())
        {
        QString column1= query.value(0).toString();
        qDebug() << column1;
        }
       db.close();
    QSqlDatabase::removeDatabase("xlsx_connection");
    }
    return a.exec();
}

Full cosnsole output looks like:
opened
QSqlQuery::exec: database not open
QSqlError("", "Driver not loaded", "Driver not loaded")
drivers list= ("QSQLITE", "QMYSQL", "QMYSQL3", "QODBC", "QODBC3", "QPSQL", "QPSQL7")
QSqlDatabasePrivate::removeDatabase: connection 'xlsx_connection' is still in use, all queries will cease to work.

it seems that my computer doesn't recognize connectionName string. Because if i use

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");

qt founds my excel file and opens it, but queries doesnt work of course.
There're qt 5.12.3 with MinGW 7.3.0 x64 and Office 2016 x64 with Access on my win10.

Christian Ehrlicher

Since you don't use the default connection name you have to pass the db connection to your QSqlQuery

MoonRaiser

@Christian-Ehrlicher it works, thanx, maybe should correct the example
But i catch new error like this
QODBCResult::exec: Unable to execute statement: "[Microsoft][─шёяхЄўхЁ фЁрщтхЁют ODBC] ╬°шсър яюёыхфютрЄхы№эюёЄш ЇєэъЎшщ"
QSqlError("0", "QODBC3: Unable to execute statement", "[Microsoft][─шёяхЄўхЁ фЁрщтхЁют ODBC] ╬°шсър яюёыхфютрЄхы№эюёЄш ЇєэъЎшщ")
query.lastError().type() said me it was problem with syntax.
I used this string from example QString test="Select * from [Sheet1$A1:I9]"; as query
Then i tried this "CREATE TABLE testdb (aaa CHAR(128), bbb CHAR(128))" i'm sure syntax fully corrected but i catch error type 2 again

Christian Ehrlicher

Don't see what Qt can do against this and me neither, sorry.

MoonRaiser

@Christian-Ehrlicher this query works in access but doesnt work through driver in excel. What sysntax should i use to make it works?