QODBC can't connect (yet another thread)

hskoglund

Hi, I seem to remember getting that same "garbage" output from lastError() when I tried on Ubuntu two years ago.
Anyway, I know I have an working example for Ubuntu 14.04 that prints out some rows from the Northwind database on a SQL Server (and to get it to work i remember I had to it the "fancy way" as you say above).
Later today I'll check my backups and post something. promise :-)

hskoglund

Found the code on my old harddisk: for ODBC to work in Linux, indeed it seems you have to do it the "fancy" way, I mean always write those files ..odbc.ini and .freetds.conf
(Linux is actually slightly simpler than OSX, the files go both into the same home directory).

To test the code, I first installed a fresh Ubuntu 16.04 and then installed unixOBDC and freeTDS:
sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc

Installed Qt 5.6, downloaded Qt's source and built the libqsqlodbc.so plugin, placed it together with the other chaps in Qt's ../plugins/sqldrivers subdirectory.

Then I created an empty Qt console app (called ODBCOnLinux :-) in the .pro file I inserted:
QT += sql
and this is how main.cpp looks:

#include <QCoreApplication>
#include <QTextStream>
#include <QDir>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlError>
#include <QSqlQuery>

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

// try to load the SQL ODBC plugin
    auto db = QSqlDatabase::addDatabase("QODBC");
    if (!db.isValid())
        qFatal("addDatabase(QODBC) failed :-(");

// prepare the args
    QString sServerIP = "192.168.100.1";
    QString sDatabase = "Northwind";
    QString sUsername = "username";
    QString sPassword = "password";

// setup dummy DSN name and where the freeTDS .so driver file is
    QString sDSN      = "dsn1";
    QString sFilename = "/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so";

// create an .odbc.ini file on our home directory
    QFile fOdbc(QDir::homePath() + "/.odbc.ini");
    if (!fOdbc.open(QFile::WriteOnly | QFile::Text))
        qFatal("open ~/.odbc.ini for write failed");

    QTextStream tsOdbc(&fOdbc);
    tsOdbc << "[" << sDSN << "]"           << "\n";
    tsOdbc << "Driver     = " << sFilename << "\n";
    tsOdbc << "Servername = " << sDSN      << "\n";
    tsOdbc << "Database   = " << sDatabase << "\n";
    tsOdbc.flush();
    fOdbc.close();

// create a .freetds.conf file on our home directory
    QFile fFreeTds(QDir::homePath() + "/.freetds.conf");
    if (!fFreeTds.open(QFile::WriteOnly | QFile::Text))
        qFatal("open ~/.freetds.conf for write failed");

    QTextStream tsFT(&fFreeTds);
    tsFT << "[" << sDSN << "]"        << "\n";
    tsFT << "host = " <<  sServerIP   << "\n";
    tsFT << "port = 1433"             << "\n";
    tsFT << "tds version = 8.0"       << "\n";
    tsFT << "client charset = UTF-8"  << "\n";
    tsFT.flush();
    fFreeTds.close();

// set the magic sauce (to get some needed features, i.e. ODBC3)
    db.setConnectOptions("SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3;");

// open sesame
    db.setDatabaseName(sDSN);
    if (!db.open(sUsername,sPassword))
        qFatal("open() failed, error = '%s'",qUtf8Printable(db.lastError().text()));

// party on the db
    QTextStream cout(stdout);

// dump all rows for all tables
    for (auto table : db.tables())
        for (auto query = QSqlQuery("select * from " + table); query.next(); cout << endl)
            for (int col = 0; (col < 10); ++col)
            { // dump max 10 columns
                auto v = query.value(col);
                if (!v.isValid())
                    break;

                cout << (v.toString() + QString(10,' ')).left(10) << ", ";
            }

// that's all folks
    db.close();

    cout << "Press Ctrl-C to quit" << endl;
    return a.exec();
}

Note 1: I didn't have to edit odbc.ini or any other file in /etc or somewhere else.
Note 2: you have to specify where the FreeTDS driver .so file is, in my case with 64-bit Ubuntu it's always /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Note 3: my dump of all the tables and rows is a bit sloppy, the program will sometimes complain about "column invalid range", this error is by design :-)

i92guboj

Hi, and please, forgive me for taking some days to answer.

Life getting in the middle...

I have tested your sample code, result is the same.

$ ./linuxodbccon 
open() failed, error = '???????????????????????????????????????????????d'
Abortado

By now, I have exactly zero doubt that the problem, whatever it is, lies within qsqlodbc.so.

I have tested many builds, with 4.x, 5.x, up to the latest, I have also downloaded the linux installer (ugh) just to discard some toolchain oddness, gcc version issues, etc.

I have been wandering in another couple forums, to no avail.

The concrete output varies a bit with versions. But I doubt it's relevant. The qt sql bridge is doing something odd that the unixodbc and freetds command line tools don't do. They all work with or without vpn in the middle.

Thank you for taking the time to answer. It's much appreciated :)

hskoglund

Hi, indeed as you say, your isql works like a charm but libqsqlodbc.so does not :-(
What you can do, compare the outputs of lsof -c isql and lsof -c linuxodbccon to see that they load the same .so files.

You could try my libqsqlodbc,so from my build (Qt 5.6 on Ubuntu 14.04 64-bit) I just zipped and uploaded it to my websitet

Or perhaps Gentoo is allergic to Qt and ODBC? If you have VirtualBox or VMWare you could try downloading Ubuntu and test on that...

i92guboj

Hello again, and thanks for taking the time to pack and upload that.

ldd on both files report the same exact paths for the relevant libraries, I'll paste nonetheless, in case someone sees something I cant see.

$ ldd /usr/lib/qt5/plugins/sqldrivers/libqsqlodbc.so 
        linux-vdso.so.1 (0x00007ffc2b7e4000)
        libodbc.so.2 => /usr/lib64/libodbc.so.2 (0x00007fde242e0000)
        libQt5Sql.so.5 => /usr/lib64/libQt5Sql.so.5 (0x00007fde2429b000)
        libQt5Core.so.5 => /usr/lib64/libQt5Core.so.5 (0x00007fde23e10000)
        libstdc++.so.6 => /usr/lib/gcc/x86_64-pc-linux-gnu/4.9.3/libstdc++.so.6 (0x00007fde23ab8000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fde237c1000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fde23424000)
        libltdl.so.7 => /usr/lib64/libltdl.so.7 (0x00007fde2321a000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fde22ffe000)
        libz.so.1 => /lib64/libz.so.1 (0x00007fde22de7000)
        libicui18n.so.55 => /usr/lib64/libicui18n.so.55 (0x00007fde22989000)
        libicuuc.so.55 => /usr/lib64/libicuuc.so.55 (0x00007fde225fb000)
        libpcre16.so.0 => /usr/lib64/libpcre16.so.0 (0x00007fde22395000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fde22191000)
        libglib-2.0.so.0 => /usr/lib64/libglib-2.0.so.0 (0x00007fde21e58000)
        librt.so.1 => /lib64/librt.so.1 (0x00007fde21c4f000)
        libgcc_s.so.1 => /usr/lib/gcc/x86_64-pc-linux-gnu/4.9.3/libgcc_s.so.1 (0x00007fde21a38000)
        /lib64/ld-linux-x86-64.so.2 (0x00005556bd1b6000)
        libicudata.so.55 => /usr/lib64/libicudata.so.55 (0x00007fde1ff80000)
$ ldd /usr/bin/isql
        linux-vdso.so.1 (0x00007ffd5b9fb000)
        libodbc.so.2 => /usr/lib64/libodbc.so.2 (0x00007f8c56dd0000)
        libreadline.so.6 => /lib64/libreadline.so.6 (0x00007f8c56b86000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f8c5696a000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f8c565ce000)
        libltdl.so.7 => /usr/lib64/libltdl.so.7 (0x00007f8c563c4000)
        libncurses.so.5 => /lib64/libncurses.so.5 (0x00007f8c5616e000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f8c5703d000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f8c55f6a000)

I tried your lib, the only difference is that this time the program refused to load the driver for some reason. I noticed that your binary is not stripped, though.

# ls -l
total 1,2M
-rwxr-xr-x 1 root root  58K may 24 18:33 libqsqlite.so
-rwxr-xr-x 1 root root  75K may 24 18:33 libqsqlmysql.so
-rwxr-xr-x 1 root root 917K may 18 00:31 libqsqlodbc.so
-rwxr-xr-x 1 root root  98K may 26 18:27 libqsqlodbc.so.mine
-rwxr-xr-x 1 root root  58K may 24 18:33 libqsqltds.so
# file *
libqsqlite.so:       ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, stripped
libqsqlmysql.so:     ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, stripped
libqsqlodbc.so:      ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, BuildID[sha1]=4152feaba6934939dcf8c88754ca082f240aa7a5, not stripped
libqsqlodbc.so.mine: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, stripped
libqsqltds.so:       ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, stripped

That should not have anything to do with it. should not ahem.

I can't remember if I said above that I also tried the binary qt installer, whose binaries are not tied to Gentoo in any way. So, if the issue is with Gentoo, it's is something with deeper roots than just qt.

Trying another distro with a different glibc version might make some sense at this point. That won't be *buntu, though :lol:

Thank you again for all your efforts.

I'll keep you posted.

i92guboj

Some random things, in case they are useful for someone else, or in case my head suffers from data corruption (it happens from time to time).

freetds must be built with odbc but without iodbc, otherwise, I can't connect using the command line tools
built using 32 bits qt, it didn't make a difference
built using non-stripped binaries, didn't make a difference
linked /etc/freetda and unixODBC crap into ~/<respective dotfile>, didn't make a difference

hskoglund

Hi, got curious about Gentoo so I downloaded/installed and tested, and I got Qt and ODBC to work :-) I took simplest possible route:

Downloaded and installed 64-bit Gentoo.
Then I installed unixODBC and freeTDS:
sudo emerge unixODBC freetds

Installed Qt 5.6 64-bit.
Then I downloaded my libqsqlodbc.so I built on my Ubuntu 14.04 (because I was too lazy to rebuild it on Gentoo) and placed it in ~/Qt/5.6/gcc_64/plugins/sqldrivers
Now I hit a snag (because I built that on the old 14.04, which only has libodbc.so.1.0.0, not libodbc.so.2.0.0):
In Ubuntu 16.04 there are 3 symbolic links libodbc.so --> libodbc.so.1 --> libodbc.so.2 so that it'll load just fine anyway but emerging unixODBC in Gentoo resulted in just 2 links libodbc.so and libodbc.so.2 :-( So I had to manually create that link:
cd /usr/lib64
sudo ln -s libodbc.so.2.0.0 libodbc.so.1

One more thing: changing the location of the libtdsodbc driver in my program, for Ubuntu it was:
QString sFilename = "/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so";
for Gentoo change it to:
QString sFilename = "/usr/lib/libtdsodbc.so";

Voila!

Edit: that dependency on libodbc.so.1 was probably the reason my libqsqlodbc.so wouldn't load when you tested it.

i92guboj

@hskoglund, thanks for all the ideas. I have discovered that whatever is happening here, it's a bug in the plugin that Gentoo installs. I have no idea what the problem is, I'll have to dig a bit more, and iron this out a bit in conjunction with the Gentoo developers.

I used ldd on your binary, and found that, indeed, it needs a libodbc.so.1 file, which I just linked as you suggested. After that, my program started to work. I am not doing any fancy thing with config files, I just use the standard files under /etc for odbc and freetds, and it just worked as it should. So, there's clearly some problem with the Gentoo generated libqsqlodbc.so library.

By the way, if you want to try the Gentoo lib out of curiosity, all you need is to enable the odbc USE flag for qtsql, and re-emerge it. If my theory is correct, that one should fail to work.

Again, thank you. I have some bug reporting to do.

i92guboj

In case someone cares, this is the bug:

https://bugs.gentoo.org/show_bug.cgi?id=584458

i92guboj

Just for the sake of completeness, and in case someone like me lands here, I'll add that, even if you can connect, you won't be able to query (other than simple UPDATE statements, it seems). SELECT won't work.

That can be easily overcome by installing freetds-1.0 though, which got added into portage just a few days ago, as opposed to the 0.91 version which is in stable portage.

At least now I can select, let's wait for unions and joins to get into scene :lol: