Home

HOWTO: Connect RubyOnRails on Fedora Linux to SQL2005

Submitted by lalee on Mon, 05/28/2007 - 12:52
[Minor Update] use Ruby-DBI, Not Rails-ODBC!

While I'd love to use Rails-ODBC in the true "Bleeding-Edge" spirit of Fedora Core, Rails-ODBC isn't quite usable with SQL2005 at the time of this writing. If you need to use SQL2005 right now then you should try using the Ruby DBI layer until the Rails-ODBC solution matures a little more. This article documents both approaches, in anticipation of an updated Rails-ODBC that will be usable with SQL2005.

Specifically, in my experience, most SELECT queries work, but most UPDATE queries fail. Trace Logs show a properly formed query -- i.e. the query works fine if cut/pasted and run from SQL2005 Studio -- but FreeTDS reports a syntax error. When I have time, I'll dig deeper and see if I can fix it myself (or submit a detailed bug-report).. but for now, my boxes have to be up ASAP, so I'm moving forward with the Ruby-DBI solution as it seems to work better.

-- Lalee

 

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

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.

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:

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

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:

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

 
#1Submitted by ryan o (not verified) on Thu, 09/25/2008 - 07:40.

Great tutorial!!

Thanks a bunch!  This is exactly what I spent a few hours looking for yesterday.  You are a champ!