Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

MSSQL with unixODBC and FreeTDS



  • The following query executes successfully, but returns 0 records on Linux (openSUSE) using unixODBC with FreeTDS, while it works fine with "ODBC Driver 17 for SQL Server" proprietary driver on the same machine.

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

    bakfile is a QString that points to the Windows path on the server.

    query.exec() succeeds. But query.record().count() is 0 and of course query.next() returns false. query.lastError().text() is empty (no errors).
    Doing the same query with an non-existent file returns the normal operating system cannot find file.

    The same SQL query works fine using tsql (freeTDS diagnostic tool).

    The server is MSSQL 2017 Express on a Windows 10 PC.

    I've also tried query without using bindvalue with the same result.

    Any suggestions?

    Could this be a Qt bug, or possible some character encoding issue?



  • Just guessing, do you use forward or backward slashes in that QString that points to the Windows path?



  • I use backslashes. But I also tested forward slashes.
    And like I said if it doesn't find the file I get an error, but in the case where i Point to a valid file exec() doesn't fail.



  • Ok, I see.
    Here's another one: have you tried setting SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3
    https://doc.qt.io/qt-5/qsqldatabase.html#setConnectOptions
    before opening the db?



  • @hskoglund said in MSSQL with unixODBC and FreeTDS:

    SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3

    Tried it now, didn't help. Same result.



  • Ok. In your freetds.conf file, do you have a tds_version line, e.g.

    tds version = 8.0
    


  • @hskoglund said in MSSQL with unixODBC and FreeTDS:

    freetds.conf

    tds version = auto



  • Hmm, running out of ideas :-(

    Qt's ODBC plugin hasn't been updated for a long time, a couple of years ago I had problem with Unicode traversal for Visual Foxpro databases, so I added my own ODBC code at the end of my own lib: https://gitlab.com/tungware/lib/-/blob/master/TWDB/TWDB.cpp

    If you have other SQL statements that runs fine through Qt's ODBC plugin, and only that Restore statement fails, maybe you could detect its success by a SELECT something to see if the Restore ran to a ok success or not.



  • @hskoglund
    I tried to do another query like:

    select * from sys.databases
    

    It worked fine and returned all databases.

    I also tested (now) on a MSSQL 2008 R2 instance with the same result using a backup file produced on the same instance.

    I haven't tried to do other BACKUP/RESTORE related queries tough, but I suspect the same thing would happen.



  • You could submit a Qt bug report.
    And if you want to very helpful, include some tcpdump outputs, to show the TDS stream over port 1433 and how Qt's ODBC plugins fails to interpret it.

    On the other hand, if your other SQL statements work, you could try to live with this "feature" and work around it, by doing a 2nd query for some props/etc. that a successful Restore would show.



  • @hskoglund
    RESTORE HEADERONLY lists all databases in the backup file and what position they are in.
    I need that info before starting the restore. I don't think I can get that any other way.


  • Qt Champions 2019

    Before writing a bug report - does the odbc driver really supports what you want? I would guess no.



  • Also it could be that Qt's ODBC plugin is ok but the layer below isn't, I mean the unixODBC/FreeTDS combo. I.e. when you instead use Qt with Microsoft's ODBC Driver 17 for SQL Server proprietary driver, it works.

    Maybe openSUSE's versions of unixODBC/FreeTDS are not the latest/greatest, perhaps if you downloaded their source and built them yourself.



  • @Jonas-Kvinge said in MSSQL with unixODBC and FreeTDS:

    RESTORE HEADERONLY lists all databases in the backup file and what position they are in.
    I need that info before starting the restore. I don't think I can get that any other way.

    I have previously used RESTORE HEADERONLY from a C# project. If, for whatever reason, it is not working fro you from ODBC, would you like a workaround? It's a bit long! And unfortunately cannot avoid that you must know the format of the header written. But I'll paste (an extract from it) anyway, in a stored procedure I have:

    	  -- see https://msdn.microsoft.com/en-us/library/ms178536.aspx for table definition
    	  CREATE TABLE #backup_header
    	  (
    	  [BackupName] nvarchar(128),
    	  [BackupDescription] nvarchar(255),
    	  [BackupType] smallint,
    	  [ExpirationDate] datetime,
    	  [Compressed] bit,
    	  [Position] smallint,
    	  [DeviceType] tinyint,
    	  [UserName] nvarchar(128),
    	  [ServerName] nvarchar(128),
    	  [DatabaseName] nvarchar(128),
    	  [DatabaseVersion] int,
    	  [DatabaseCreationDate] datetime,
    	  [BackupSize] numeric(20, 0),
    	  [FirstLSN] numeric(25, 0),
    	  [LastLSN] numeric(25, 0),
    	  [CheckpointLSN] numeric(25, 0),
    	  [DatabaseBackupLSN] numeric(25, 0),
    	  [BackupStartDate] datetime,
    	  [BackupFinishDate] datetime,
    	  [SortOrder] smallint,
    	  [CodePage] smallint,
    	  [UnicodeLocaleId] int,
    	  [UnicodeComparisonStyle] int,
    	  [CompatibilityLevel] tinyint,
    	  [SoftwareVendorId] int,
    	  [SoftwareVersionMajor] int,
    	  [SoftwareVersionMinor] int,
    	  [SoftwareVersionBuild] int,
    	  [MachineName] nvarchar(128),
    	  [Flags] int,
    	  [BindingID] uniqueidentifier,
    	  [RecoveryForkID] uniqueidentifier,
    	  [Collation] nvarchar(128),
    	  [FamilyGUID] uniqueidentifier,
    	  [HasBulkLoggedData] bit,
    	  [IsSnapshot] bit,
    	  [IsReadOnly] bit,
    	  [IsSingleUser] bit,
    	  [HasBackupChecksums] bit,
    	  [IsDamaged] bit,
    	  [BeginsLogChain] bit,
    	  [HasIncompleteMetaData] bit,
    	  [IsForceOffline] bit,
    	  [IsCopyOnly] bit,
    	  [FirstRecoveryForkID] uniqueidentifier,
    	  [ForkPointLSN] numeric(25, 0) NULL,
    	  [RecoveryModel] nvarchar(60),
    	  [DifferentialBaseLSN] numeric(25, 0) NULL,
    	  [DifferentialBaseGUID] uniqueidentifier,
    	  [BackupTypeDescription] nvarchar(60),
    	  [BackupSetGUID] uniqueidentifier NULL
    	  )
    	  IF @sql_ver_major >= 10	-- SQL 2008
    	  BEGIN
    	    ALTER TABLE #backup_header
    	      ADD [CompressedBackupSize] bigint NULL
    	  END
    	  IF @sql_ver_major >= 11	-- SQL 2012
    	  BEGIN
    	    ALTER TABLE #backup_header
    	      ADD [Containment] tinyint
    	  END
    	  IF @sql_ver_major > 12 OR	-- SQL 2014 SP1
    	       (@sql_ver_major = 12 AND
    	         (@sql_ver_minor > 0 OR
    	           (@sql_ver_minor = 0 AND
    	             (@sql_ver_build >= 4100)
    	           )
    	         )
    	       )
    	  BEGIN
    	    ALTER TABLE #backup_header
    	      ADD KeyAlgorithm nvarchar(32) NULL, 
    		  EncryptorThumbprint varbinary(20) NULL, 
    		  EncryptorType nvarchar(32) NULL
    	  END
    	  -- note: if FILE = <backup_set_file_number> not specified, default for RESTORE HEADERONLY is all backup sets in the media set are processed
    	  SET @command = N'RESTORE HEADERONLY FROM ' + @device + N' = ''' + @device_file + N''''
    	  INSERT INTO #backup_header
    	    EXEC (@command)
    	  -- set @file_number to greatest [Position] in backup sets
    	  -- do not forget this will return NULL if no backup sets!
    	  SELECT @file_number = MAX([Position]) FROM #backup_header
    
    	  DROP TABLE #backup_header
    

    The point here is that I put the output from RESTORE HEADERONLY into a temporary table. Your stored procedure could then e.g. finish with a SELECT from it, which should return a resultset which you are able to read normally. You might feel like adapting to your use case, if things aren't working for you otherwise!



  • I traced the query result from restore headeronly with wireshark and it's t here.



  • Yeah, makes sense, since you mentioned: "The same SQL query works fine using tsql (freeTDS diagnostic tool)." which presumably utilizes the TDS same stream as Qt.



  • What else to do on a Saturday evening? Found an old VM with MSSQLServer 2008 and the Northwind db on it, made a backupfile of it, then wrote a small QT console app that just either does:

    "select * from sys.databases"
    or "restore headeronly from disk = 'C:/Northwind.bak'"

    First I tested the Qt app on Windows, both statements worked (as expected). Then I launched a fresh Ubuntu 19.10 VM, installed Qt and did:
    sudo apt-get install unixodbc unixodbc-dev freetds-dev tdsodbc

    Run the same program, the "select * ..." worked (also as expected) but the "restore ..." failed the same way (the query returns ok, but next() fails).

    And of course, if I tested with osql, first I made a 1-line text file, containing one of the 2 SQL commands above, and then tried:

    osql -S dsn1 -U sa -P adminsa < commandfile
    

    (dsn1 is defined in my .freetds.conf and .odbc.ini in my ~)
    And both SQL commands worked. Which means I reproduced your openSUSE experience on Ubuntu 19.10 and Qt 5.14. (Also I could see the traffic on port 1433 using tcpdump)

    This pretty much points the finger on Qt's ODBC plugin libqsqlodbc.so :-(


  • Qt Champions 2019

    @hskoglund said in MSSQL with unixODBC and FreeTDS:

    This pretty much points the finger on Qt's ODBC plugin libqsqlodbc.so :-(

    Why? You're using the same one on Windows, or?



  • @Christian-Ehrlicher Yes, it's the same Qt .cpp code for Windows and Linux, so I'm thinking it's something wrong during the handoff of TDS traffic between Qt and the underlying ODBC driver, i.e. on Windows it's a .dll from Microsoft (where it works) and on Ubuntu it's /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so.



  • Continuing with "What to do on a Saturday evening...", found the culprit and I've built a working libqsqlodbc.so. It is indeed some kind of handoff problem, I could see all the 52 field descriptors were being returned ok (yes "restore headeronly..." returns 52 columns!) but when time come to the fetch, it failed in QODBCResult::fetchFirst() on Ubuntu, but not on WIndows.

    Here's the culprit/patched part of qsql_odbc.cpp:

    ...
    bool QODBCResult::fetchFirst()
    {
        Q_D(QODBCResult);
        if (isForwardOnly() && at() != QSql::BeforeFirstRow)
            return false;
        SQLRETURN r;
        d->clearValues();
        if (isForwardOnly()) {
            return fetchNext();
        }
    // line 1133 qsql_odbc.cpp: following 3 lines are replaced with a SQLFetch
    //    r = SQLFetchScroll(d->hStmt,
    //                       SQL_FETCH_FIRST,
    //                       0);
        r = SQLFetch(d->hStmt);
        if (r != SQL_SUCCESS) {
            if (r != SQL_NO_DATA)
                setLastError(qMakeError(QCoreApplication::translate("QODBCResult",
                    "Unable to fetch first"), QSqlError::ConnectionError, d));
            return false;
        }
        setAt(0);
        return true;
    }
    ... 
    

    SQLFetchScroll() is an ODBC 3.0-flavored function and maybe it's too advanced for FreeTDS, so on a lark I replaced it with the legacy ODBC 2.0 SQLFetch(), voila!

    But, as @Christian-Ehrlicher says above, this is not really a bug in Qt, rather FreeTDS/unixODBC stumbling with ODBC 2.0/3.0 differences or something. So consider it a workaround. So feel free to debug more/deeper and submit a bugreport :-)

    P.S. If you want to build it yourself, it's pretty easy, start MaintenanceTool and select Sources. Then open
    ~/Qt/5.14.1/Src/qtbase/src/plugins/sqldrivers/sqldrivers.pro
    from Qt Creator, edit odbc/qsql_odbc.cpp as per above, and then just rebuild the project. A new directory will appear in the directory above, i.e.
    ~/Qt/5.14.1/Src/qtbase/src/plugins/build-sqldrivers-Desktop_Qt_5_14_1_GCC_64bit-Release and in that directory you'll find the new, working libqsqlodbc.so in ../plugins/sqldrivers


  • Qt Champions 2019

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



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



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



  • @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 :-)



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



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



  • @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));
    

  • Lifetime Qt Champion

    Hi,

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



  • @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())


  • Lifetime Qt Champion

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


Log in to reply