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:

——
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

——
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:
<pre>sudo port install freetds</pre>

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

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

<pre>% tsql -C

My settings were:

<pre>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!



    
	
  1. 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

Two options are available TDS or JDBC drivers.
h3. TDS
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

JDBC
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

——
TEST your odbc configs:
<pre>% 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:
<pre>sudo port install rb-odbc sudo port install rb-dbi +dbd_odbc </pre>

Compilers, type away.
<pre>cd ~/tmp curl -O <a href="http://www.ch-werner.de/rubyodbc/ruby-odbc-0.996.tar.gz">http://www.ch-werner.de/rubyodbc/ruby-odbc-0.996.tar.gz</a> tar vxzf ruby-odbc-0.996.tar.gz cd ruby-odbc-0.996 ruby extconf.rb make sudo make install</pre>

Find latest version of ruby-dbi at http://rubyforge.org/projects/ruby-dbi/
<pre>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:

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

—
Rails 2.1.0 users may see the following error:

/Library/Ruby/Site/1.8/rubygems.rb:142:in `activate':Gem::Exception: can't activate activerecord (= 1.15.6, runtime), already activated activerecord-2.1.0

This was resolved by installing the activerecord-sqlserver-adapter gem:

sudo gem install activerecord-sqlserver-adapter --source=http://gems.rubyonrails.org
Updated on August 08, 2008 13:23 by Anonymous Coward (149.165.251.66)
Edit | Back in time (45 older versions)