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
MS SQLServer
mysql connector/odbc
oracle instant client with ODBC


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


  yum install unixODBC
  yum install unixODBC-devel

  tar tzvf freetds-stable.tgz
  cd freetds-(version)
  ./configure --with-unixodbc=/usr/local
  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 and unixODBC (last tested is 2.2.12), so install instant client version rather than the latest. More details here.

Download the basic client and the ODBC add-on:
  • oracle-instantclient-basic-

Install the instant client, things should be put under /usr/lib/oracle/
  rpm -iv oracle-instantclient-basic-

Unzip the ODBC add-on, and copy to the instant client library:
  cp instantclient_10_2/ /usr/lib/oracle/

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/ 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/ and rerun ldconfig.

So create a file for oracle and update the ld cache:
  echo /usr/lib/oracle/ > /etc/
  ldconfig -v


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 install

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.


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.

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.