---+iODBC and Ruby

%TOC%

---++Abstract

This HOWTO is intended to walk you through the process of installing and 
configuring iODBC, Ruby and the Ruby/ODBC bridge module with a goal of 
writing and executing simple scripts to effect a database connection. It is 
currently maintained by Tim Haynes of [[http://www.openlinksw.com/][OpenLink Software]],
&lt;[[mailto:iodbc@openlinksw.com][iodbc@openlinksw.com]]&gt;, that is <code>iodbc</code> at <code>openlinksw.com</code>. 

We assume you have some familiarity with using either Terminal on Apple 
Mac OS X or the shell on a GNU/Linux platform such as Ubuntu, Debian, 
RedHat, or Fedora Core, etc.

---++Required Components

 ---+++iODBC Driver Manager
 
 We start by installing the iODBC Driver Manager.
 
 ---++++Linux
 
 Some Linux distributions (Debian, Ubuntu, Gentoo) have their own packages 
 for iODBC, so you should only need to type a command such as one of

   * <code>sudo apt-get install libiodbc2 iodbc</code>
   * <code>sudo emerge libiodbc</code>
   * <code>sudo yum install iodbc</code>
    
 to install it, and possibly some dependencies (GTK+ libraries for the 
 adminstrator utility), automatically.
 
 ---++++ Mac OS X
 
 For Mac OS X users, we provide a DMG installer on our [[Downloads][Downloads]]
 page. This will install the iODBC Framework, development environment and 
 graphical configuration utility.
 
 Note that this supersedes the version of iODBC supplied by Apple with Mac OS X,
 as it resolves two bugs:
   * on 64-bit machines, ruby would be built in 64-bit mode by default but 
    the system libiodbc is only 32-bit, making building the ruby-odbc bridge
    impossible;   
   * the handling of SQLError() in ruby-odbc. 
 
 ---++++Other Unix operating systems
 
 On other unix operating systems, you need to compile iODBC from source. This
 is generally easy:

   * [[Downloads][Download]] the iODBC source tarball
   * unpack it using a command such as <code>gzip -cd libiodbc-3.52.7.tar.gz| tar xvf -</code>
   * <code>./configure --prefix=/usr/local/iODBC/</code>
   * <code>make</code>
   * become root and run <code>make install</code>, e.g., <code>su root -c 'make install'</code>
 
 Of course you can also build the same way on linux; the <code>./configure</code>
 script takes a few options you might want to vary, notably <code>--with-prefix=</code>
 for where the files will be installed and <code>--enable-gui</code> for whether
 to build the GTK+ Administrator or not. 
 
 If you wish to build from source on Mac OS X, after unpacking the archive, run
   * <code>cd mac</code>
   * <code>make</code>
   * <code>sudo make install</code>

This will build the Framework version and create a subdirectory under 
/usr/local/iODBC/ so that other applications (eg PHP, Perl, Ruby) can link
against it without having to modify them to look for frameworks.

---++++Configuring and Testing iODBC

It is wise to test that iODBC works directly before trying to layer any Ruby 
ODBC module on top. Either:

   * use the graphical Administrator application (<b>iodbcadm-gtk</b> on linux/unix or
   <b>iODBC Administrator.app</b> (/Applications/iODBC/) on Mac OS X) to register an
   ODBC driver, add a System or User DSN using the driver, and test it, or
   * edit your own <code>odbc.ini</code> (<code>/etc/odbc.ini</code>, <code>~/.odbc.ini</code>, or <code>$ODBCINI</code> environment variable) 
   to contain something similar to the following:
   
<verbatim>
[ODBC Data Sources]
pgdata = Native PostgreSQL ODBC driver

[pgdata]
Driver=/usr/lib/odbc/psqlodbcw.so
Host       = data
Server     = data
ServerName = data
Database   = me
UserName   = me
UID        = me
Port       = 5432
</verbatim>
   
From the commandline you should be able to run <code>iodbctest DSN=pgdata</code> and it should attempt to connect:
   
<verbatim>
zsh, ubuntu libiodbc-3.52.7/ % iodbctest DSN=pgdata 
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0709.0909
Driver: 08.03.0200 (psqlodbcw.so)

SQL>
</verbatim>   

The <code>SQL&gt;</code> prompt there shows iodbctest has connected and is now
awaiting a SQL command as input; additionally it understands the command
`<code>tables</code>' which lists tables visible in the current database. 

Note the `DSN=pgdata' parameter: this is part of an ODBC connection string,
not just a data-source name. As such it takes the form

<verbatim>
DSN=somedsn;[param=value[;]]*
</verbatim>

where the parameters are specific to the ODBC driver being used. In this case,
the <nop>PostgreSQL native ODBC driver requires Host, Database and Port, above. 
For MySQL and OpenLink drivers, the parameters vary.

The [[FAQ][iODBC FAQ]] contains a section of common error messages in the 
event of something going wrong at this stage.

---+++Ruby

Ruby is an open-source, interpreted, dynamic, object-oriented scripting 
language in the same space as Perl, Python on the desktop and with the famous
Ruby-on-Rails engine in web-space (to compare with PHP).

There are two ways in which Ruby interfaces with ODBC: a ruby-odbc binding
module which presents the ODBC API at the Ruby layer with little abstraction,
maintained by Christian Werner; and a DBD::ODBC module that provides a driver
compatible with DBI (a database-interaction specification familiar to Perl
users, now implemented in Ruby). The DBD::ODBC driver depends upon the 
ruby-odbc binding, so we continue building the packages in hierarchical order.

---++++Linux

Most Linux distributions have packages for 
[[http://www.ruby-lang.org/en/][Ruby]] already. Remember if your distribution
distinguishes between runtime and development versions packages, you will need
to install the development libraries in order to compile other packages from 
source against them.
   * Debian/Ubuntu: <code>sudo apt-get install ruby ruby-dev</code>
   * Gentoo: <code>sudo emerge -av ruby</code>

---++++Mac OS X

Mac OS X, from Tiger onwards, comes with Ruby 1.8 by default. No further
installation is required, unless you really want to build it from source
yourself.

---++++From Source

For other unix environments, it might be required to build Ruby from source.
That process is not documented here except to say that it follows a similar 
path to iODBC from source: download, unpack, run <code>./configure</code>, 
<code>make</code> and <code>sudo make install</code>.

---+++Ruby/ODBC Binding

Christian Werner maintains the 
[[http://ch-werner.de/rubyodbc/][ruby-odbc binding module]].

---++++Linux

Some GNU/Linux distributions (debian and ubuntu in particular) have already
packaged the ruby-odbc binding, so a simple

<verbatim>
zsh, ubuntu ruby/ % sudo apt-get install libodbc-ruby1.8 
</verbatim>

is all that's required.

However, for better handling of SQLError(), we recommend you use the latest 
iODBC (3.52.7) and ruby-odbc module (0.9997) so you might still need to build
from source, as follows.

---++++ Mac OS X, other Unix platforms: build from source  

The following instructions apply equally to Linux, Mac OS X, and other Unix-like operating
systems, as we're building from source here.

Download this and unpack it as usual:

<verbatim>
zsh, pizza C/ % tar xvfz ruby-odbc-0.9997.tar.gz
./ruby-odbc-0.9997/
./ruby-odbc-0.9997/doc/
./ruby-odbc-0.9997/doc/odbc.html
./ruby-odbc-0.9997/test/
...
zsh, pizza C/ % cd ruby-odbc-0.9997 
zsh, pizza ruby-odbc-0.9997/ % 
</verbatim>

To configure it, run
<verbatim>
zsh, pizza ruby-odbc-0.9997/ % ruby extconf.rb --with-odbc-dir=/usr/local/iODBC
checking for version.h... yes
checking for sql.h... yes
checking for sqlext.h... yes
checking for SQLTCHAR in sqltypes.h... yes
checking for SQLLEN in sqltypes.h... yes
checking for SQLULEN in sqltypes.h... yes
checking for odbcinst.h... yes
checking for SQLAllocConnect() in -liodbc... yes
</verbatim>

*Note* three things: 
   * first, we tell it where to find the iODBC libraries using --with-odbc-dir 
   (which works well with Mac OS X, where ordinarily libraries are provided 
   using frameworks, so to avoid needing to use those, the iODBC installer 
   package provides a /usr/local/iODBC/ directory with symbolic links into the
   Frameworks directory); 
   * secondly, it must report successful checking using <code>-liodbc</code>, 
   not -lodbc - unless you've symlinked the two libraries together;
   * thirdly, you need iODBC version 3.52.7 and ruby-odbc version 0.9997 to
   fix a bug in the handling of SQLError()  

We continue to build and install it:

<verbatim>
zsh, pizza ruby-odbc-0.9997/ % make
/usr/bin/gcc-4.0 -I. -I. -I/usr/local/ruby/1.8/powerpc-darwin8 -I. \
	-DHAVE_VERSION_H -DHAVE_SQL_H -DHAVE_SQLEXT_H -DHAVE_TYPE_SQLTCHAR \
	-DHAVE_TYPE_SQLLEN -DHAVE_TYPE_SQLULEN -DHAVE_ODBCINST_H \ 
	-DHAVE_SQLINSTALLERERROR -I/usr/local/iODBC/include -I/opt/local/include \ 
	-D_XOPEN_SOURCE -D_DARWIN_C_SOURCE  -I/opt/local/include -fno-common \
	-O2  -fno-common -pipe -fno-common   \
	-c init.c
...
zsh, pizza ruby-odbc-0.9997/ % sudo make install
Password:
/usr/bin/install -m 0755 odbc.bundle /usr/local/ruby/site_ruby/1.8/powerpc-darwin8
</verbatim>

To check that it built correctly, run one of the following commands to 
see the shared library dependencies; there should be a mention of `iodbc' somewhere
in the output:

On Mac OS X:
<verbatim>
zsh, pizza ruby-odbc-0.9997/ % otool -L /usr/local/ruby/site_ruby/1.8/powerpc-darwin8/odbc.bundle 
/usr/local/ruby/site_ruby/1.8/powerpc-darwin8/odbc.bundle:
        /usr/local/lib/libruby.dylib (compatibility version 1.8.0, current version 1.8.7)
        /usr/local/lib/libiodbcinst.1.dylib (compatibility version 2.0.0, current version 2.0.0)
        /usr/local/lib/libiodbc.1.dylib (compatibility version 2.0.0, current version 2.0.0)
</verbatim>

On Linux/Unix:

<verbatim>
zsh, ubuntu ruby-odbc-0.9997/ % ldd /usr/lib/ruby/site_ruby/1.8/i486-linux/odbc.so 
[...]
        libc.so.6 => /lib/libc.so.6 (0xb7d0e000)
        libiodbc.so.2 => /usr/local/lib/libiodbc.so.2 (0xb7cc6000)
        libiodbcinst.so.2 => /usr/local/lib/libiodbcinst.so.2 (0xb7cb3000)
        /lib/ld-linux.so.2 (0xb7fd2000)
</verbatim>
 

---++++Testing

Having installed the ruby-odbc binding module, you can write a simple ruby script
to test it such as the attached which makes a connection and probes some 
metadata available through the ODBC connection:

   * <a href="%ATTACHURLPATH%/odbc-metadata.rb" style="wikiautogen">odbc-metadata.rb</a>

<verbatim>
zsh, ubuntu ruby/ % ./odbc-metadata.rb pgdata "" ""
Connecting to [pgdata, , ]
Connected: true

Data-types:

Type integer:
Native: int4
  TYPE_NAME:           int4
  DATA_TYPE:           4
  PRECISION:           10
  LITERAL_PREFIX:      
  LITERAL_SUFFIX:      
  CREATE_PARAMS:       
  NULLABLE:            1
  CASE_SENSITIVE:      0
  SEARCHABLE:          2
  UNSIGNED_ATTRIBUTE:  0
  MONEY:               0
  AUTO_INCREMENT:      0
  LOCAL_TYPE_NAME:     
  MINIMUM_SCALE:       0
  MAXIMUM_SCALE:       0
  SQL_DATA_TYPE:       4
  SQL_DATETIME_SUB:    
  NUM_PREC_RADIX:      10
  INTERVAL_PRECISION:  0
...

Trying type SQL_CURSOR_ROLLBACK_BEHAVIOR (String): ret=[2]

Trying type SQL_DYNAMIC_CURSOR_ATTRIBUTES1 (String): ret=[0]

Trying type SQL_KEYWORDS (String): ret=[]

Trying type SQL_LIKE_ESCAPE_CLAUSE (String): ret=[N]

Trying type SQL_COLUMN_ALIAS (String): ret=[Y]

Trying type SQL_ORDER_BY_COLUMNS_IN_SELECT (String): ret=[N]
</verbatim>


---+++Ruby/DBI DBD::ODBC Driver

The second method to connect Ruby to ODBC is using the DBI interface; this 
provides a higher-level interface, consisting of an controlling module (DBI)
that invokes drivers for specific databases (DBD::&lt;something&gt; modules)
 as required, one of which is DBD::ODBC.

---++++Cross-platform: Ruby GEM

Ruby has its own packaging system, known as rubygems, which includes the dbi 
module. 

<verbatim>
zsh, pizza C/ % sudo gem install dbi dbd-odbc  --remote
Password:
Successfully installed dbi-0.4.3
Successfully installed dbd-odbc-0.2.5
2 gems installed
Installing ri documentation for dbi-0.4.3...
Installing ri documentation for dbd-odbc-0.2.5...
Installing RDoc documentation for dbi-0.4.3...
Installing RDoc documentation for dbd-odbc-0.2.5...
</verbatim>

If you are using gems for maintaining ruby packages, no further installation
is required after this.
 
---++++Linux Packages

As before, some GNU/Linux distributions (Debian and Ubuntu) already have packages
for Ruby's DBI and DBD modules, so a simple

<verbatim>
sudo apt-get install libdbd-odbc-ruby1.8 
</verbatim>

suffices, and will include the DBI module as a dependency too.


---++++ Mac OS X and other Unix-like systems: building from source

On other platforms, if not using the gems above, you have the option to 
build DBI &amp; DBD::ODBC from source.

---+++++DBI

Download the ruby-dbi module from [[http://ruby-dbi.rubyforge.org/][ruby-forge]]
and unpack it:

<verbatim>
zsh, ubuntu C/ % tar xvfz dbi-0.4.3.tar.gz
dbi-0.4.3/
dbi-0.4.3/build/
dbi-0.4.3/build/rake_task_lib.rb
dbi-0.4.3/build/Rakefile.dbi.rb
dbi-0.4.3/Rakefile
...
zsh, ubuntu C/ % cd dbi-0.4.3 
zsh, ubuntu dbi-0.4.3/ % ls
bin/    ChangeLog  lib/     Rakefile  setup.rb
build/  examples/  LICENSE  README    test/
</verbatim>

Then run the following 3 commands to build and install it:

   * <code>ruby setup.rb config</code>
   * <code>ruby setup.rb setup</code>
   * <code>sudo ruby setup.rb install</code>

<verbatim>
zsh, ubuntu dbi-0.4.3/ % ruby setup.rb config
---> bin
<--- bin
---> lib
---> lib/dbi
---> lib/dbi/utils
<--- lib/dbi/utils
---> lib/dbi/handles
<--- lib/dbi/handles
---> lib/dbi/base_classes
<--- lib/dbi/base_classes
---> lib/dbi/sql
<--- lib/dbi/sql
<--- lib/dbi
<--- lib

zsh, ubuntu dbi-0.4.3/ % ruby setup.rb setup 
---> bin
updating shebang: test_broken_dbi
updating shebang: dbi
<--- bin
---> lib
---> lib/dbi
---> lib/dbi/utils
<--- lib/dbi/utils
---> lib/dbi/handles
<--- lib/dbi/handles
---> lib/dbi/base_classes
<--- lib/dbi/base_classes
---> lib/dbi/sql
<--- lib/dbi/sql
<--- lib/dbi
<--- lib

zsh, ubuntu dbi-0.4.3/ % sudo ruby setup.rb install
rm -f InstalledFiles
---> bin
mkdir -p /usr/local/stow/ruby/bin
install test_broken_dbi /usr/local/stow/ruby/bin/
install dbi /usr/local/stow/ruby/bin/
<--- bin
---> lib
mkdir -p /usr/local/stow/ruby/lib/ruby/site_ruby/1.9.1
install dbi.rb /usr/local/stow/ruby/lib/ruby/site_ruby/1.9.1/
...
</verbatim>

---+++++DBD::ODBC

The installation of ruby dbd-odbc follows a similar path to the dbi module.

Download and unpack dbd-odbc from [[http://rubyforge.org/frs/?group_id=234&release_id=36729][ruby-forge]]:

<verbatim>
zsh, ubuntu C/ % tar xvfz dbd-odbc-0.2.5.tar.gz 
dbd-odbc-0.2.5/
dbd-odbc-0.2.5/ChangeLog
dbd-odbc-0.2.5/README
dbd-odbc-0.2.5/lib/
dbd-odbc-0.2.5/lib/dbd/
dbd-odbc-0.2.5/lib/dbd/odbc/
zsh, ubuntu dbd-odbc-0.2.5/ % ls
build/  ChangeLog  lib/  LICENSE  Rakefile  README  setup.rb  test/
</verbatim>


Then run the following 3 commands to build and install it:

   * <code>ruby setup.rb config</code>
   * <code>ruby setup.rb setup</code>
   * <code>sudo ruby setup.rb install</code>
   
<verbatim>
zsh, ubuntu dbd-odbc-0.2.5/ % ruby setup.rb config
---> lib
---> lib/dbd
---> lib/dbd/odbc
<--- lib/dbd/odbc
<--- lib/dbd
<--- lib

zsh, ubuntu dbd-odbc-0.2.5/ % ruby setup.rb setup 
---> lib
---> lib/dbd
---> lib/dbd/odbc
<--- lib/dbd/odbc
<--- lib/dbd
<--- lib

zsh, ubuntu dbd-odbc-0.2.5/ % sudo ruby setup.rb install
rm -f InstalledFiles
---> lib
mkdir -p /usr/local/stow/ruby/lib/ruby/site_ruby/1.9.1
---> lib/dbd
mkdir -p /usr/local/stow/ruby/lib/ruby/site_ruby/1.9.1/dbd
install ODBC.rb /usr/local/stow/ruby/lib/ruby/site_ruby/1.9.1/dbd
---> lib/dbd/odbc
... 
</verbatim>

---+++++Testing Ruby, DBI, ODBC

The attached script is a quick demonstration of some of DBI's capabilities,
going via the ODBC driver.

   * <a href="%ATTACHURLPATH%/odbc-test-dbd.rb" style="wikiautogen">odbc-test-dbd.rb</a>

<verbatim>
zsh, ubuntu C/ % ./odbc-test-dbd.rb pgdata "" ""
[1, "This is a varchar 10"]
[2, "This is a varchar 40"]
[3, "This is a varchar 90"]
[4, "This is a varchar 160"]
[5, "This is a varchar 250"]
[6, "This is a varchar 360"]
[7, "This is a varchar 490"]
[8, "This is a varchar 640"]
[9, "This is a varchar 810"]
[10, "This is a varchar 1000"]
</verbatim>

First the script connects; then it drops and recreates a table called `test';
then it inserts 10 rows and selects them back, before closing off the 
statement and connection handles.