This page describes the ingredients needed for a Ruby DBI program on CentOS 5 Linux to access databases via unixODBC.

general concept


The database access model for the program:
ruby program
\_ DBI (ruby-dbi)
  \_ DBD::ODBC (requires ruby-odbc)
    \_ unixODBC datasource manager
      \_ ODBC driver
        \_ database
ODBC driver
database
FreeTDS
MS SQLServer
mysql connector/odbc
MySQL
oracle instant client with ODBC
Oracle

installation


Do the following (some require root privilege) to install the needed components:

unixODBC


  yum install unixODBC
  yum install unixODBC-devel

freetds


http://www.freetds.org/
  tar tzvf freetds-stable.tgz
  cd freetds-(version)
  ./configure --with-unixodbc=/usr/local
  make
  make install

mysql connector/odbc


  yum install mysql-connector-odbc

oracle instant client with ODBC


As of April 2008, Oracle has released Linux instant clients for 10g and 11g, they can be downloaded from oracle's website.

note there is an incompatibility between oracle instant client versions greater than 10.2.0.3 and unixODBC (last tested is 2.2.12), so install instant client version 10.2.0.3 rather than the latest. More details here.

Download the basic client and the ODBC add-on:
  • oracle-instantclient-basic-10.2.0.3-1.i386.rpm
  • instantclient-odbc-linux32-10.2.0.3-20061115.zip

Install the instant client, things should be put under /usr/lib/oracle/10.2.0.3/client:
  rpm -iv oracle-instantclient-basic-10.2.0.3-1.i386.rpm

Unzip the ODBC add-on, and copy libsqora.so.10.1 to the instant client library:
  unzip instantclient-odbc-linux32-10.2.0.3-20061115.zip
  cp instantclient_10_2/libsqora.so.10.1 /usr/lib/oracle/10.2.0.3/client/lib/

Oracle clients 10g and above support the Easy Connect naming method for remote connections, bypassing the need for a tnsnames.ora file. See the oracle doc for more info.

The LD_LIBRARY_PATH environment variable needs to be set (in this case, to /usr/lib/oracle/10.2.0.3/client/lib) and exported to enable connections via isql, and for any ruby scripts that are run afterwards from the shell. But if we attempt to set this variable directly in a ruby script (e.g. ENV['LD_LIBRARY_PATH'] = '/x/y/x') it doesn't work. Setting DMEnvAttr in the odbc.ini file as documented doesn't seem to work either. Fortunately the following suggestion does work:

The ODBC driver defined by DRIVER=xxx in the odbcinst.ini file depends on other shared objects which are not on your dynamic linker search path. Run ldd on the driver shared object named by Driver= in the odbcinst.ini file and see what dependent shared objects cannot be found. If some cannot be found than you need to defined your LD_LIBRARY_PATH environment variable to define the paths to any dependent shared objects or add these paths to /etc/ld.so.conf and rerun ldconfig.

So create a ld.so.conf file for oracle and update the ld cache:
  echo /usr/lib/oracle/10.2.0.3/client/lib > /etc/ld.so.conf.d/oracle.conf
  ldconfig -v

ruby-odbc


http://raa.ruby-lang.org/project/ruby-odbc/

note the following installs the non-utf8 version
gunzip ruby-odbc-(version).tar.gz
tar xvf ruby-odbc-(version).tar
cd ruby-odbc-(version)
ruby extconf.rb
make
make install

ruby-dbi


http://rubyforge.org/projects/ruby-dbi/
tar xvf dbi-(version).tar
cd ruby-dbi
ruby setup.rb config --with=dbi,dbd_odbc
ruby setup.rb setup
ruby setup.rb install

notes on odbcinst


odbcinst is the command line tool provided by unixODBC to manage drivers and data source names (DSNs).

The ODBCINI environment variable must be set for deletes to work. Adding and deleting DSNs work well, deleting non-existent does not raise error, and adding duplicates in succession replaces the previous.

configuration


ODBC connections can be configured with just odbcinst.ini and odbc.ini, or even just odbc.ini. It is not necessary to set up freetds.conf.

The odbc collector set up uses odbcinst.ini to configure drivers, and odbc.ini to configure DSNs.

Below are some sample template files to set up drivers and data sources. Driver templates are named *.driver.tpl, DSNs templates are named *.dsn.tpl.





Do the following (as root if needed) to set up drivers and DSNs using template files.

Drivers:
odbcinst -i -d -f /path/to/driver/template

Data sources:
odbcinst -i -s -l -f /path/to/dsn/template

You can look at /etc/odbcinst.ini and /etc/odbc.ini to check that the drivers and DSNs are installed, and use isql to test the connection.