Connecting to MSSQL with Ruby on Ubuntu

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

 

 

6 Comments so far

  1. Jason on August 11th, 2008

    I was struggling to get the freetds/odbc/odbcinst configs to work. This was very simple, and I got it done in 5 mins. Thanks!

  2. mlambie on August 11th, 2008

    I’m glad I was able to help out Jason.

  3. Jan Wikholm on August 20th, 2008

    Thanks for this Ubuntu specific version of the installation.
    I would like to point out this issue I had with unixODBC and libtdsodbc.so:

    [01000][unixODBC][Driver Manager]Can’t open lib ‘/usr/lib/odbc/libtdsodbc.so’ : /usr/lib/odbc/libtdsodbc.so: cannot open shared object file: No such file or directory
    [ISQL]ERROR: Could not SQLConnect

    $ ls -la /usr/lib/

    lrwxrwxrwx 1 root root 19 2008-08-20 17:18 libtdsodbc.so -> libtdsodbc.so.0.0.0

    And that was a broken symbolic link, since the referred file is not there.

    I found the solution in a thread of Ubuntu forums via Google:
    http://ubuntuforums.org/showthread.php?t=433435

  4. Sahand on October 21st, 2008

    Matthew, Thank you for this post. It saved me a lot of headache. The missing libtdsodbc caused a bit of headache, but I found this link:
    https://bugs.launchpad.net/ubuntu/ source/freetds/ bug/68239

    Keep up the good work.

  5. Greg Corrigan on December 9th, 2008

    Matthew,

    You should add package ‘tdsodbc’ to your initial apt-get to make sure the libtdsodbc entry does not haunt those following your excellent instructions:

    mlambie@ubuntu:~$ sudo aptitude install tdsodbc

  6. the_0ne on December 31st, 2008

    Wow, great directions. Worked perfectly. I added @Greg Corrigan’s instructions also, to include the tdsodbc library in the initial sudo aptitude install line.

    Thanks…

Leave a Reply