Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ruby on Rails
HowtoConnectToMicrosoftSQLServerFromRailsOnOSX (Version #40)

Home Page | All Pages | Recently Revised | Feed

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:
<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.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:
<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 !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</pre>

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

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:
<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.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:
<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 !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</pre>

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

i2 on Rails, powered by Phusion Passenger™ and Ruby Enterprise Edition.