Accessing MS SQL Server with QODBC

hsfougaris

I have an issue trying to access data from a MS SQL Server through ODBC, from OSX (it should be applicable under Linux too).

The problem becomes evident when trying to access non-latin characters (I am trying with Greek), and is related to the fact that MS SQL Server stores everything internally as UCS-2 and not as unicode or UTF-8 (irrespectively of data type definition text, varchar, nvarchar, etc).

Under Windows there aren't any issues using the default pre-built binaries and libraries, and all text is retrieved/stored properly when using Microsoft's driver (their typical magic custom libraries that address the many issues of their products. and their custom extensions to standard protocols)

Under OSX and Linux one has to choose a 3rd party ODBC driver, as Qt made the huge mistake of dropping support for QTDS.
Their reasoning was based on claims that it wasn't supported anymore and it doesn't support new features, but today (and I guess for a very long time) there is no other way of talking to an MS SQL Server outside of Windows (except from JDBC).
3rd party ODBC drivers (outside Windows of course) are based on TDS: I know of 2, OpenLink and ActualTechnologies, and I think only OpenLink is actively developed/supported. There is also another one I've tested that uses a ODBC to JDBC bridge, but that was too slow to be of practical use.

Anyway, back to the issue: I purchased the driver from OpenLink (the Lite version - the Express is the one using the bridge).
They give you 2 drivers, a unicode one, and a non-unicode one, and two iODBC demo apps, a unicode one, and a non-unicode one.

The only way I can get "good" (proper Greek) data from the MS SQL Server was when I was the non-unicode driver, and non-unicode app.
This kind of makes sense to me, as my server isn't really unicode (my database uses varchars, but I tried retrieving data from nvarchar fields, but it didn't change anything as expected).

So in my Qt app, I am trying to retrieve data and the greek text is garbled.
After a lot of troubleshooting, I realized the QODBC code (qsql_odbc.cpp, line 1951) has a checkUnicode that sets a variable unicode to true or false, and then upon retrieving data converts it to a QString differently, depending on this variable.
The way it determines unicode support is based on whether the server returns 2 byte unicode characters. It evens run a 'SELECT test' on the server, but it doesn't really test for proper string encoding/decoding.
For me the solution is to comment all the code in this function, simply set unicode=false, recompile the driver and use that instead of the pre-built one.

I am not really sure where if is something that directly relates to Qt (because in theory it's doing some sane tests that would normally suggest the database is indeed unicode), but it is a big issue when trying to use a very popular database server (unfortunately) from OSs other than Windows.
Qt might claim you can access MS SQL Server with ODBC, but there are issues, and more alarmingly the 3rd party drivers are based on a driver Qt had and decided wasn't good enough...

hsfougaris

I managed to try the FreeTDS ODBC driver under Fedora:
Surprisingly, this (outdated,unsupported) library worked out of the box with greek, and I didn't have to do anything funny with Qt or ODBC ini files
(all I had to do was to add
client charset = UTF-8
to my freetds.conf file).

andre

Thanks for posting back with your progress and solution.

hsfougaris

The only question for me now is whether the FreeTDS ODBC driver under OSX works so easily and well.
I'll try building/testing it and let everyone know...

hsfougaris

The FreeTDS ODBC driver works just fine under OSX too.
So to summarize,

When trying to access MS SQL Server from Qt, you only have the option of using ODBC, as FreeTDS was deemed unsupported, outdated, etc.
Outside of Windows, the only 3rd ODBC drivers that exist are based on FreeTDS.
They seem to have issues under Qt when using non-latin characters, which the FreeTDS ODBC driver doesn't have.
So basically the best ODBC driver to use is FreeTDS, which seems like a chicken-and-egg thing to me, as we are not supposed to use FreeTDS (according to Qt) but ODBC, but the only working ODBC driver is based on FreeTDS.

Maybe they were just thinking of Windows where Microsoft's ODBC driver is very robust (even though they are trying to steer people away from it).