How to make FreeTDS, unixODBC and QT working together

UnixODBC and FreeTDS driver compialtion and configuration has been taken from the http://it.toolbox.com/wiki/index.php/Ubuntu_Debian_FreeTDS_ODBC

Here are instructions for getting tsql and isql to play nice together.
Apt-getting doesn’t always get you all the tools you need to develop against SQLserver and Sybase.
Gather some important packages:

apt-get install libtool bison autotools-dev g++ build-essential tcsh unixodbc-dev tdsodbc

Download, extract and compile FreeTDS driver:

wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
tar xzvf freetds-stable.tgz
cd freetds-0.82/
./configure --prefix=/usr --sysconfdir=/etc --with-unixodbc=/usr --with-tdsver=8.0
 make && make install clean

Create a file named tds.driver.template with the following contents:

[FreeTDS]
Description     = v0.82 with protocol v8.0
Driver          = /usr/lib/libtdsodbc.so

Register the driver with ODBC

odbcinst -i -d -f tds.driver.template

On 64 bit Debian, I’ve run into the situation where freetds won’t compile against the distribution’s ODBC. To resolve this, it was necessary to compile and install a parallel ODBC and compile freetds against it.
If this is a server, you may not have a GUI, so don’t try to compile with it.

wget http://www.unixodbc.org/unixODBC-2.2.14.tar.gz
tar xvf unixODBC-2.2.14.tar
cd unixODBC-2.2.14
./configure --enable-gui=no
make
make install

Default installation dir is: /usr/local/bin/. Now, follow the above instructions replacing the configure line for freetds with this:

./configure --prefix=/usr --sysconfdir=/etc --with-unixodbc=/usr/local --with-tdsver=8.0

Possible errors:

  • SQLInstallDriverEx failed with Invalid install path
  • . Probably /usr/local/etc/odbcinst.ini file is missing, or use root account to install driver template.

How to test unixODBC:

odbcinst -s -q show available ODBC sources
odbcinst -d -q show available ODBC drivers
odbcinst -j show config
isql -v datasourcename login password conenct using data source name

How to test freetds:

tsql -H 127.0.0.1 -p 1433 -U sa -P password conenct using host name
tsql -S datasourcename -U sa -P password conenct using data source name from the freetds.conf file

Configure QT to work with unixODBC

Note that QT application will use files:
/usr/local/etc/odbcinst.ini
/usr/local/etc/odbc.ini

, but isql uses:
/etc/odbcinst.ini
/etc/odbc.ini

The best solution is to create symbolic links and keep orginal files in one place.

cd $QT/src/plugins/sqldrivers/odbc
qmake "INCLUDEPATH+=/usr/local/unixODBC/include" "LIBS+=-L/usr/local/unixODBC/lib -lodbc"
make
make install

On Ubuntu x64 we need to modify following files manually:
sudo vim /usr/local/include/sqltypes.h

#ifdef ODBCINT64
//typedef ODBCINT64     SQLBIGINT;
typedef long    SQLBIGINT;
#endif
#ifdef UODBCINT64
//typedef UODBCINT64    SQLUBIGINT;
typedef unsigned long   SQLUBIGINT;
#endif

vim ../../../sql/drivers/odbc/qsql_odbc.cpp

when connection fails, then try to update add LD_LIBRARY_PATH in Qt project settings
LD_LIBRARY_PATH = /usr/lib:/usr/lib/odbc:/usr/local/lib:/usr/local/Trolltech/Qt-4.7.4/lib

cd $QT/src/plugins/sqldrivers/tds
qmake "INCLUDEPATH+=$QT/QtSources/4.8.1/include" "LIBS+=-L/usr/local/unixODBC/lib -lodbc"
make 
make install
qDebug() << QSqlDatabase::drivers();

result:
(“QSQLITE”, “QODBC3”, “QODBC”)

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC3");
db.setConnectOptions("Driver={FreeTDSDriver};NetworkAddress=10.7.111.149,4101;Database=kplus;UID=kplus;PWD=kplus11;");
db.setUserName("kplus");
db.setPassword("kplus11");
if( !db.open() )
{
	qDebug() << "Connection errror:"  << db.lastError().text();
}
else
{
	QSqlQuery query("SELECT country FROM artist", db);
	while (query.next())
	{
		QString country = query.value(0).toString();
		qDebug() << country;
	}
}

/etc/odbc.ini
[blee]
#Driver = /usr/lib/libtdsodbc.so
Driver = FreeTDSDriver
Description = Northwind sample database
Trace = Yes
Server = 10.7.111.149
Port = 4101
Database = kplus
UID = kplus

cat /etc/odbcinst.ini
[FreeTDSDriver]
Description = v0.82 with protocol v8.0
Driver = /usr/lib/libtdsodbc.so
UsageCount = 2
CPTimeout =
CPReuse =

/etc/freetds.conf
[blee]
host = 10.7.111.149
port = 4101
tds version = 5.0
dump file = /var/log/freetds.log

References:
http://it.toolbox.com/wiki/index.php/Ubuntu_Debian_FreeTDS_ODBC
http://www.savelono.com/linux/how-to-configure-linux-odbc-connections-for-ms-sql.html
http://www.webaj.com/how-setup-mysql-dsn-datasbase-source-centos-myodbc-and-unixodbc-command-line.htm
http://www.clarksys.com/blog/2010/10/25/howto-configure-unix-odbc/

How to enable remote access to Xserver on Ubuntu 11.04 and Ubuntu 11.10

First of all remove -nolisten tcp from /etc/X11/xinit/xserverrc file:

cat /etc/X11/xinit/xserverrc 

result:

#!/bin/sh
#exec /usr/bin/X -nolisten tcp "$@"
exec /usr/bin/X "$@"

Then:
* Ubuntu 11.04 – with gdm

sudo vim /etc/gdm/custom.conf

File contents:

[security]
DisallowTCP=false

* Ubuntu 11.10 – with lightdm

sudo vim /etc/lightdm/lightdm.conf

File contents:

[SeatDefaults]
greeter-session=unity-greeter
user-session=ubuntu
xserver-allow-tcp=true

Other useful params of lightdm.conf file:
http://hmontoliu.blogspot.com/2011_10_01_archive.html