POUWIEL|COM

JeroenPouwiel

RoR on remote Oracle db

After several mysterious data disappearances in MySQL, I finally had enough and went on to configure RoR to use a remote Oracle db installation.
Here’s what’s what:

Versions:
Rails 4.0.5
ruby 2.0.0p481 (2014-05-08 revision 45883) [x86_64-linux]

Gemfile:
gem 'ruby-oci8', '~> 2.1.5'
gem "activerecord-oracle_enhanced-adapter", "~> 1.5.0"

database.yml
development:
  adapter: oracle_enhanced
  encoding: utf8
  database: 
  username: 
  password: 

.bashrc
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export TNS_ADMIN=/usr/lib/oracle/12.1/client64/tns
export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
export PATH="$PATH:$HOME/.rvm/bin:$LD_LIBRARY_PATH:$ORACLE_HOME/bin:$TNS_ADMIN" # Add RVM to PATH for scripting
[[ -s "$HOME/.rvm/scripts/rvm" ]] && source "$HOME/.rvm/scripts/rvm"

After several tries to catch the culprit, I was not able to figure out what was going on, it even got to the point where I created a fresh user after a

rake db:refresh

and after a quick bite to eat my user was gone!.

Troubleshooting:
First things first: Can you connect to the db via sqlplus!?
Can Ruby connect to the database?:

ruby -rubygems -e "require 'oci8'; OCI8.new('','','').exec('select * from dual') do |r| puts r.join(','); end"

When you have set the NLS_LANG parameter, you should just get ‘X’ as output.

MySQL, triggers and the obscure disappearance of data when using RoR…

Working through the excellent rails tutorial by Michael Hartl, somewhere around the end chapter 9 I came across some unusual behavior of (what I suspect) Ruby/Rails. My data, which is committed to a MySQL database, kept disappearing.
Whenever a change was made to any of the spec or controller files, all my users would be gone 🙁
To get a grip on who the culprit is, I went off to create some triggers who would log what happened and when:

CREATE TABLE logger
( logger_id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, logger_event      VARCHAR(50)
, logger_table      VARCHAR(50)
, logger_instring   VARCHAR(100)
, logger_outstring  VARCHAR(100)
, creation_date     DATETIME
, last_update_date  DATETIME) ENGINE=MyISAM;

DELIMITER $$
CREATE TRIGGER trg_users_insert
AFTER INSERT ON users
FOR EACH ROW begin
INSERT INTO logger VALUES ( null
           ,'insert'
           ,'users'
           , new.email
           , null
           , new.created_at
           , null );
END$$

CREATE TRIGGER trg_users_update
AFTER update ON users
FOR EACH ROW begin
INSERT INTO logger VALUES ( null
           ,'update'
           ,'users'
           , old.email
           , new.email
           , old.updated_at
           , new.updated_at );	
END$$

CREATE TRIGGER trg_users_delete
before delete ON users
FOR EACH ROW begin
INSERT INTO logger VALUES ( null
           ,'delete'
           ,'users'
           , old.email
           , null
           , null
           , current_timestamp );	
END$$

Not the most sophisticated piece of code ever, but let’s see if it does the trick…

Protected: RoR Sample App

This content is password protected. To view it please enter your password below:

Categories