Raw Block

A while back I wrote about my frustrations trying to get Ruby to talk to Oracle on Mac OS X. I’ve tried a new approach using JRuby and ActiveRecord-JDBC with great success.

What I’m after is quite simple: I just want to use Ruby for some simple housekeeping scripting tasks which occasionally means connecting to Oracle to shunt data around. So this is all outside of Rails, which adds to the fun.

The basic problem with Oracle on Mac OS X is that Oracle support is lousy (add that to my list of complaints :P). There is no standard client install, hence no standard Oracle C libraries. While there is a stripped down version of the libraries - it is only available compiled for PowerPC. So no go with Intel CPUs (unless you want to recompile Ruby for PowerPC and run in emulation mode - just to allow you to build the OCI adapter. yech)

So I gave up.

Until I gave it another shot with JRuby and ActiveRecord-JDBC. Here’s a quick guide:

Install gems

From the command line:

gem install activerecord

gem install ActiveRecord-JDBC

A trap for new players is to make sure you’re using JRuby gem utility, and not the Ruby one - tripped me up.

Setup DB

This can be whatever you’ve already got, or maybe a new DB. Here’s what I’m using for this example:

--our table to test the setup.
--note standard activerecord setup with 'ID' as primary key
create table TEST_MESSAGES
(
  ID      NUMBER not null,
  MESSAGE VARCHAR2(100)
);
alter table TEST_MESSAGES
  add constraint test_messages_pk primary key (ID);

--sequence used to populate PK
create sequence test_messages_seq;

Setup your classpath

Make sure you’ve got your Oracle JDBC driver in a handy directory, and run export CLASSPATH=ojdbc14.jar from your favourite command prompt. You can get the ojdbc14.jar driver file from Oracle’s JDBC download page, although in general I find Oracle’s site on a par with trying to Find Permit A-38 in terms of frustration…

Run some Ruby code against it

Now for the fun bit.

# active_record is the standard rails AR require
require ‘active_record‘

# jdbc_adapter is in ActiveRecord-JDBC, and makes a JDBC adapter 
# available to ActiveRecord
require ‘active_record/connection_adapters/jdbc_adapter‘

# Connect to the database 
# - this is required because we’re running without rails.
ActiveRecord::Base.establish_connection(:adapter  => ‘jdbc‘,
                                        :driver   => ‘oracle.jdbc.driver.OracleDriver‘,
                                        :url      => ‘jdbc:oracle:thin:@server:1521:database‘,
                                        :username =>  ‘username‘,
                                        :password =>  ‘password‘)

# our model class that maps to the table we created
class TestMessage < ActiveRecord::Base
end

# insert a record                                
hi_message = TestMessage.new
hi_message.message= ‘Hello World!‘
hi_message.save

# and another one for fun
ar_message = TestMessage.new
ar_message.message = ‘This is activerecord JDBC on Oracle‘
ar_message.save

# should spit back out both our inserted records
TestMessage.find(:all).each do |m|
  puts m.message
end

In this example I used ActiveRecord standard table structures, but in practice I was connecting to legacy databases. ActiveRecord is still quite useful with legacy databases, but you just have to do a bit more setup. set_primary_key and set_table_name will get you some of the way. has_many and belongs_to with the :foreign_key option will get you most of the rest of the way.

Environment setup is one of the worst aspects of software development. You can get all hyped up about the latest and greatest technology and techniques, but at some point you’ve got to get your environment set up so you can use them. I’ve spent a good part of this afternoon trying to get Ruby to work with an Oracle database on my Mac OS X box.

Simple huh? Nope. Mostly just old fashioned pain.

A bit of googling initially pointed me to the ruby-oci8 project, which provides a Ruby library for Oracle. I happily download and started working through the install instructions which involves compiling the library. Fine - except for the compilation problems. Seems my Oracle Instant Client install isn’t quite complete.

Next step is a trip to Oracle’s website, which is an altogether separate exercise in frustration - after eventually reviving my developer login (with the help of mailinator and Borat), I stumbled around clicking through a “terms of use - sell your soul agreement” for every… single… page… I eventually find the download I’m after. Cool.

I try to compile ruby-oci8 again, but this time I come up against:

Oracle doesn't support intel mac.
 http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/223854

There are three solutions:
1. Compile ruby as ppc binary.
2. Wait until Oracle releases mac intel binary.
3. Use a third-party ODBC driver and ruby-odbc instead.
    http://www.actualtechnologies.com/

By this time about 2 hours have gone by, and hair is being pulled out in clumps. Admittedly I would have known this if I’d just read the platform specific issues on the ruby-oci8 site.

By now it’s 6pm, and the evening round of CS is starting up. I’ll save this one for tomorrow. Maybe I’ll play around with ODBC, or possibly try using a JDBC driver and using JRuby instead.

The ironic part is that I’m just wanting to "quickly" mock up some code to test out something that will later be implemented properly in Java (oldschool, heavy Java). At this rate it probably would have been quicker to do something quick and nasty in Java and be done with it (which isn’t *that* bad with Eclipse). Lately though, I’m shuddering at the thought of file after file of needless case handling, redundant interfaces, xml config files, and over engineered Sun APIs - even for simple stuff. Environmental pain is probably the lesser of two evils in the long run.