Tuesday, February 12, 2008

ibm_db and Ruby/Informix interface comparison

Antonio Cangiano, a Software Engineer at IBM, recently posted in his blog an Essential guide to the Ruby driver for DB2, which included some samples of IBM's ibm_db Ruby driver. This driver supports not only DB2, but several IBM databases, Informix included.

As Antonio states in his guide, it was not meant to be thorough (that's what the API reference is for), but it certainly helped to get an idea of how ibm_db looks at work.

Antonio explains in a comment that ibm_db's interface was taken from PHP's DB2 driver, and that same interface was used for the IBM's Ruby and Python drivers.

That helps to explain why the code examples don't look Ruby-ish at all.

Here's one of the simplest examples:



require 'rubygems'
require 'ibm_db'

if conn = IBM_DB::connect("sample", "db2inst1", "mypassword")
sql = "SELECT * FROM EMPLOYEE"
begin
if stmt = IBM_DB::exec(conn, sql)
while row = IBM_DB::fetch_assoc(stmt)
puts "#{row['FIRSTNME']} #{row['LASTNAME']}: #{row['EMPNO']}"
end
IBM_DB::free_result(stmt)
else
puts "Statement execution failed: #{IBM_DB::stmt_errormsg}"
end
ensure
IBM_DB::close(conn)
end
else
puts "Connection failed: #{IBM_DB::conn_errormsg}"
end


And here's the equivalent, written with Ruby/Informix:



require 'informix'

Informix.connect("sample", "db2inst1", "mypassword") do |conn|
sql = "SELECT * FROM EMPLOYEE"
conn.cursor(sql) do |cur|
cur.open
cur.each_hash do |row|
puts "#{row['FIRSTNME']} #{row['LASTNAME']}: #{row['EMPNO']}"
end
end
end


The difference is obvious: blocks.

Blocks are one of the nicest features of Ruby. Matz, creator of Ruby, gave an attractive look to an old concept, which made it easy and desirable to use.

You can see blocks everywhere in Ruby as iterations, for resource management, event handling or as high order functions.

Blocks are a distinctive of Ruby. But still no blocks in ibm_db.

Fortunately Informix users have an alternative.




UPDATE: It will be even more succinct using the new features that will be included in the next release but that you can already try grabbing the code from the CVS tree.




require 'informix'

Informix.connect("sample", "db2inst1", "mypassword") do |conn|
sql = "SELECT * FROM EMPLOYEE"
conn.each_hash(sql) do |row|
puts "#{row['FIRSTNME']} #{row['LASTNAME']}: #{row['EMPNO']}"
end
end

6 comments:

jonahblue said...

Hi Gerardo,
I tried connecting a new Ruby on Rails app to our legacy Informix database about a year and a half ago, and though you were kind enough to email me a few times, I could never get it working and had to move on to other things. Well, now I'm trying again, and while I'm following instructions, I'm getting a timeout after 80 seconds:

Informix::DatabaseError in KatalystController#index

Client unable to establish connection

RAILS_ROOT: script/../config/..
Application Trace | Framework Trace | Full Trace

#{RAILS_ROOT}/app/models/katalyst.rb:10:in `initialize'
#{RAILS_ROOT}/app/models/katalyst.rb:10:in `connect'
#{RAILS_ROOT}/app/models/katalyst.rb:10
app/controllers/katalyst_controller.rb:12:in `index

Is there any good way to debug this connection problem?

Thanks much,

Don Hogan
Asbury College
Information Services Department

Gerardo Santana said...

Hello Don,

Ok, according to the error message, there's a problem establishing a connection. It is usually a problem in the sqlhosts or SetNet32 configuration, or we are specifying the database name incorrectly.

For debugging this, we can do by hand what the Rails environment is doing. Take the value of :database located in your database.yml and use it as an argument to Informix.connect. Try this in irb:

require 'rubygems' # if needed
require 'informix'
Informix.connect("yourdb")

If you are using a database located in a server different from what INFORMIXSERVER says, you must use the dbname@servername notation.

You can test with dbaccess too:

dbaccesss yourdb@yourserver

If you cannot connect with dbaccess with your database exactly as you wrote it in :database, you won't be able to connect with Ruby/Informix either.

Let me know how it goes.

jonahblue said...

Thank you for your reply, Gerardo.

Your irb suggestion times out with:

irb(main):004:0> Informix.connect("kampus@katalyst")
Informix::DatabaseError:
message : Client unable to establish connection
sql_code : -908
sql_state : 08004
class_origin : ISO 9075
subclass_origin: ISO 9075

message : ISAM error: record is locked.
sql_code : 107
sql_state : IX000
class_origin : IX
subclass_origin: IX000

from (irb):4:in `initialize'
from (irb):4:in `connect'
from (irb):4

I went to /opt/informix and couldn't find dbaccess. All I have installed on my laptop (running Fedora) is:
[mgrdon@mgrdon02 bin]$ check_version csdk
Currently installed version: 2.90.UC4
[mgrdon@mgrdon02 bin]$ check_version conn
Currently installed version: 2.90.UC4

The "kampus" database is on a Red Hat network server: "solomon.asbury.edu".

My /opt/informix/etc/sqlhosts entry:
katalyst onsoctcp solomon.asbury.edu 1023

What am I doing wrong???

Don

Gerardo Santana said...

Let's use finderr to know more about the problem. Type this at the command prompt:

finderr -908

It seems that you system can't reach solomon.asbury.edu at port 1023, or it's too busy.

Verify that you can access that server at that port. You can do something like this:

telnet solomon.asbury.edu 1023

You should get something like this:

Trying #.#.#.#...
Connected to solomon.asbury.edu.
Escape character is '^]'.

Otherwise you have a network problem.

jonahblue said...

Gerardo - You're the man. Turns out my problem was a firewall issue. Once that was resolved I was able to make an Informix connection and retrieve data with a simple select.

Thanks so much!

Don

Gerardo Santana said...

Great :)

Those firewall problems can be a headache.

By the way, I've just released Ruby/Informix 0.7.0. It has a hand of new nice features you may want to check.

I'll blog about it later.