| sioc:content
| - ---+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]],
<[[mailto:iodbc@openlinksw.com][iodbc@openlinksw.com]]>, that is
iodbc at openlinksw.com.
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
* sudo apt-get install libiodbc2 iodbc
* sudo emerge libiodbc
* sudo yum install iodbc
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 gzip -cd libiodbc-3.52.7.tar.gz| tar xvf -
* ./configure --prefix=/usr/local/iODBC/
* make
* become root and run make install, e.g., su root -c 'make install'
Of course you can also build the same way on linux; the ./configure
script takes a few options you might want to vary, notably --with-prefix=
for where the files will be installed and --enable-gui 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
* cd mac
* make
* sudo make install
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 (iodbcadm-gtk on linux/unix or
iODBC Administrator.app (/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 odbc.ini (/etc/odbc.ini, ~/.odbc.ini, or $ODBCINI environment variable)
to contain something similar to the following:
[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
From the commandline you should be able to run iodbctest DSN=pgdata and it should attempt to connect:
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>
The SQL> prompt there shows iodbctest has connected and is now
awaiting a SQL command as input; additionally it understands the command
`tables' 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
DSN=somedsn;[param=value[;]]*
where the parameters are specific to the ODBC driver being used. In this case,
the 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: sudo apt-get install ruby ruby-dev
* Gentoo: sudo emerge -av ruby
---++++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 ./configure,
make and sudo make install.
---+++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
zsh, ubuntu ruby/ % sudo apt-get install libodbc-ruby1.8
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:
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/ %
To configure it, run
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
*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 -liodbc,
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:
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
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:
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)
On Linux/Unix:
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)
---++++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:
* odbc-metadata.rb
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]
---+++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::<something> 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.
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...
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
sudo apt-get install libdbd-odbc-ruby1.8
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 & DBD::ODBC from source.
---+++++DBI
Download the ruby-dbi module from [[http://ruby-dbi.rubyforge.org/][ruby-forge]]
and unpack it:
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/
Then run the following 3 commands to build and install it:
* ruby setup.rb config
* ruby setup.rb setup
* sudo ruby setup.rb install
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/
...
---+++++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]]:
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/
Then run the following 3 commands to build and install it:
* ruby setup.rb config
* ruby setup.rb setup
* sudo ruby setup.rb install
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
...
---+++++Testing Ruby, DBI, ODBC
The attached script is a quick demonstration of some of DBI's capabilities,
going via the ODBC driver.
* odbc-test-dbd.rb
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"]
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.
|
![[cxml]](/fct/images/cxml_doc.png)
![[csv]](/fct/images/csv_doc.png)
RDF
![[text]](/fct/images/ntriples_doc.png)
![[turtle]](/fct/images/n3turtle_doc.png)
![[ld+json]](/fct/images/jsonld_doc.png)
![[rdf+json]](/fct/images/json_doc.png)
![[rdf+xml]](/fct/images/xml_doc.png)
ODATA
![[atom+xml]](/fct/images/atom_doc.png)
![[html]](/fct/images/html_doc.png)
About
![[RDF Data]](/fct/images/sw-rdf-blue.png)
