QODBC can't connect (yet another thread)

i92guboj

Hi.

I've been dealing with this for days now. So I thought it's about time to look for help.

I am trying to connect to SQL server from a Qt application, using Gentoo Linux. Right now I have the following code:

    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setDatabaseName(QString("Driver={SQL Server};Server=10.x.x.1;Database=mybase;Uid=sa;Pwd=xxxxxxx;Trusted_Connection=yes;"));

    if( !db.open() )
    {
        qDebug() << db.lastError().text();
        qDebug() << db.lastError().driverText();
        qDebug() << db.drivers();

        exit(1);
    }

Which outputs:

Starting /home/i92guboj/qt-builds/sql-runner/QSLRunner...
"��������������������d����ៀ餅餒夝ᤝ餞够񗼅����嶆����嶇埇埀ퟀ埀ퟀ埀ퟀ埀ퟀ埀ퟀ埀ퟀ埀ퟀ埀ퟀ埀ퟀ埀ퟀ埀ퟀ顀៘񀀂𠄀៬񀀂 QODBC3: Unable to connect" 
"QODBC3: Unable to connect" 
("QSQLITE", "QSQLITE3", "QMYSQL3", "QMYSQL", "QODBC3", "QODBC", "QTDS7", "QTDS") 
/home/i92guboj/qt-builds/sql-runner/QSLRunner exited with code 1

The output of lastError().text() is a bit surprising and I have no idea where it comes from. I have tried rebuilding the qtsql module but (as I knew beforehand) it didn't help. The driver is there, and Qt seems able to see it. I have used QtSql in several problems in the past, though maybe never with MSSQL.

I have used many variants of that connection string (client and server variants, and a few others). I went as fas as to using the FreeTDS configured driver name, which also didn't work.

From the command line I can connect using this FreeTDS thing, so I know the server is reachable and accepting connections.

$ tsql -S server -U user -P pass
locale is "es_ES.utf8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> quit
$ isql lgfapp username pwd
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

I don't really know if Qt needs this FreeTDS thing when using the QODBC driver. I am just trying at random and nothing seems to work. The fact that the only bit of info I could help appears to be trashed doesn't help in debugging this...

So, any help is welcome.

Thanks for reading and for any help.

hskoglund

Hi, I also struggled with this some time ago, the problem is that with FreeTDS and db.setDatabaseName() you cannot specify modern style DSNs like you do in Windows with the IP.number etc.
Instead you have use old-style ODBC DSN-names, called a DSN-name like "dsn1".
Then create an odbc.ini file and a .freetds.conf file which refers to that DSN-name.

I have some code that wrote those 2 files for FreeTDS on the Mac and on Ubuntu, can only find the Mac code right now
on my GitHub line 81-123
but I remember it worked on Ubuntu as well.

i92guboj

@hskoglund

Hi and thanks for answering. I read your code. You do it the fancy way by setting a user freetds/odbc configuration, but basically the approach is (and correct me if I am wrong)

db.setDatabaseName("mydsn");
db.setUserName("sa");
db.setPassword("Ultrasikret");

Where the string "mydsn" is that little header that goes in between brackets in the odbc.ini and FreeTDS files.

This is a variant I have tried, along with many others. I also tried the full DSN in combination with the setUserName() / setPassword() thing, but it didn't help.

And, in any case, I am afraid that that corruption in the lastError() output has something to do with it. For I don't know what reason something in the ODBC chain if failing miserably and I fear that while I don't fix that I am doing nothing but hitting against a wall.

I will be setting a Windows machine at work to test this in the next few days to confirm or discard this theory, but doing the development in Windows is not something I'll be doing so sooner or later I will have to either fix this or re-think my roadmap :P

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: