getting ruby to talk to mssql using odbc on unix

Wed, Feb 17, 2010

This was a bit of an adventure and a lot of googling so here it is in distilled form. Note that the unix paths have a space after unixodbc/ as otherwise wordpress goes wonky!

Download unuixODBC, unpack and install:

./configure –prefix=/usr/local/unixodbc –enable-gui=no
make
make install
Download  FreeTDS, unpack and install:
./configure –prefix=/usr/local/unixodbc –with-unixodbc=/usr/local/unixodbc
make
make install
Download rubyodbc, unpack and install:
ruby extconf.rb –with-odbc-dir=/usr/local/unixodbc
make
make install
Add this to /usr/local/unixodbc/ etc/freetds.conf:
[mssql]
host = SERVER_ID_ADDRESS
port = 1433
tds version = 8.0
client charset = UTF-8
and test FreeTSD access:
cd usr/local/unixodbc/ bin
./tsql -S mssql -U username -P password
…
use DBNAME
go
select something from something
go
quit
Add this to /usr/local/unixodbc/ etc/odbc.ini:
[mssql]
Driver          = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = No
Servername      = nhcsits
Database        = DBNAME
Port            = 1433
add this to /usr/local/unixodbc/ etc/odbcinst.ini:
[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/local/unixodbc/ lib/libtdsodbc.so
Setup           = /usr/local/unixodbc/ lib/libtdsS.so
CPTimeout       =
CPReuse         =
FileUsage       = 1

[ODBC] Trace = Yes TraceFile = /tmp/odbcinst_tr ForceTrace = Yes

Also, if I didn’t add this to ~/.profile on OS X:
export ODBCINI=/usr/local/unixodbc/ etc/odbc.ini
I got the error:
[unixODBC][FreeTDS][SQL Server]Unable to connect to data source (DBI::DatabaseError)
The last bit of testing is isql:
cd usr/local/unixodbc/ bin
./isql -v mssql username password
select something from something
quit
That’s the boiler stuff done, so now it’s just a case of writing some simple Ruby code:
require ‘dbi’
odbcConnection = DBI.connect(“DBI:ODBC:mssql”, “username”, “password”)
odbcConnection.select_all(‘select * from table’) { |row| puts row }

comments powered by Disqus