HomeRamblings  ⁄  GeneralRuby LanguageSQLSetupsConfiguration

Making Ruby talk to MSDE

Published: April 09, 2010 (over 7 years ago)
Updated: over 2 years ago

Getting Ruby to talk to Microsoft SQL Server 2005 is one thing. Getting Ruby to talk to Microsoft SQL Server Developer Edition 2000, has one twist that threw me off for hours. Here, I’ll show you how to do and hopefully save you lots of frustration and brain damage from banging your head against the wall.

I won’t cover a complete Ruby and Rails/Ramaze/Sinatra installation. This post assumes you have that much down pat, so we’ll pick up the ball with installing FreeTDS, unixODBC, Ruby ODBC

If you’re using Ubuntu 8.10, you can get away with installing everything from packages with the following:

apt-get install  unixodbc freetds-common tdsodbc \
  libodbc-ruby1.8 \
  libdbi-ruby1.8 libdbd-odbc-ruby \
  autoconf make

NOTE: I wrote this article and right before publishing, attempted to replicate these results on both mac OS X and CentOS 5. Both failed to work with the technique I thought I had all nice and worked out. I spent several hours over many months trying to find out exactly what’s different between the packages, but never got very far with my investigations. For a long time, it looked to me like both the ODBC packages and the FreeTDS packages need to be tweaked to properly handle [SERVER][DB_INSTANCE] construct that MSDE uses vs. just [SERVER] that the full-fledged SQL Server uses. This, after all, is the convention of the Windows ODBC driver and DSN definitions. However, there’s a really simple solution that I hit upon on Actual Technologies’ website that led me to a true universal solution and that’s to learn the port the INSTANCE runs on and set up the DSN specifically to that port!

But beware, the above doesn’t get you all of the freetds tools, most importantly the tsql command which can help you with debugging issues before getting into the ODBC game (that is, direct TDS connections to your SQL Server). So, to cover the most distros possible, I’ll document the “from source” approach for *nix systems and macports for Macs so you’ll find two sets of installation instructions for every step.

Preparing your Environment

I prefer to pull down all source packages into my local user directory under a “~/src” folder. The instructions that follow assume you’re in this folder. If you’re not, then please make the appropriate substitutions as you follow along. Additionally, I configure and compile most things as a regular user then use sudo to install system-wide as a “super user.”

Install unixODBC

unixODBC provides the ODBC connectivity for the TDS driver.

wget http://www.unixodbc.org/unixODBC-2.2.14.tar.gz
tar zxfv unixODBC-2.2.14.tar.gz
cd unixODBC-2.2.14
./configure  --enable-gui=no
make
sudo make install

On Macs

sudo port install unixODBC

On CentOS

sudo yum install unixODBC unixODBC-devel

Install FreeTDS

FreeTDS provides the open source implementation of the protocol implemented by both Sybase and Microsoft SQL Server.

wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
tar zvxf freetds-stable.tgz 
cd freetds-0.82/
sudo ./configure  --with-unixodbc=/usr/local 
sudo make
sudo make install

On Macs

sudo port install freetds

On CentOS

sudo yum install freetds

Install ruby-odbc

The ruby-odbc package provides the ruby bindings for ODBC connectivity.

wget http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9995.tar.gz
tar zvxf ruby-odbc-0.9995.tar.gz
cd ruby-odbc-0.9995/
ruby extconf.rb
make
sudo make install

On Macs

sudo port install rb-odbc

Install ruby DBI

wget http://rubyforge.org/frs/download.php/655/ruby-dbi-all-0.0.23.tar.gz
tar zvxf 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

Configure FreeTDS and ODBC

If you’re connecting to either SQL Server 2000 or SQL Server 2005 Standard, Enterprise, etc., then the typical approach is to add an entry for the server to /etc/freetds/freetds.conf and then reference that entry in the /etc/odbc.ini. Sometimes, these files get installed in /usr/local/etc. If so, then I will usually symlink the files into /etc for convenience, so that’s what’s referenced below.

My server is called “apvdbs01” (the WINS name) with an IP Address of 10.0.2.2 and the database I’m connecting to is called “test_development” while “username” and “passwd” should be substituted with an valid user account and credentials for successfully connection to the database server.

Add a server entry for your server (which I typically give same name as Windows Server) to /etc/freetds.conf, so that host points to the SQL Server address:

[apvdbs01]
host = 10.0.2.2
port = 1433
tds version = 7.0

TDS version 7.0 is for SQL Server 2000. If you have SQL Server 2005, then tds version is 8.0.

tsql –S apvdbs01 –U username –P passwd 

Once you can connect directly with the TDS drivers, move on to adding the ODBC driver entries.

Add the FreeTDS ODBC driver by editing “/etc/odbcinst.ini” (make sure “/usr/local/lib/libtdsodbc.so” exists first):

[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so

Add a new DSN to “/etc/odbc.ini”:

[test_development]
Driver          = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = No
ServerName      = apvdbs01
Database        = test_development

So, “FreeTDS” is the driver that we gave for the lib TDS ODBC connector in the odbcinst.ini file (this allows you to install and maintain multiple versions of the libtdsodbc.so file). We reference this driver in the odbc.ini file with the “Driver” entry and we reference the actual server data in the freetds.conf file via the “ServerName” entry.

Now that you have ODBC configured for FreeTDS, test it with:

isql test_development username passwd 

But that doesn’t work for MSDE!

The above will get you connected to a standard or enterprise SQL Server 2000/2005 installation, but does not work for an Embedded SQL Server 2000/2005 installation. After trying many permutations, I finally came up with a configuration that actually works. One of the key things to know about MSDE is that the MSDE server instance is in the form of “servernameinstance” rather than straight up “servername” as with standard/enterprise installations. This is known as a “named instance.” The trick to connecting? Find out the port the named instance is listening on and connect to that port rather than the 1433 port!

You can determine the port used by the instance by using the Server Network Utility in the Microsoft SQL Server program group on the Windows server where your database resides. Select the instance name from the drop down list and select TCP/IP from the list of enabled protocols. This will show you the port number for that instance.

[test_msde2000]
Driver         = FreeTDS
Description    = ODBC connection via FreeTDS
Server         = 10.0.2.2
Database       = my_test_db
Port           = 1348
TDS_Version    = 7.0

I could then connect to the MSDE instance like so:

isql test_msde2000 username passwd 

Test ODBC connectivity from Ruby

If both tsql and isql commands work, then you’re well on your way to connecting to SQL Server via Ruby. To test Ruby with the above ODBC configurations:

1
2
3
irb 
require "dbi" 
dbh = DBI.connect("dbi:ODBC:test_development", "username", "passwd")

Connecting with Sequel

One of my favorite tools for connecting to SQL DBMS’ is Sequel, which is maintained by Jeremy Evans. To connect using Sequel, install the gem:

sudo gem install sequel

And then give this a shot:

1
2
3
4
5
irb
require 'rubygems'
require 'sequel'

DB = Sequel.odbc('test_development', :db_type => 'mssql', :user => 'username', :password => 'passwd')

References

  • http://www.freetds.org/userguide/confirminstall.htm
  • http://www.unixodbc.org/odbcinst.html
  • http://www.actualtech.com
comments powered by Disqus