building ODBC plugin on Linux



  • 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?


  • Lifetime Qt Champion

    Hi and welcome to devnet,

    What if you remove -lodbc ?



  • 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 :-(



  • @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.



  • 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.



  • @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?



  • 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 :-(



  • @hskoglund
    Thank you.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.