Thursday, September 2, 2010

ActiveRecord for Oracle won't update records

I needed a script to fix up some date data in an Oracle database that didn't lend itself to using a pl/sql script. Naturally I turned to Ruby and ActiveRecord to sort out the issue. I drafted the script against a local MySQL DB and it worked flawlessly, but when run against the Oracle instance it wouldn't save any data changes.

Here's what happened, and how to fix it...

So turns out this has happened before to other - see this post on for details. The OP didn't get any replies so I'm assuming that he figured it out on his own, unluckily for me he didn't share the fix so I had to do some leg work.

For starters I'm running Ruby v1.8.7, activerecord v2.3.8 and activerecord-oracle_enhanced-adapter v1.3.0

My schema is legacy with a primary key that isn't "id". Easy to sort out, I coded up something like this:

class Receipt < ActiveRecord::Base
set_primary_key "rt_receipt_id"

So onto the issue. This worked fine with MySQL but not Oracle so my first stop is to check out the SQL that's being created:

SET "RT_RECEIVED" = TO_DATE('2008-01-03 23:58:19','YYYY-MM-DD HH24:MI:SS')


On any update, AR tries to sql encode the value of the AR object's "id" value as the primary key. Calling the id accessor on my Receipt objects was returning nil, and AR was translating that to a sql NULL. There's a simple fix, alias id to our new primary key accessor:

class Receipt < ActiveRecord::Base
set_primary_key "rt_receipt_id"
alias_attribute :id, :rt_receipt_id