Updated on July 2nd, 2008.
As you may know, I develop a lot of new web applications using Ruby on Rails, and to completely buy in to the whole “cult that is Rails”, I purchased a Macbook Pro for development. I am really happy with it, but one of the problems I ran across is the fact that there’s no built-in or prepackaged way to connect to Microsoft SQL Server which I use at work for a lot of my projects.
This guide is written from my notes and should give you the tools you need to get up and running. Some of this information comes from the Ruby on Rails wiki, but some things there are a bit misleading.
This now works on Leopard too!
Getting everything ready
We’ll build everything we need from source. Open up your favorite terminal and create the following folders:
mkdir src mkdir /Library/ODBC
We’ll download all of the source files into the src/ folder so we don’t litter up the hard drive with miscellaneous files.
Next, install Xcode and MacPorts, and be sure to update your path per the Macports instructions.
sudo port install wget
Install and configure FreeTDS
wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz tar zvxf freetds-stable.tgz cd freetds-0.82 ./configure
Attention Leopard Users!
You need to do an additional step here:
cp /usr/bin/glibtool libtool
Then you can continue on…
make sudo make install cd .. rm -rf freetds-0.82/
To test the configuration, run tsql -C. You’ll see the below output or something similar.
Compile-time settings (established with the "configure" script):
Version: freetds v0.64
MS db-lib source compatibility: no
Sybase binary compatibility: unknown
Thread safety: yes
iconv library: yes
TDS version: 5.0
Next, configure FreeTDS by editing /usr/local/etc/freetds.conf. Add the following code to the bottom of this file:
[MY_SERVER_NAME] host = db.mydomain.com port = 1433 tds version = 8.0
The MY_SERVER_NAME placeholder is an identifier for your server. Examples might be PRODUCTION_RAILS_SERVER or something t hat helps you identify it in other configuration files.
Configuring iODBC to play nice
If you’ve ever configured the ODBCUnix packages for Linux then you might be familiar with this section. I tried to use a configuration that I’ve used on Ubuntu before but with no luck. It turns out that there are a few extra steps when it comes to OS X and ODBC with FreeTDS.
iODBC is going to look for the configuration files in /Library/ODBC which was not created on my system. Create that folder if it’s not there.
Edit the file /Library/ODBC/odbcinst.ini and place the following contents in that file:
[ODBC Drivers] TDS = Installed [TDS] Description = FreeTDS Driver = /usr/local/lib/libtdsodbc.so Setup = /usr/local/lib/libtdsodbc.so
This file basically binds FreeTDS to iODBC and is really, really important to getting this whole thing working. Make sure that you have both the Driver and Setup paths correct (they do in fact point to the same file.)
Finally, tie it all togoether by editing the file /Library/ODBC/odbc.ini and using this code:
[ODBC Data Sources] MY_SERVER_NAME = TDS [MY_DSN] Driver = /usr/local/lib/libtdsodbc.so Description = Description of this database connection Trace = yes TraceFile = /tmp/odbc.log Servername = MY_SERVER_NAME Database = MY_DATABSE_NAME
Test the connection.
You should get some sort of message saying it connected successfully.
. If you got a message saying that the data source name could not be found then you need to check your configuration. If your computer just seems to hang for ever without responding, check your Internet connection and check to see if the DBA has firewalled your machine off.
Getting Rails to work
Assuming that Rails is already installed, you need to install two additional libraries for Ruby.
cd ~/src wget http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9995.tar.gz tar -zxvf ruby-odbc-0.9995.tar.gz cd ruby-odbc-0.9995 ruby extconf.rb make sudo make install cd .. rm -rf ruby-odbc-0.9995/ wget http://rubyforge.org/frs/download.php/655/ruby-dbi-all-0.0.23.tar.gz tar -zxvf ruby-dbi-all-0.0.23.tar.gz cd ruby-dbi-all ruby setup.rb config --with=dbi,dbd_odbc ruby setup.rb setup sudo ruby setup.rb install cd .. rm -rf ruby-dbi-all/ wget http://rubyforge.org/frs/download.php/33959/dbi-0.2.0.tar.gz tar -zxvf dbi-0.2.0.tar.gz cd dbi-0.2.0 ruby setup.rb config --with=dbi,dbd_odbc ruby setup.rb setup sudo ruby setup.rb install cd .. rm -rf dbi-0.2.0
That’s it. That should get everything working so you can connect via Ruby.
. To add additionlal database servers, add them to the freetds.conf file. To add additional DSNs that point to databases on one of the existing servers in freetds.conf, change odbc.ini in the /Library/ODBC folder.
Database configuration in Rails
Here’s the database.yml configuration file for a typicall Rails application using a DSN.
production: database: database_name adapter: sqlserver mode: odbc dsn: MY_DSN username: foo password: password