2014-03-07

Install ODBC and MS SQL Server Client for Erlang

http://arcusfelis.github.io/blog/2012/07/02/odbc/

Install ODBC and MS SQL Server Client for Erlang

Here we are connecting to MS SQL Server 2008 on MS Windows XP from Erlang on Debian Linux.

Part 1

Install utilities (FreeTDS SQL client):
1
# apt-get freetds-bin freetds-dev
Try to connect with the tsql command from the freetds-bin package:
1
tsql -H %ip_address_or_hostname% -p %port_number% -U %username% -P %password%
or (with actual values for me):
1
2
3
4
5
$ tsql -H PI -p 1433 -U test -P test
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>
If you see the counter (1, 2, 3, …), most likely tsql is unable to connect to the indicated server. Check the firewall’s settings.
sqsh is an alternative for tsql (more powerful).

Part 2

Install the ODBC client driver.
1
apt-get install tdsodbc

Problems? Part 2a

tdsodbc uses unixodbc library, that conflicts with libiodbc2libiodbc2 is a dependency for soprano, that is a dependency for the most of KDE applications.
So, you cannot use the KDE and UnixODBC in Debian (the bug from 2011 year).
This conflict can be fixed, downloading the patched versions of the packages from here:
1
2
http://packages.ubuntu.com/precise/libsoprano4
http://packages.ubuntu.com/precise/i386/soprano-daemon
If you don’t use KDE or the bug was fixed, skip this step.

Part 3

Update the config files:
/etc/odbc.ini
1
2
3
4
5
6
7
[odbc-test]
Description = test
Driver = ms-sql
Servername = odbc-test
UID = test
Database = test_db
Port = 1433
/etc/odbcinst.ini
1
2
3
4
5
6
[ms-sql]
Description = TDS connection
Driver = /usr/lib/i386-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/i386-linux-gnu/odbc/libtdsS.so
UsageCount = 1
FileUsage = 1
Test it:
1
2
$isql -v odbc-test test test
SQL>

Part 4

Try it from Erlang:
1
2
3
4
5
6
1> odbc:start().
ok
2> {ok, S} = odbc:connect("DSN=odbc-test;UID=test;PWD=test", []).
{ok,<0.44.0>}
3> odbc:sql_query(S, "SELECT 1").
{selected,[[]],[{1}]}
   ErlangODBC