Satya's blog - 2010/02/

Getting Rails to work with Oracle is straight-forward. Get the instant client libraries from Oracle. I got the following, and unzipped them into /opt/oracle/instantclient-VERSION:

instantclient-basic-linux32-10.2.0.3-20061115.zip
instantclient-sdk-linux32-10.2.0.3-20061115.zip
instantclient-sqlplus-linux32-10.2.0.3-20061115.zip

for n in instantclient*.zip; do unzip $n; done

Symlink the directory so that we don't have to refer to it by version number, and then symlink certain files inside it because the libraries are supposed to be generically-named:

ln -s instantclient-VERSION instantclient
cd instantclient
ln -s libclntsh.so.11.1 libclntsh.so
ln -s libocci.so.11.1 libocci.so

In /etc/apache2/envvars add:

export LD_LIBRARY_PATH=/opt/oracle/instantclient:$LD_LIBRARY_PATH

And then install the ruby-oci8 driver, and the activerecord_oracle_adapter:

sudo env LD_LIBRARY_PATH=$LD_LIBRARY_PATH /usr/bin/gem install ruby-oci8 --version "< 2.0.0"
sudo gem install activerecord-oracle-adapter

Here's an example config/database.yml file:

production:
  adapter: oracle
  database: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=foo.example.com)(PORT=1234))(CONNECT_DATA=(SID=databse)))
  username: foo
  password: boo

There, all done. There might be a way to do this using ODBC but I'm just happy this works pain-free (unlike sybase).

I've been trying to get Sybase working with Rails, as detailed before in my blog. Here's what eventually worked for me, on Ubuntu 9.10 and Rails 2.3.5, using Phusion Passenger.

Install the rubygems package. Install rails and stuff via gem, and some ruby libraries via apt-get. Note: We apt-get install rubygems, and all the other rails stuff is installed as a gem. Because we wanted latest stuff. *Ruby* libraries are apt-get installed where possible, otherwise via gem. freetds and sybct libraries are compiled on the box. Yuck.

The following gems are installed (I've taken out ones, like calendar_date_select, that are obviously irrelevant for this purpose):

activerecord (2.3.5, 2.3.4, 2.3.3, 2.3.2, 1.15.6)
activerecord-jdbc-adapter (0.9.2, 0.9.1)
activerecord-odbc-adapter (2.0)
activerecord-oracle-adapter (1.0.0.9250)
activerecord-oracle_enhanced-adapter (1.2.3)
activerecord-sybase-adapter (1.0.0.9250)
activeresource (2.3.5, 2.3.4, 2.3.3, 2.3.2)
activesupport (2.3.5, 2.3.4, 2.3.3, 2.3.2, 1.4.4)
dbd-odbc (0.2.5)
dbi (0.4.3)
deprecated (2.0.1)
fastthread (1.0.7)
rack (1.1.0, 1.0.1, 1.0.0)
rails (2.3.5, 2.3.4, 2.3.3, 2.3.2)
rake (0.8.7)
ruby-oci8 (1.0.7)
ruby-odbc (0.9999)
rubygems-update (1.3.5)

I'd gem install rails, dbd-odbc, and rubygems-update. That should pull in most of the others as dependencies. Don't install ruby-odbc and ruby-oci8 right off. For ruby-odbc, see below, and for ruby-oci8, See another article (to be written). The oci8 is for Oracle.

Additional stuff to install (again, only relevant stuff listed):

Your /etc/freetds/freetds.conf file (not sure you need this for ODBC):

[A]
        host = somehost1
        port = 4100
        tds version = 5.0
[D]
        host = somehost2
        port = 4100
        tds version = 5.0

Begin activerecord-sybase-adapter instructions. If you want to use ODBC only, skip this section.

At some point, I got sybase-ctlib ruby library (sybct-ruby) from here: http://enjoy1.bb-east.ne.jp/~tetsu/sybct-ruby-0.2.12.tar.gz (sybase-ctlib project page). Also download freetds-stable, ./configure and make, and then edit sybct's extconf.rb file:

sybase = "/usr/local/"
$CFLAGS = "-g -Wall -DFREETDS -I#{sybase}/include"
$LDFLAGS = " -L#{sybase}/lib -L/freetds-0.82/src/tds/.libs" 
$LOCAL_LIBS = "-lct  -lsybdb -ltds -rdynamic -ldl -lnsl -lm"

Note the ldflags line — you have to tell it where libtds is located. You can find freetdsdirectory -name "libtds*" to find the libtds location. Then in the sybase-ctlib directory, run:

ruby extconf.rb
make
sudo cp sybct.rb sybct.so sybsql.rb /usr/local/lib/site_ruby/1.8/i486-linux/

Fix this destination according to your system. The command "make install" tries to put it in the right place, but doesn't copy all three files.

At this point you should be able to use the native sybase adapter in Rails. Your config/database.xml:

my_db:
  adapter: sybase
  host: a
  database: whatever
  username: heh
  password: youwish

End activerecord-sybase-adapter instructions. If you want to use ODBC only, skip the above section.

For the ODBC connection, I stuck this in ~/odbc and ran "odbcinst -i -s -d ~/odbc" (I have tdsodbc and unixodbc, dunno how much of that is required). Actually, I just stuck the following stuff in /etc/odbc.ini:

[d]
Description     = d server, name elided
Driver      = /usr/lib/odbc/libtdsodbc.so
Server=fqdn.example.com
Port=4100
TDS Version      = 5.0
[a]
Description     = a server, name elided
Driver      = /usr/lib/odbc/libtdsodbc.so
Server=fqdn2.example.com
Port=4100
TDS Version      = 5.0

Test with this command: isql -v d user pass where d is the 'd' from the odbc file, and user and pass are the actual ones.

For ODBC with Rails: Per instructions from RubyODBC maintainer (Christian Werner, whom I hereby thank again). First line removes libdbd-odbc-ruby libdbd-odbc-ruby1.8 libodbc-ruby1.8 if they were installed:

sudo apt-get purge libodbc-ruby1.8
sudo gem install ruby-odbc -- --build-flags --disable-dlopen

And that got Sybase over ODBC to work. disable-dlopen is kinda important. Important information that I was looking for but couldn't find: "-- --build-flags" is how you pass stuff to extconf.rb when the gem install command is going to compile the gem. Now you should be able to use ODBC in Rails with this config/database.yml file:

my_db:
  adapter: odbc
  dsn: a
  database: whatever
  username: heh
  password: youwish

Phew!

Update: I don't think you need the sybse-ctlib library, nor the freetds source/compile, if you're only doing the ODBC thing. You can get by with the freetds package installed by apt-get. If you're using the activerecord sybase adapter, that's when you need sybase-ctlib and therefore the freetds source.

Last updated: Mar 23 2010 14:55