building ODBC plugin on Linux

bdfyy

Hi, all

I have Qt 5.9 and ubuntu 16.04 LTS

I'm trying to build the ODBC plugin for Linux, per the instructions on the page: http://doc.qt.io/qt-5/sql-driver.html#qodbc

I've already built the unixODBC found here: http://www.unixodbc.org/
using the commands for built unixODBC:

sudo ./configure --sysconfdir=/etc
sudo make
sudo make install

and I have files:

/usr/local/bin:     dltest, isql, iusql, odbc_config, odbcinst, slencheck
/usr/local/include: autotest.h, odbcinst.h, odbcinstext.h, sql.h, sqlext.h, sqlspi.h, sqltypes.h, sqlucode.h, unixodbc_conf.h, uodbc_extras.h, uodbc_stats.h
/usr/local/lib:     libodbc.la, libodbc.so, libodbc.so.2, libodbc.so.2.0.0, libodbccr.la, libodbccr.so, libodbccr.so.2, libodbccr.so.2.0.0, libodbcinst.la, libodbcinst.so, libodbcinst.so.2, libodbcinst.so.2.0.0

/usr/local/share/man/man1: dltest.1, isql.1, iusql.1, odbc_config.1, odbcinst.1
/usr/local/share/man/man5: odbc.ini.5, odbcinst.ini.5
/usr/local/share/man/man7: unixODBC.7

When I build a qt odbc plugin using commands:

cd /opt/Qt/5.9/Src/qtbase/src/plugins/sqldrivers/odbc
sudo qmake "INCLUDEPATH+=/usr/local/include" "LIBS+=-L/usr/local/lib -lodbc"

I have error:

Info: creating stash file /opt/Qt/5.9/Src/qtbase/.qmake.stash
Project ERROR: Library 'odbc' is not defined.

What am I doing wrong?

SGaist

Hi and welcome to devnet,

What if you remove -lodbc ?

hskoglund

Hi, I've tried and indeed got the same error, also when just doing an empty 'qmake'. Think you've been hit by this bug :-(

bdfyy

@SGaist

I installed:

sudo apt-get install mdbtools unixodbc libmdbodbc1

Described the driver in /etc/odbcinst.ini:

[MDBTools]
Description     = MDBTools Driver
Driver          = libmdbodbc.so
Setup           = libmdbodbc.so
FileUsage       = 1
UsageCount      = 1

Added a description of the database in /etc/odbc.ini:

[testdb]
Description = test
Driver      = MDBTools
Database    = /home/rootuser/testdb.accdb

Tested with utilite isql:

isql testdb
select * from objects

Works correctly.

Edited the file /opt/Qt/5.9/Src/qtbase/src/plugins/sqldrivers/odbc/odbc.pro:

TARGET = qsqlodbc

HEADERS += $$PWD/qsql_odbc_p.h
SOURCES += $$PWD/qsql_odbc.cpp $$PWD/main.cpp

#QMAKE_USE += odbc
unix: DEFINES += UNICODE

OTHER_FILES += odbc.json

PLUGIN_CLASS_NAME = QODBCDriverPlugin
include(../qsqldriverbase.pri)

Commented the line:

QMAKE_USE += odbc

Builded and installed the plugin with the following commands:

cd /opt/Qt/5.9/Src/qtbase/src/plugins/sqldrivers/odbc
sudo /opt/Qt/5.9/gcc_64/bin/qmake "INCLUDEPATH+=/usr/local/include" "LIBS+=-L/usr/local/lib -lodbc"
sudo make
sudo make install

Created a test application:

#include <QApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QFileInfo>
#include <QSqlError>
#include <QSqlQuery>

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

    QString dbFilePath = "/home/rootuser/testdb.accdb";
    QString connectionName = "OdbcConnection";

    QFileInfo fileInfo(dbFilePath);
    if (fileInfo.exists())
    {
        QSqlDatabase m_dbConnection = QSqlDatabase::addDatabase("QODBC", connectionName);
        m_dbConnection.setDatabaseName(QString("Driver={MDBTools};FIL={MS Access};DBQ=%1").arg(dbFilePath));
        if (m_dbConnection.open())
        {
            QSqlQuery query(m_dbConnection);
            query.setForwardOnly(true);
            if (query.exec("select * from objects"))
            {
                while (query.next())
                {
                    qDebug() << query.value(1).toString();
                }
            }
            else
            {
                qDebug() << "Can not exec query" << m_dbConnection.lastError().text();;
            }

        }
        else
        {
            qDebug() << "Can not open database: " << m_dbConnection.lastError().text();;
        }
    }
    else
    {
        qDebug() << "File not exist";
    }

    return 0;
}

And received the message:

Starting /home/rootuser/Documents/Qt/build-TestODBC-Desktop_Qt_5_9_0_GCC_64bit2-Debug/TestODBC...
Error at Line : syntax error near 'test'
syntax error near 'test'
Got no result for 'select 'test'' command
QODBCDriver::checkHasSQLFetchScroll: Warning - Driver doesn't support scrollable result sets, use forward only mode for queries
Can not exec query " "
/home/rootuser/Documents/Qt/build-TestODBC-Desktop_Qt_5_9_0_GCC_64bit2-Debug/TestODBC exited with code 0

SQL query is not execute. What is wrong?
In Windows this test application work correctly.

hskoglund

Hi, I very much suspect this is because MDBTools does not handle Unicode very well.

Long time ago I had a similar problem using the ODBC plugin in Qt to read Visual Foxpro .dbf files. After some searching, I found a patch that neuters the Unicode support in the ODBC plugin, and that solved it for me.

You could try it, change a few lines in qsql_odbc.cpp and rebuild the plugin with "make" and "make install". If you are lazy, I've uploaded a freshly patched version (from Qt 5.9), you can download it here.

bdfyy

@hskoglund
Thank you.
Data readed. But I received the message:

Starting /home/rootuser/Documents/Qt/build-TestODBC-Desktop_Qt_5_9_0_GCC_64bit2-Debug/TestODBC...
QODBCDriver::checkHasSQLFetchScroll: Warning - Driver doesn't support scrollable result sets, use forward only mode for queries
"qMakeField: Unable to get column attributes for column 0" 	Error: ""
"qMakeField: Unable to get autovalue attribute for column 0" 	Error: ""
"qMakeField: Unable to get column attributes for column 1" 	Error: ""
"qMakeField: Unable to get autovalue attribute for column 1" 	Error: ""
"qMakeField: Unable to get column attributes for column 2" 	Error: ""
"qMakeField: Unable to get autovalue attribute for column 2" 	Error: ""
"06CDA01/A1/A1-12\u0000"
"06CDA01/A2/A2-17\u0000"
"06CDA01/A2/A2-17\u0000"
/home/rootuser/Documents/Qt/build-TestODBC-Desktop_Qt_5_9_0_GCC_64bit2-Debug/TestODBC exited with code 0

Is message normal?

"qMakeField: Unable to get column attributes for column 0" 	Error: ""
"qMakeField: Unable to get autovalue attribute for column 0" 	Error: ""

And in test application I added update command:

if (m_dbConnection.open())
{
    QSqlQuery query(m_dbConnection);
    query.setForwardOnly(true);
    if (query.exec("select * from objects"))
    {
        while (query.next())
        {
            qDebug() << query.value(1).toString();
        }
    }
    else
    {
        qDebug() << "Can not exec query" << m_dbConnection.lastError().text();;
    }
    QSqlQuery query_update(m_dbConnection);
    if (query_update.exec("update objects set F_Name = 'TEST_UPDATE' where ID = 330"))
    {
        m_dbConnection.commit();
        qDebug() << "record updated";
    }
    else
    {
        qDebug() << "can not update record";
    }
}

And received the message:

Starting /home/rootuser/Documents/Qt/build-TestODBC-Desktop_Qt_5_9_0_GCC_64bit2-Debug/TestODBC...
QODBCDriver::checkHasSQLFetchScroll: Warning - Driver doesn't support scrollable result sets, use forward only mode for queries
"qMakeField: Unable to get column attributes for column 0" 	Error: ""
"qMakeField: Unable to get autovalue attribute for column 0" 	Error: ""
"qMakeField: Unable to get column attributes for column 1" 	Error: ""
"qMakeField: Unable to get autovalue attribute for column 1" 	Error: ""
"qMakeField: Unable to get column attributes for column 2" 	Error: ""
"qMakeField: Unable to get autovalue attribute for column 2" 	Error: ""
"06CDA01/A1/A1-12\u0000"
"06CDA01/A2/A2-17\u0000"
"06CDA01/A2/A2-17\u0000"
Error at Line : syntax error near update
syntax error near update
Got no result for 'update objects set F_Name = 'TEST_UPDATE' where ID = 330' command
can not update record
/home/rootuser/Documents/Qt/build-TestODBC-Desktop_Qt_5_9_0_GCC_64bit2-Debug/TestODBC exited with code 0

What could be the problem?

hskoglund

Hi, I downloaded and installed MDBTools and tested it on a simple Access file using isql:

select * from ... works fine (same as in your example above)

but
select count(*) ...
update ...
insert ...
all returns the same error as "Error at Line : syntax error near..." as in your program above.

So it seems MDBTools is very rudimentary, indeed if you look at the README it says:
....
The initial goal of these tools is to be able to extract data structures and
data from mdb files. ...

I.e. MDBTools is most likey only read-only :-(

bdfyy

@hskoglund
Thank you.

wxzxg1804

@hskoglund :hi,i have the same problem with Mr bdfyy. Could you give me the Patch qsql_odbc.cpp? Thanks a lot! E_Mail:843576957@qq.com.

hskoglund

@wxzxg1804 Hi, just emailed you, I've uploaded a 5.13.0 version in the same place, here

levy_rii

@hskoglund said in building ODBC plugin on Linux:

@wxzxg1804 Hi, just emailed you, I've uploaded a 5.13.0 version in the same place, here
hi,i have the same problem with Mr bdfyy. But the "http://tungware.com/qsql_odbc.cpp" become 404 not found. Could you give me the Patch qsql_odbc.cpp? Thanks a lot! E_Mail:liweixxxx1990@163.com.