What's the best way to connect to SQL Server from Ruby on OS X?

Question

I need to let my Rails app connect to a MS SQL Server database and do a simple query to do a lookup. The main DB for the app is MySQL. It just needs to do this SQL Server thing on the side.

What's the best way to do that?

I could write an entirely separate app in Java that connects to the DB and dumps some XML data to the filesystem for my Ruby app to pick up.

Or I could mess around with Ruby ODBC connectivity. I did a search on Gemcutter and found these...

• dbd-odbc (0.2.5) 11141 downloads
• ruby-odbc (0.99992) 6390 downloads
• activerecord-odbc-adapter (2.0) 2333 downloads
• odbc-rails (1.5) 2167 downloads

It would mean connecting to two different DBs from one Rails app, though. I'm not even sure how to do that.

Does anyone have experience working with SQL Server in Ruby? Any thoughts on which approach is most practical?

---

UPDATE -- got it working

Thanks for the help. Based on the answers and some research I worked it out. I'm going to paste my notes below. Sorry for the casual tone. I just copied them over.

Steps taken to connect to SQL Server with Ruby on OS X

Download, configure, build FreeTDS library

The docs are pretty detailed and clearly written, mostly. There are a few rough spots in crucial places that cost me a few hours of troubleshooting.

By default it installs into /usr/local/freetds/lib.

That gives you a bunch of utilities and stuff. They can help with testing and troubleshooting.

For example, from the docs: "The tsql utility is provided as part of FreeTDS expressly for troubleshooting."

Added this to /etc/profile:

# 2010-10-19
# To support the FreeTDS library for connecting Ruby to SQL Server.

PATH=$PATH:/usr/local/freetds/bin

# Have FreeTDS to log some output.
#export TDSDUMP=/tmp/freetds.log
#export TDSDUMPCONFIG=/tmp/freetdb_config.log

export PATH

FreeTDS config file

Need to create a config file for FreeTDS. The docs list several places where that can be stored. The only one that worked for me was ~/.freetds.conf...

[DATA_SERVER_NAME]
    host = hostname
    port = 1433
    tds version = 8.0

That's the whole config. For the [DATA_SERVER_NAME] you can't use the hostname or it won't work. Use some intuitive logical name. You'll use that name when you hook up to it inside Ruby code with the tiny_tds gem.

Don't need to set up ODBC

Despite what it says in the FreeTDS docs, there's no need to create a odbc.ini or a odbcinst.ini file. It works without it.

Installed tiny_tds gem

The tiny_tds gem connects Ruby to FreeTDS to an SQL Server database. In the tiny_tds docs it says...

'...please make sure to compile FreeTDS with libiconv support for encodings to work at their best. Run "tsql -C" in your console and check for "iconv library: yes".'

I was able to do that and it worked.

It's supposed to work like this:

require 'tiny_tds'
client = TinyTds::Client.new(:username => '...username...', :password => '...password...', :dataserver => 'DATA_SERVER_NAME')
sql = '... whatever ...'
result = client.execute(sql)
client.close

ActiveRecord

Supposedly you can use ActiveRecord with SQL Server after you get all that set up by installing the activerecord-sqlserver-adapter gem. I didn't need that so I didn't install it.

Answer 1

I just read in a book that there is a gem called "Magic Multi-Connections"

There is a Git repo: http://github.com/drnic/magic_multi_connections

You can Google it but I never tried it myself, so I suggest you ask more about it, or create a simple app that talks to your two DBMS and see if it works.

Answer 2

You can use the same strategy as I explained in this answer but use the ODBC adapter as explained here in your database.yml file. So basically you would do:

class SQLServerThing < ActiveRecord::Base
  establish_connection :sqlserver
  set_table_name 'things'
end

Now you can do:

SQLServerThing.find(1)

Or run generic queries on the database, do:

SQLServerThing.connection.select_all("SELECT * FROM table ...")

If you have no desire to use regular ActiveRecord methods, setting a table name is optional.

An additional gem (magic_multi_connections) is not needed unless you want to connect to multiple database from within the same model. By using a separate model, you can avoid bringing in a separate library.

Answer 3

Here is a good tutorial how to do that.

In general you'll need:

System level

• freetds
• tdsodbc
• libdbd-odbc-ruby

Gems

• dbi
• dbd-odbc
• activerecord-sqlserver-adapter
• activerecord-odbc-adapter

The latest two is if you are planning use activerecord.

Answer 4

There is currently a lot of development going on with the necessary gems for connecting to MS SQL.

Short summary:

• you no longer need dbi or dbd. ( The guide posted by c0r0ner is to old )
• all you need is freetds, ruby-odbc and activerecord-sqlserver-adapter
  ( follow the guide from Xavier Shay posted by wuputah )
• look out for tiny_tds coming soon as a replacement for ruby-odbc
  It makes the configuration even simpler and faster access to the database
  ( http://github.com/rails-sqlserver/tiny_tds )