From: Antonio Cangiano <acangiano@...>
Date: 2008-04-26T15:30:03+09:00
Subject: Re: DBI:ibm_db connection for IBM DB2 database

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Nicholas Quaine wrote:
> Hello,
> 
> I am attempting to make a DBI connection from Ruby code running on Win32
> (XP) machine to an IBM DB2 v9.5 database running under Linux
> 
> On the Win32 machine, I have Ruby 1.8.6 (installed with the one-click
> installer) and have successfully launched the following gem installation
> commands for the IBM DB drivers and required libraries:
> 
>> gem install activesupport -v 1.4.2
>> gem install activerecord -v 1.15.3
>> gem install ibm_db -v 0.8.5

Hi Nicholas,

please note that there are newer, improved versions for all three gems
mentioned above. The new ones are recommended, but utilizing the
versions that you chose shouldn't affect your results in most cases.

> I have also (as required by the IBM documentation at
> http://rubyforge.org/docman/view.php/2361/967/IBM_DB-0.6.0.pdf) added
> ‘ibm_db’ to the list of connection adapters on line77 of
> gems\1.8\gems\activerecord-1.15.3\lib\active_record.rb :
> 
> RAILS_CONNECTION_ADAPTERS = %w( mysql postgresql sqlite firebird
> sqlserver db2 oracle sybase openbase frontbase ibm_db )

This step was formerly required in order to inform ActiveRecord about
the existence of an ibm_db adapter. With newer versions of ActiveRecord
this is no longer a requirement. In either case, this was necessary only
for using ActiveRecord with the ibm_db adapter and not a requirement for
the ibm_db driver, contained in the gem too, to work.

> Here is the ruby code :
> 
> -------------
> 
> require 'rubygems'
> gem 'ibm_db'
> 
> require 'dbi'
> 
> # Connect to a database
> dbh =
> DBI.connect('DBI:ibm_db:host=localhost;port=50000;database=MY_DB;',
> 'username', 'password')
> 
> # Select some data
> sth = dbh.prepare('select * from my_table')
> sth.execute
> 
> # Print out selected data
> while row=sth.fetch do
>   p row
> end
> 
> ---------------
> 
> Error reported is as follows :
> 
> C:\dev\ruby\dbtest>dbtest.rb
> c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:329:in `load_driver': Unable to
> load drive
> r 'ibm_db' (DBI::InterfaceError)
>         from c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:227:in
> `_get_full_driver'
>         from c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:213:in `connect'
>         from C:/dev/ruby/dbtest/dbtest.rb:4

Ruby-DBI doesn't have an ibm_db interface. It has its own DB2 driver,
that is not created nor supported by IBM, and it hasn't received an
update in more than 2 years. I wouldn't recommend going for the DBI route.

The IBM_DB Ruby driver is "high level enough" to let you interact with
DB2  with ease. I prepared a guide to get people started, and you can
find it here: http://rubyurl.com/a0tS.

Translating your snippet of code is very straightforward:

require 'rubygems'
require 'mswin32/ibm_db'

dbh = IBM_DB::connect("DRIVER={IBM DB2 ODBC DRIVER};DATABASE=MY_DB;\
                       HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;\
                       UID=username;PWD=password;", "", "")

sth = IBM_DB::exec(dbh, 'select * from my_table')

while row = IBM_DB::fetch_row(sth) do
  p row
end

This is the version without a prepared statement, but following my guide
you can change it by using the prepare and execute methods if you wish.

Please let me know if you have further questions.

Cheers,
Antonio
- --
http://antoniocangiano.com - Zen and the Art of Programming
http://stacktrace.it - Aperiodico di resistenza informatica
http://math-blog.com - Math Blog: Mathematics is wonderful!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkgSyucACgkQqCqsu0qUj9QztQCghYpPag20Re01Lashv8GT6qWh
130AniEBy7h9hBw1m7kGtBDdeNTdafso
=0Uk7
-----END PGP SIGNATURE-----