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.