Getting Rails talking to SQLServer on OSX via ODBC

Published May 07, 2007

Getting your Mac to talk to SQLServer via ActiveRecord is akin to watching Gretel give commentary on who’s-picking-who’s-nose on the latest episode of Big Brother.

Though there’s a few nuggets of information floating around on the web—New Auburns’s article, the Rails wiki, and iamrichardson.com’s 2 part series—I wanted something that integrated with my preferred macports setup and didn’t involve recompiling ruby-dbi.

Update: Thanks to Maun Suang Boey from macports we’ve been able to update the rb-dbi port to include support for odbc, making this process a whole lot simpler.

So here’s the steps we need to take for a macports friendly SQLServer via Ruby-DBI via ODBC:

Install Ruby-DBI with ODBC
Install FreeTDS
Test FreeTDS
Configure FreeTDS
Add the FreeTDS ODBC driver
Create an ODBC datasource
Test the ODBC connection
Configure Rails

Notice no need for unix-odbc? OSX ships with ODBC and an ODBC admin tool to make our lives just that little bit easier.

Install Ruby-DBI with FreeTDS

Make sure you’ve synced with the latest macports tree:

sudo port sync

and install away:

sudo port install rb-dbi +dbd_odbc

If you you’ve already got the rb-dbi or the old rb-dbd-mysql and rb-dbd-postgresql ports installed you’ll probably need to remove them first.

You could also add +dbd_mysql and +dbd_pg if you like.

Install FreeTDS

$ sudo port install freetds

easy peasy.

Test FreeTDS

We need to test that we can connect to the SQL server using FreeTDS’s diagnostic tool, tsql tool. We must make sure this works before proceeding with modifying configs and setting up ODBC.

$ man tsql
NAME
       tsql - utility to test FreeTDS connections and queries

SYNOPSIS
       tsql   {-S servername [-I inside] | -H hostname -p port}
              -U username [-P password]
...

Plug in the values to your SQL server as below, specifying your own values for SQL_SERVER_ADDR, USER and PASSWORD:

$ tsql -H SQL_SERVER_ADDR -p 1433 -U USER -P PASSWORD
locale is "C/en_US.UTF-8/C/C/C/C" 
locale charset is "UTF-8" 
1>

We’ve got the 1> prompt, which means we’re succesfully connected. Let’s run a DB query just to see things are working, not forgetting the all-important ‘go’:

1> use jobfutures;
2> select top 1 memberId from tblMembers;
3> go
memberId
263

Woohoo! Now we’re talkin.

Configure FreeTDS

Now we’ve connected to SQL server using tsql we add a server entry to the FreeTDS config file, which can be found at /opt/local/etc/freetds/freetds.conf

[somesqlserver]
  host = HOST_ADDRESS
  port = 1433
  tds version = 8.0 # for SQL2000

Throw out the host and password parameters we needed before, we can now just refer to it using the server name somesqlserver:


$ tsql -S somesqlserver -U sa -P SA_USER_PASSWORD
locale is "C/en_US.UTF-8/C/C/C/C" 
locale charset is "UTF-8" 
1>

Add the FreeTDS ODBC driver

OS X ships with a small but handy tool ODBC Administrator, found in /Applications/Utilities, which you can use to manage your ODBC settings. Fire it up and go to the Drivers tab. Add the TDS driver with the following settings:

Description: TDS
Driver file: /opt/local/lib/libtdsodbc.so
Setup file:  /opt/local/lib/libtdsodbc.so
Define as:   System

Create an ODBC datasource

Add a new User DSN In ODBC Administrator with a name of somesqlserver (our Rails config will refer to this DSN) and the following Key/Values:

ServerName: somesqlserver (the name used in the FreeTDS config file)
Database: jobfutures (change to suit)

Test the ODBC connection

Firstly we test using the iodbctest tool:


$ iodbctest "dsn=somesqlserver;uid=USERNAME;pwd=PASSWORD" 
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0001.0928
Driver: 0.64 (libtdsodbc.so)

SQL>use jobfutures;
1: SQLExec = [FreeTDS][SQL Server]Changed database context to 'jobfutures'. (5701) SQLSTATE=01000
Statement executed. 0 rows affected.

woohoo!

And now in the colourful world of Ruby using irb:


require 'rubygems'
#=> true
require 'dbi'
#=> true
DBI.connect('DBI:ODBC:somesqlserver', 'USERNAME', 'PASSWORD')
#=> #<DBI::DatabaseHandle:0x106cf74 @trace_output=#<IO:0x2b890>, @trace_mode=2, @handle=#<DBI::DBD::ODBC::Database:0x106ced4 @handle=#<ODBC::Database:0x106cefc>, @attr={}>>

Woohoo! Only one tiny step between us and world SQLServer domination.

Configure Rails

All that’s left is to modify our database.yml to refer to our shiny new ODBC DSN:


development:
  adapter: sqlserver
  mode: odbc
  dsn: someservername
  username: USERNAME
  password: PASSWORD

Load up script/console and give it a whirl.

phew! If you made it this far you deserve a break, a beer and a few days off work (file a claim for “MS torture leave”).

Archived comments

Comments were previously allowed on articles. Though no new comments are being accepted you can see the old comments below.

bryanl

Hopefully more publicity will hurry up the effort to get the sqladapter fixed. Thanks for the great article.
Marcus Crafter

Top writeup. When I tried to get FreeTDS working with sql server, even after jumping all these hoops I still had problems with ‘text’ fields failing to be read properly.

In the end I gave in and purchased the commercial Mac ODBC driver from Actual which worked fine, and was able to get back to Rails coding again :)

M!
Brian Hogan

That’s very nice. The article on my site was mostly to help me remember how I did it and to share it with others. I like your way better too.
Tim Lucas

Yeah this was the third time a guy at work (me) had to do this w/o documentation, so it was more out of frustration than anything :)

Marcus: yeah I can understand just buying the Actual driver… it’s really really frustrating, especially if you thought you’d solved it. Haven’t had any probs with the text fields (yet, touch wood).

Brian: Thanks for posting your article! Would have been screwed w/o it.
Mark

Cheers! I am going to give this a go tonight. I am happily using a Mac in an office full of MS. I will be laughing with a simple activerecord/web/crud thingy for the sql server.
Rein Henrichs

Thanks, Tim. We’ve been using this at work as well. Saved much hair pulling.
Tim Perrett

Hey Tim,

Thanks for the article, it makes for interesting reading. I was wondering however what the performance was like using an ODBC connection? Is it comparable performance wise to any other databasae we might us that’s native to OSX?

Cheers

Tim
Patrick

Tim, every step was working great until i tried to run ‘iodbctest’ and was met with the following error: 1: SQLDriverConnect = [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded (0) SQLSTATE=IM002 1: ODBC_Connect = [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded (0) SQLSTATE=IM002

As I said everything prior worked great. Spent the last day beatin’ my head on the monitor. Was hoping that you might have some pointers!! Running OS X 10.4.11 Thanks Patrick
Patrick

Tim, ignore my previous comment. Got it working. Thanks for a great article! Patrick
Tys von Gaza

You can get around the whole ini file configuration mess by using a DSN-less string. Ie:

navision: adapter: sqlserver mode: odbc dsn: DRIVER=/opt/local/lib/libtdsodbc.so;TDS_Version=8.0;SERVER=sqlserver.com;DATABASE=DBNAME;Port=14330;uid=dbusername;pwd=dbpassword;

Tys von Gaza

Ops, lets try it again

navision:
    adapter: sqlserver
    mode: odbc
    dsn: DRIVER=/opt/local/lib/libtdsodbc.so;TDS_Version=8.0;SERVER=sqlserver.com;DATABASE=DBNAME;Port=14330;uid=dbusername;pwd=dbpassword;

Matt Newell

I got FreeTDS and the ODBC working fine but got confused on where the irb comes in. Where do I add the DBI.connect(‘DBI:ODBC:sqlConnect’, ‘user’, ‘pass’)
Steve Purcell

Thanks for this – very helpful.

Spotted one minor mistake; “Throw out the host and password parameters” should read “Throw out the host and port parameters”.
adam

Hi,

Thanks for the excellent info. We have been running this setup for over 6 months now but the database admin guy is saying that we have 105 connections to SQL Server! Not bad for 5 mongrels that are mostly idle. Do you know any way of checking / monitoring this?

thanks in advance

Adam.
Tim Lucas

Sorry Adam, no idea. Surely the mongrel’s wouldn’t still be hanging onto the connections? Maybe you can drop them on the SQL server end?
Tom

You mention Maun Suang Boey’s update, but I’m not sure your instructions have been updated?

This is one of the clearest posts I’ve found on the Net for connecting a Rails App to a Microsoft SQL Server on OSX. I just want to make sure it is up to date!
Tom

Alright I am pulling my hair out. I was able to get everything working except configuring the keyword/value pair in Leopard’s ODBC Administrator. It will NOT let me double click on the Key and Value to rename them! ARGGGH!

I don’t know if it matters, but I also had to use /opt/local/lib/libtdsodbc.0.0.0.so because there was no libtdsodbc.so file in that folder. Does that matter?
Tim Lucas

Tom: no idea on Leopard I’m afraid. Report back if you find out!
Ben Mishkin

Tim -

just use the tab key to move to the ey / value boxes. No idea why clicking isn’t working, but tabbing to the boxes does.

Feedback and discussion

For errors, updates or feedback please shoot an email to t.lucas@#{thisdomain}. There’s no place for discussion (yet)—see my notes and drop me a line if you’ve got any ideas.