Ruby on Rails
HowtoConnectToMicrosoftSQLServerFromRailsOnOSX

Getting Rails connected to Microsoft SQL Server from OS X requires two things:

1) An ODBC driver manager – unixODBC and iODBC are the most popular.
2) An ODBC driver – Almost all solutions involve FreeTDS, an open source library that allows for connecting to MS SQL Server and Sybase.

I first tried following the instructions in HowtoConnectToMicrosoftSQLServerFromRailsOnLinux but ran into some difficulties with getting FreeTDS to talk to unixODBC. However, a fallback is available since iODBC comes pre-installed with the OS.

Successfully made connection using:

OS X 10.4.4
iODBC 3.52.1
FreeTDS 0.63
ActiveRecord 1.13.2
ruby-dbi 0.0.23
ruby-odbc 0.997

I used `port install …` all the way. I found the iODBC version in the about page for ‘ODBC Administrator’. If anyone can explain how to load the sql server drivers into this gui, I’d appreciate it. —MattScilipoti

OS X 10.5.1
iODBC 3.52 (in Leopard)
FreeTDS 0.64_1+darwin_9
ActiveRecord 1.13.2
ruby-dbi 0.1.1_1
ruby-odbc 0.9995_0 (Sooo close to 1.0)

Assuming you already have ActiveRecord installed the only thing you will need to download and install is FreeTDS.

You can install FreeTDS using MacPorts with:

sudo port install freetds

You want to compile?
(Check here for help installing FreeTDS.)

Once FreeTDS is installed you can check your settings by running:

% tsql -C

My settings were:

Compile-time settings (established with the "configure" script):
                           Version: freetds v0.63
    MS db-lib source compatibility: no
       Sybase binary compatibility: unknown
                     Thread safety: yes
                     iconv library: yes
                       TDS version: 8.0
                             iODBC: yes
                          unixodbc: no

You don’t have to have exactly these settings. The real test comes after you create the config.

NOTE: You can avoid messing with these config files, see the DSN-less note at the bottom of the page.

Next you need to configure the following files as indicated: Note: If you did NOT install via MacPorts you probably use /usr/local.

/opt/local/etc/freetds/freetds.conf:

Add a section to the bottom of the file (remove the parenthetical comments):


[YOUR_DB_DEFINITION_NAME]
        host = 192.168.1.101  (change this to the right one for you)
        port = 1433
        tds version = 4.2  (might be 8.0, check the docs for the version of SQL Server you are running)

TEST!


# tsql -S YOUR_DB_DEFINITION_NAME -U USERNAME -P PASSWORD

You should see and do the following to make sure all went well:


locale is "en_US.ISO-8859-15" 
locale charset is "ISO-8859-15" 
1> use YOUR_ACTUAL_DB_NAME
2> go
1> select * from users   (do this for a table that exists in your db)
2> go
id      login
1       user1
3       user2   (make sure you get correct output)
1> quit

Now we create the ODBC configs.
You should be able to do this using the ‘ODBC Administrator’, but I couldn’t find out how. —MattScilipoti

You probably have to make the /Library/ODBC dir.

/Library/ODBC/odbcinst.ini:


[ODBC Drivers]
JDBC = Installed

[JDBC]
Description = Sybase JDBC Server
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so

/Library/ODBC/odbc.ini:


[ODBC Data Sources]
MySQLServer = JDBC

[MyDSN]
Driver = /usr/local/lib/libtdsodbc.so
Description = Description of this database connection
Trace = yes
TraceFile = /tmp/odbc.log
Servername = MySQLServer
Database = YOUR_ACUTAL_DB_NAME

Since we just installed FreeTDS, I used it (gleaned from HowtoConnectToMicrosoftSQLServerFromRailsOnFreeBSD) . These paths assume `port install freetds`. If you compiled it, you know how to find it. – MattScilipoti

/Library/ODBC/odbcinst.ini:


[TDS]
Description     = FreeTDS
Driver          = /opt/local/lib/libtdsodbc.so
UsageCount      = 1

/Library/ODBC/odbc.ini:


[YOUR_DB_DEFINITION_NAME]
Driver          = TDS
Description     = ODBC connection to via FreeTDS
Trace           = No
Servername      = YOUR_DB_DEFINITION_NAME
Database        = YOUR_ACTUAL_DB_NAME

TEST your odbc configs:

% iodbctest "dsn=MyDSN;uid=USERNAME;pwd=PASSWORD"

You should be able to run SQL commands at this point. If so, congratulations: you have a working DSN and are 80% of the way to getting Rails working with ODBC under OS X.

Now you need to install a couple of Ruby libraries: ruby-dbi and ruby-odbc.

If you’re using MacPorts, you can do:

sudo port install rb-odbc
sudo port install rb-dbi +dbd_odbc

Compilers, type away.

cd ~/tmp
curl -O !http://www.ch-werner.de/rubyodbc/ruby-odbc-0.996.tar.gz
tar vxzf ruby-odbc-0.996.tar.gz
cd ruby-odbc-0.996
ruby extconf.rb
make
sudo make install

cd ~/tmp
curl -O <a href="http://rubyforge.org/frs/download.php/655/ruby-dbi-all-0.0.23.tar.gz">http://rubyforge.org/frs/download.php/655/ruby-dbi-all-0.0.23.tar.gz</a>
tar vxzf 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

Test Connection from Ruby:

Heres a quick way to see if ruby can talk to SQL Server provided by Mark Imbriaco:


# irb1.8
irb(main):001:0> require "dbi" 
=> true
irb(main):004:0> dbh = DBI.connect('dbi:ODBC:YOUR_DB_DEFINITION_NAME', 'USERNAME', 'PASSWORD')
=> #<DBI::DatabaseHandle:0xb7d28688 @trace_output=#<IO:0xb7d79064>, @trace_mode=2, 
    @handle=#<DBI::DBD::ODBC::Database:0xb7d28480 @attr={}, 
    @handle=#<ODBC::Database:0xb7d284a8>>>
irb(main):005:0> quit

Note: if you installed RoR using MacPorts, ensure your odbc.ini and odbcinst.ini are in /opt/local/etc or else Ruby will complain that it can’t find your DSNs.

All that remains now is to configure conf/database.yml:

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

Note: As of Rails 2.0.2, this is no longer needed.

Please note: should you run rake from your application root at this point you will receive failure messages along the lines of:

sh: line 1: scptxfr: command not found
/usr/local/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:21: command not found: scptxfr /s  /d  /I /F db /q /A /r
sh: line 1: osql: command not found

The Rake file lib/tasks/databases.rake assumes you have these two utilities installed; unfortunately they’re Windows only. See defect 3298

There is a gem for this…rubyforge.org… mssqlclientadapter

Note: You can avoid the configuration file mess of FreeTDS and ODBC by using a DSN-less connection string in your database.yml.

development:
    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;

Updated on June 03, 2008 13:58 by AndyAtkinson (199.199.210.66)

Ruby on RailsHowtoConnectToMicrosoftSQLServerFromRailsOnOSX

Ruby on Rails
HowtoConnectToMicrosoftSQLServerFromRailsOnOSX