RubyOnRails using MSSQL Server and FreeTDS

So I’m no Ruby expert but I’ve need of connecting Rails to a MSSSQL Server 2000 DB. I just wanted to offer a bit more explanation at the FreeTDS and iODBC pieces of the installation.

UBER BIG DISCLAIMER: I’m no Rails export (had to say it again) so although I can try to help, please don’t hate me if for some reason these instructions don’t work for you; I’m just trying to help out. I’m mostly using the instructions from the wiki.rubyonrails.com site.

First we need to install FreeTDS (version 0.63 as of this writing). I used the freetds docs as a guide:

1. Download the latest stable release
2. Extract the tar file to your “/usr/local” directory
3. Using Terminal, cd to your “/usr/local/freetds-0.64″ folder
4. run: ./configure
5. run: make
6. run: sudo make install
7. Once the install is complete run “tsql -S mypool -C”. This should present you with something that looks a bit like this:

[source:ruby]
Compile-time settings (established with the “configure” script):
Version: freetds v0.64
MS db-lib source compatibility: no
Sybase binary compatibility: unknown
Thread safety: yes
iconv library: yes
TDS version: 5.0
iODBC: yes
unixodbc: no
[/source]
Now let’s check and see if we can connect to one of our SQL Server DB’s.

1. By default FreeDTS installed freetds.conf, locales.conf, and pool.conf to your “/usr/local/etc” directory.
2. Open the freetds.conf file to edit. Circa line 94 you’ll find the [mypool] host we just tested a moment ago. You’ve also several other templates for creating “mappings” (if you will) for FreeDTS to use to your SQL Servers.
3. Just take one of the configurations and pop in your credentials to connect to a database. For example:

[source:ruby]
[MySQLServer]
host = mySQLdb.somewhere.com
port = 1433
tds version = 8.0
[/source]

1. Now go back to your terminal and run:
   tsql – S mySQLdb -U <<InsertUsernameHere>>
2. You’ll be prompted to type your password and if successful you’ll see a “1>”; type Control-Z to exit.

Next configure your ODBC ini files

1. In Terminal again, cd to /Library/ODBC (you may need to create the directory)
2. Create the following files:

odbcinst.ini
[source:ruby]
[ODBC Drivers]
JDBC = Installed

[JDBC]
Description = Sybase JDBC Server
Driver = /usr/local/lib/libtdsodbc.so
Setup = /usr/local/lib/libtdsodbc.so
[/source]
odbc.ini
[source:ruby]
[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
[/source]
*NOTE: You need to make sure that the “Servername” in this step is the same as the value in Step 3 of the previous section, or else you may twist your head over why it won’t work.

Now test your connection by running:

[source:ruby]iodbctest “dsn=MySQN;uid=USERNAME;pwd=PASSWORD”[/source]

And a command prompt should display on the screen. You can now try and type in any SQL statement (i.e., SELECT * FROM MyTable) and be amazed at the code shooting across the screen.

OK, so that finishes getting iODBC and FreeTDS connecting. Once again, this was just a “spell-it-out-for-those-who-need-it” version of the first half of the official wiki.rubyonrials documentation.

See RubyOnRails using MSSQL Server and Free DS Part 2

Technorati Tags: Apple, SQL Server 2000, Ruby On Rails