MSSQL with unixODBC and FreeTDS

Christian Ehrlicher

@hskoglund said in MSSQL with unixODBC and FreeTDS:

So consider it a workaround. So feel free to debug more/deeper and submit a bugreport

Why? We don't support ODBC2 anymore.

hskoglund

Indeed, Qt doesn't. I was thinking of submitting a bug report somewhere to unixODBC or FreeTDS

Jonas Kvinge

@hskoglund
Thanks for doing all the debugging on this.
I already did submit a bug report to Qt: https://bugreports.qt.io/browse/QTBUG-82724
But if you think the problem is in FreeTDS or unixODBC, maybe we should submit a bug report there instead.

hskoglund

@Jonas-Kvinge Yeah, it seems to me like there's some forgotten corner in unixODBC or FreeTDS that handle SQL commands, and someone forgot to upgrade that to ODBC3 support for "restore...".

On the other hand, there already is a isFreeTDSDriver boolean var in qsql_odbc.cpp that's used for some workarounds, like turning off Unicode support(!): unicode = unicode && !isFreeTDSDriver

So I mean, since the infrastructure is already in place in qsql_odbc.cpp to detect the presence of FreeTDS, you could suggest in your bug report that Qt implements yet another workaround, e.g.

if (isFreeTDSDriver) 
    r = SQLFetch(d->hStmt);
else
    r = SQLFetchScroll(d->hStmt,SQL_FETCH_FIRST,0);

(Or we download the sources of unixODBC and FreeTDS and start digging. Maybe next Saturday :-)

Jonas Kvinge

@hskoglund
I modified the qodbc driver but I still can't make it work. I tried both with Qt 5.14.1 and the dev branch. Am I missing something?
I'm using openSUSE tumbleweed (up to date) with unixODBC 2.3.7 and FreeTDS ODBC driver 1.1.20

In checkHasSQLFetchScroll() I just added || isFreeTDSDriver

In bool QODBCResult::fetchFirst():

if (d->hasSQLFetchScroll)
       r = SQLFetchScroll(d->hStmt, SQL_FETCH_FIRST, 0);
   else
       r = SQLFetch(d->hStmt);

If you want to test my code, it's all on GitHub:
https://github.com/jonaski/sqlrestore
Look at the freetds branch, the modified qodbc driver is in 3rdparty and will exposed as QODBCX , select QODBCX in test settings AND restart the program (that's the statically compiled driver in 3rdparty)

hskoglund

@Jonas-Kvinge You were 99% there :-)

Only thing you forgot, since you altered the odbc.json file to just contain "QODBCX", you also need to add it the list of kosher names in main.cpp's QODBCDriverPlugin::create(), i.e. change line 62 in main.cpp from

if (name == QLatin1String("QODBC") || name == QLatin1String("QODBC3")) {

to

if (name == QLatin1String("QODBC") || name == QLatin1String("QODBC3") || name == QLatin1String("QODBCX")) {

P.S. I can see in your logging: "d->hasSQLFetchScroll: false" and this warning rears its ugly head:
"QODBCDriver::checkHasSQLFetchScroll: Warning - Driver doesn't support scrollable result sets, use forward only mode for queries"
Maybe you neutered qsql_odbc.cpp too much, because other SQL commands like Select works without the patch, but maybe better safe than sorry.

P.P.S. I see you're using QThread for the database calls, I never got around to doing that, right now I have some really slooow SQLServers to interface with, maybe I could use some of your QThreading code...

Jonas Kvinge

@hskoglund
I'm doing that: https://github.com/jonaski/sqlrestore/blob/freetds/3rdparty/qsqlodbc/qsql_odbc_main.cpp#L57

hskoglund

@Jonas-Kvinge Sorry, you're right, that bug was mine, not yours. It was introduced because your qsql_odbc_main.cpp and h didn't make it into my build on Ubuntu 19.10 (with FreeTDS 1.1.6 and unixODBC 2.30).

You see, I didn't use your app to test the plugin, instead I copied everything from your 3rd party dir into my copy of 5.14.1's sources (into ~/Qt/5.14.1/Src/qtbase/src/plugins/sqldrivers/odbc) and then I rebuilt libqsqlodbc.so using Qt Creator and the vanilla ~/Qt/5.14.1/Src/qtbase/src/plugins/sqldrivers/odbc/sqldrivers.pro file (which means it compiled with the standard 5.1.41 main.cpp, not your new qsql_odbc_main.cpp and qsql_odbc.main.h)

Anyways, your qsql_odbc.cpp got compiled and it worked as I said nicely with my simple test program, just a Qt console app, standard .pro file expect that "QT += sql" is added, and here's the main.cpp:

#include <QCoreApplication>
#include <QTextStream>
#include <QDir>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlRecord>
#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("QODBCX");
    if (!db.isValid())
        qFatal("addDatabase(QODBC) failed :-(");

// prepare the args
    QString sServerIP = "192.168.1.100";
    QString sDatabase = "northwind";
    QString sUsername = "sa";
    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 = 7.0"       << "\n";
    tsFT.flush();
    fFreeTds.close();

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

    QTextStream cout(stdout);

// party on the db, using either the "restore.. " or the "select.."
    auto query = QSqlQuery("restore headeronly from disk = 'C:/northwind.bak'");
    //auto query = QSqlQuery("select * from sys.tables");
    auto columns = query.record().count();
    while (query.next())
        for (auto c = 0; (c < columns); ++c)
            cout << QString::number(c) << " : " << query.value(c).toString() << "\n";

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

Note: I had to use "tds version = 7.0" in my .freetds.cont file, otherwise I couldn't establish contact with my MSSQLServer 2008R2.

Maybe you could try my test console app and see if it works on your openSUSE tumbleweed...

Jonas Kvinge

@hskoglund
I found the problem. I Made a query without bindValue and it worked.
In my code I changed:

query.prepare("RESTORE HEADERONLY FROM DISK = :bakfile");
query.bindValue(":bakfile", bakfile);

to

query.prepare(QString("RESTORE HEADERONLY FROM DISK = '%1'").arg(bakfile));

SGaist

Hi,

Not all SQL operations support binding values especially when they are implementation specific.

hskoglund

@Jonas-Kvinge Nice! @SGaist Totally agree, long time ago I got bitten by the SQL binding bugs, and since I started with Qt I always use the QString("sql %1 %2 etc").arg().arg()... construction.

(And as I understand it, since around the turn of the century, there's no difference in SQL Server performance between using prepare/bind and QString::arg())

SGaist

@hskoglund IIRC binding values has additional benefits when having types other than string. And it helps secure dynamically created queries.