ODBC on Unix

ODBC has some issues on Unix, mainly due to lack of clean specifications.

ODBC and 64-bit

ODBC was originally specified as 32-bit[23]. Its evolution to 64-bit took place in the absence of a good specification which led to conflicting declarations and associated problems. For instance, some parameters are defined as SQLINTEGER but are used for pointer offsets. But SQLINTEGER was (and remains) 32-bit, while pointer offsets must be 64-bit. Also row numbers and some other formerly 32-bit quantities are now 64-bit.

If you use unixODBC Frediano would recommend at least version 2.2.14. Earlier versions have issues if used on 64-bit environments.

sizeof(SQLWCHAR)

Under Windows sizeof(wchar_t) == sizeof(SQLWCHAR) == 2 but on many Unix systems you have sizeof(wchar_t) == 4. And some DMs decided to keep sizeof(SQLWCHAR) == 2 (including unixODBC) while in other DM sizeof(SQLWCHAR) == sizeof(wchar_t) == 4 (namely iODBC). This leads to incompatible ABIs between applications and drivers. If you compile the FreeTDS ODBC driver using iODBC take care to ensure all drivers are compiled with the same header files.

Alternatively, compile FreeTDS with both includes and rename the library to use two ABIs (for instance having a libtdsiodbc.so and a libtdsuodbc.so).

At the time of writing Ubuntu compiled Qt using iODBC but most packages use unixODBC. If you plan to use Qt with the FreeTDS ODBC driver, you should have an iODBC-compatible driver. Also be aware that the QODBC Qt driver has problems with iODBC and SQLWCHAR (see Qt). Due to these problems Frediano suggests not using this configuration (Qt database) on Ubuntu at this time.

Default charset

Character encoding is yet another trap. ODBC makes no provision for specifying client character encoding. By default many DM converting from multi-byte to wide characters assume the client uses ISO 8859-1. Even the FreeTDS driver assumes ISO 8859-1 by default. Also some DM have problems converting multi-byte encodings (like UTF-8), by assuming a byte can be converted to a single wide character (and vice versa). That creates problems if you use multi-byte encoding for FreeTDS driver.



[23] In fact, the earliest versions were 16-bit.