Despite the great strides in the Virtualization Support of Fedora, I still prefer the polished capabilities of VMware Server.  In fact, I will happily install VMware on any big, beefy Fedora Linux box I can get my hands on.  In my opinion, the Virtualization Group in Fedora's Yum Repository (i.e Xen Kernel, KVM, and Qemu) are still in need of fine-tuning, and aren't quite ready for use in a reliable setting.

Software professionals need reliability for their environments -- we have enough bugs and infrastructure quirks to deal with in our own solutions to need to diagnose and debug an entire Host or Guest Operating System environment!    We'll play with Xen and the other Virtualization Toys on our personal machines later, but for now, to keep things humming along on the bleeding edge, I'd rather eliminate a potential point-of-failure and work with something that's polished, mature, and freely available:  VMware Server.

The goal of this article is to create a "Production-Quality" Rails Server. Thus, the best possible technologies (strictly my opinion) have been selected at the time of this writing to achieve this goal.

Unlike other developers, I prefer to install my Rails Applications under Web Subdirectories, such as http://www.not404.com/MyRailsApp, instead of running it as a Root Application of a Web Root, such as http://MyRailsApp.not404.com/. These instructions are geared for how I lay things out, but will let you know what to adjust in order to run your Rails Apps as traditional Web-Root Applications.

You may also notice that these instructions are SQLite3-oriented. This is intentional. IMHO, it's better to use the simplest-case database to prove that everything else is properly stitched together. Then, once you're satisfied that everything is properly locked down and performance-tuned, you can focus your attention on tying your Rails Application to a real database.

UnixODBC

Install UnixODBC

Fedora Core has a package out-of-box, so we'll use it:

• yum install unixODBC unixODBC-devel

Configure UnixODBC

Place variables similar to these in an /etc/profile.d global-settings file, or in your ~/.bashrc file

• export ODBCINI=/etc/odbc.ini
• export ODBCSYSINI=/etc

FreeTDS

(Easy Way) Download FreeTDS RPM

FreeTDS isn't included out-of-box in Fedora because of potential Intellectual Property issues. But Dries and others have an RPM that can be used:

• wget ftp.belnet.be/packages/dries.ulyssis.org/fedora/fc6/i386/RPMS.dries/freetds-0.63-1.2.fc6.rf.i386.rpm
• rpm -ivH freetds*i386.rpm

(The Hard Way) Build and Compile FreeTDS

FYI, I didn't take this approach, so you're on your on if you take this route. :-7

• wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
• tar -xvzpf freetds-stable.tgz
• cd freetds*
• ./configure
• make
• make install

Configure FreeTDS

• nano /etc/freetds.conf
• Add something similar to the following to the tail end of /etc/freetds.conf, substituting your own values as appropriate.
  

   [SQL2005Host1]
    host = 192.168.1.101
    port = 1433
    tds version = 8.0
  

• See Also FreeTDS Configuration Options For Reference.
• See Also FreeTDS Protocol Selection For Reference.

Test FreeTDS

• tsql -S SQL2005Host1 -U UserName

You should be able to type in your password and get a ">" prompt. At this point, try fetching something from a database table, to make sure your connection and database access controls are how you expect them.

 use SomeDatabaseName
 go

 select * from SomeTable
 go

 quit

Configure an ODBC DSN

• nano /etc/odbc.ini, and add a hunk similar to the following, Change values to suit your own environment.

  [SQL2005DSN1]
  Driver          = FreeTDS
  Description     = ODBC Connection via FreeTDS
  Trace           = No
  Server          = 192.168.1.101
  Port            = 1433
  TDS Version     = 8.0
  Database        = SomeDatabaseName
  

• nano /etc/odbcinst.ini, and add a hunk similar to the following. Change the values to suit your own environment.
  

  [FreeTDS]
  Description     = ODBC Connection via FreeTDS
  Driver          = /usr/lib/libtdsodbc.so.0
  FileUsage       = 1
  

Test UnixODBC Over FreeTDS

• isql SQL2005DSN1 MyUserID MyPassword should reveal something like the following:

  +---------------------------------------+
  | Connected!                            |
  |                                       |
  | sql-statement                         |
  | help [tablename]                      |
  | quit                                  |
  |                                       |
  +---------------------------------------+
  SQL> 
  

Ruby-ODBC Module

Build and Compile Ruby-ODBC

• wget http://ch-werner.de/rubyodbc/ruby-odbc-0.9993.tar.gz
• tar -xvzpf ruby-odbc*gz
• cd ruby-odbc*[0-9]*
• ruby extconf.rb
  

  checking for sql.h... yes
  checking for sqlext.h... yes
  checking for SQLTCHAR... yes
  checking for SQLLEN... yes
  checking for SQLULEN... yes
  checking for odbcinst.h... yes
  checking for dlopen()... yes
  checking for dlopen() in -ldl... yes
  creating Makefile
  

• make
• sudo make install

Test the Ruby-ODBC Module

• ruby -e 'require "odbc"'

You should just get back to the command prompt. if you get a LoadError, something is amiss within this step.

Rails-ODBC ActiveRecord Adapter

Build and Compile Rails-ODBC

Caution: At this writing, the Rails-ODBC Layer is not quite usable with SQL2005. Hopefully an updated version will be, though.

• wget http://rubyforge.org/frs/download.php/13979/rails-odbc-1.1.tgz
• tar -xvzpf rails-odbc*tgz
• cd rails-odbc*[0-9]
• sudo ruby install_odbc.rb

Make modifications as you see fit (I didn't change anything on my own boxes), and then type "i" to proceed with the installation.

Update config/database.yml

Modify your database.yml to something similar to this:

development:
  adapter: odbc
  dsn: SQL2005DSN1
  username: MyUserID
  password: MyPassword
  trace: false
  convert_numeric_literals: true

Optional Patch To Get Current Database Name

The Rails-ODBC connection-adapter doesn't yet implement current_database. The following patch will implement a current_database function that gives the ODBC DSN in place of the current_database. For my needs (My application tells users what database they're working against), this is sufficient enough -- I can name my DSNs to reflect a meaningful name.

If you also have a need for current_database, add the following hunk of code to the tail-end of your Rails Application's environment.rb file:

# LALEE's Quick-Fix for ODBCAdapter
class ActiveRecord::ConnectionAdapters::ODBCAdapter
       def current_database
         @connection_options[0] # 0 == dsn name, 1 == username, 2 == password
       end
end

Database Migrations and Type Mappings

I've noticed that using SQL2005 Databases under this solution is a bit more strict than using the Ruby-DBI/ADO solution under Windows. Specifically, I'm noticing type-mismatch complaints when using strings to search for text-fields. The following document may be helpful in identifying these issues:

• http://virtuoso.openlinksw.com/wiki/main/OdbcRails/RailsAdapterWebNotesSybase

Since this still a brand-new setup for me, I'm figuring out the best way to handle this for my application. In the end, I may just decide to patch Rails-ODBC to test field-types and maybe figure out how to rework things at that layer. We'll see.

Ruby-DBI

Build and Compile Ruby-DBI

• wget http://rubyforge.org/frs/download.php/12368/dbi-0.1.1.tar.gz
• tar -xvzpf dbi-0.1.1.tar.gz
• cd ruby-dbi
• sudo ruby setup.rb config --with=dbi,dbd_odbc
• sudo ruby setup.rb setup
• sudo ruby setup.rb install

Update config/database.yml

Modify your database.yml to something similar to this:

development:
  adapter: sqlserver
  mode: odbc
  dsn: SQL2005DSN1
  username: MyUserID
  password: MyPassword

Various Plumbing and Best-Practices

(More to Come)

Additional References

This article could not have been put together without referring to older documentation:

• How To Connect To MS SQL Server From Rails On Linux
• RubyODBC Module HOWTO
• OdbcRails Wiki

Contacting The Author

If you need additional Ruby on Fedora support, or are looking to hire an experienced Ruby On Rails Developer, feel free to contact me (Laurence Lee) as username lalee_net, at yahoo.com.

User Comments and Feedback

Comment by juanvidal2@hotmail.com on Mon May 28 10:56:51 2007

HI.. i need to do this connection but I wanna ask U if U want to send me a copy of one of your programs who reallly works...TKS

Comment by lalee on Mon May 28 15:52:24 2007

Hi juanvidal2,

I wish I could provide you with the source code that uses SQL2005 as the back-end, but it was done as a work-for-hire. All I can really say is that it's an ETL application that uses two databases concurrently, and that the company I wrote it for is reselling it in the Insurance/Finance sector.

The above instructions work OK -- about the only gotchas I remember are in making sure the SQL2005 databases have ANSI-Nulls enabled. (Found in the properties tab when you right-click the database in SQL2005 Manager.)

Blecch! I absolutely HATE the xdg (Well-Known Folders) feature because it pollutes the Home Directory with directories I don't use. Furthermore, these are auto-created upon each GUI-Login, so you can't just delete them. The configuration-file adjustment below prevents Fedora from auto-creating the following nuisance subdirectories:

• Documents
• Download
• Music
• Pictures
• Public
• Templates
• Videos