I’m working on a problem for a client which involves connecting to a Microsoft SQL Server 2005 database from Linux using Ruby. Here’s what I did to get it working, based off some useful instructions that are tailored for Ruby on Rails:

Firstly, update your ~/.profile to include the following:

export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds/freetds.conf

Then reload your .profile, by logging out and in again.

Secondly, on Ubuntu 7.10 Server I needed to install some packages.

mlambie@ubuntu:~$ sudo aptitude install unixodbc unixodbc-dev freetds-dev sqsh

With FreeTDS installed I could configure it like this:

mlambie@ubuntu:/etc/freetds$ cat freetds.conf
[ACUMENSERVER]
  host = 192.168.0.10
  port = 1433
  tds version = 7.0

The important thing here is ACUMENSERVER, which is the DSN that I’ll use when connecting to the database. The host, and port are self-explanatory, and it’s worth noting that I had to use 7.0 specifically as the tds version.

Testing FreeTDS is not too hard:

mlambie@ubuntu:~$ sqsh -S ACUMENSERVER -U username -P password
sqsh: Symbol `_XmStrings' has different size in shared object, consider re-linking
sqsh-2.1 Copyright (C) 1995-2001 Scott C. Gray
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
1> use acumen
2> go
1> select top 1 firstname, lastname from tblClients
2> go

[record returned]

(1 row affected)
1> quit

Next up it’s necessary to configure ODBC:

mlambie@ubuntu:/etc$ cat odbcinst.ini
[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
CPTimeout       =
CPReuse         =
FileUsage       = 1

mlambie@ubuntu:/etc$ cat odbc.ini
[ACUMENSERVER]
Driver          = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = No
Servername      = ACUMENSERVER
Database        = ACUMEN

I then tested the connection with isql:

mlambie@ubuntu:~$ isql -v ACUMENSERVER username password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> use ACUMEN
[][unixODBC][FreeTDS][SQL Server]Changed database context to 'Acumen'.
[ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO
SQLRowCount returns -1
SQL> select top 1 firstname from tblClients;

[record returned]

SQLRowCount returns 1
1 rows fetched
SQL> quit

 
OK, so we’ve got ODBC using FreeTDS to connect to a remote MSSQL server. All that’s left is to add Ruby into the mix.

mlambie@ubuntu:~$ sudo aptitude install libdbd-odbc-ruby

The last thing to test is that Ruby can use DBI and ODBC to hit the actual database, and that’s easy to test:

mlambie@ubuntu:~$ irb
irb(main):001:0> require "dbi"
=> true
irb(main):002:0> dbh = DBI.connect('dbi:ODBC:ACUMENSERVER', 'username', 'password')
=> #<DBI::DatabaseHandle:0xb7ac57f8 @handle=#<DBI::DBD::ODBC::Database:0xb7ac5744
@handle=#<ODBC::Database:0xb7ac576c>, @attr={}>, @trace_output=#<IO:0xb7cbff54>,
@trace_mode=2>
irb(main):003:0> quit

And a more complete test (only with SQL SELECT, mind you):

#!/usr/bin/env ruby

require 'dbi'
db = DBI.connect('dbi:ODBC:ACUMENSERVER', 'username', 'password')
select = db.prepare('SELECT TOP 10 firstname FROM tblClients')
select.execute
while rec = select.fetch do
  puts rec.to_s
end
db.disconnect