Connecting with Oracle from you ruby/rails applications on an "Intel Mac OS X" using DBI with OCI8

Prerequisite:

- Mac OS X (10.5.5)
- ruby --version => 1.8.6 (2008-03-03 patchlevel 114) [universal-darwin9.0]

NOTE: Here the machine where i am working is 'SBKislamzD1' and the user name is 'islamz'. In your case just replace this user name with your user name

Installing DBI

The easiest way of installing a gem is using gem command [gem install dbi] But here I am going to use OCI8-1.0.3. With this version of OCI8 library not all the version of DBI works. In my case DBI version 0.2.0 worked. You can't install this version (0.2.0) of DBI using gem install. You need to downloaded the tar file and unzip it.

Lets say the unzip folder is /Users/islamz/Downloads/dbi-0.2.2
Now open a terminal/console and goto /Users/islamz/Downloads/dbi-0.2.2 and run following commands:

> ruby setup.rb config --with=dbi
> ruby setup.rb setup

Now you should run the following command with 'sudo'

> sudo ruby setup.rb install

If you have any other version of DBI gem you might need to uninstall that gem

Installing Oracle Instant Client

At first from http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/intel_macsoft.html download following packages:

• - Instant Client Package - Basic (10.2.0.4)
• - Instant Client Package - SQL*Plus (10.2.0.4)
• - Instant Client Package - SDK (10.2.0.4)

Now unzip all of packages in /Library/Oracle/instantclient_10_2. After unzipping you should have followings in this folder:

• - BASIC_README
• - classes12.jar
• - genezi
• - glogin.sql
• - libclntsh.dylib.10.1
• - libnnz10.dylib
• - libocci.dylib.10.1
• - libociei.dylib
• - libocijdbc10.dylib
• - libocijdbc10.jnilib
• - libsqlplus.dylib
• - libsqlplusic.dylib
• - ojdbc14.jar
• - sdk
• - sqlplus
• - SQLPLUS_README

Its now time to add some environment variables. For this purpose open /etc/profile in edit mode and add the environment variables as stated below:

• Open the file in edit mode using: sudo vi /etc/profile
• Add following in this file:
  
  ORACLE_HOME=/Library/Oracle/instantclient_10_2
  export TNS_ADMIN=$ORACLE_HOME
  export LD_LIBRARY_PATH=$ORACLE_HOME
  export DYLD_LIBRARY_PATH=$ORACLE_HOME
  export PATH=$PATH:$ORACLE_HOME
  
  
• Save the file and logoff then login
• To test whether the environment variables are set or not type 'env' in terminal(console)

Now you need to create symlinks. To do this follow these steps:

• open a terminal/console and goto /Library/Oracle/instantclient_10_2
• sudo ln -s libclntsh.dylib.10.1 libclntsh.dylib
• sudo ln -s libocci.dylib.10.1 libocci.dylib
• in /Library/Oracle/instantclient_10_2 folder you will find two new symlink

Compiling ruby-oci8

To compile ruby-oci8 follow the steps as stated below:

• download the ruby-oci8-1.0.3.tar If you are using Safari then it will be downloaded in /Users/islamz/Downloads/
• unzip it to ruby-oci8-1.0.3 (/Users/islamz/Downloads/ruby-oci8-1.0.3)
• run terminal/console and goto /Users/islamz/Downloads/ruby-oci8-1.0.3
• export SQLPATH=$ORACLE_HOME
• export RC_ARCHS=i386
• ruby setup.rb config
• make
• sudo make install [Don't forget to run this command as 'sudo']
  

So, you are done. Now you need to test whether your compilation is okay or not.

You can easily test by following these steps:

• run terminal/console
• irb
• require 'oci8' #it should return 'true'

If there is no error then you installation is SUCCESSFUL


SIDE NOTES

1) How to check which version of DBI is supported:

- goto /Library/Ruby/Site/1.8/DBD/OCI8/
- open the file OCI8.rb
- check the value of USED_DBD_VERSION. [In my case it was "0.2"]

2) Problems with NetBeans:

If you are using NetBeans for developing your project in that case you might face some problems:

a) You might get some permission related problems. So run Netbeans with sudo

b) When running your application if it throws an error that 'libclntsh.dylib.10.1' is not found in '/scratch/plebld/208/rdbms/lib/' then put the file in the specified path

c) When running your application you are getting this error:

OCIError (Error while trying to retrieve text for error ORA-24812):
lob.c:194:in oci8lib.so
/Library/Ruby/Site/1.8/oci8.rb:1005:in `write'

It means when you are running NetBeans as sudo the environment variables are missing. Follow these steps:

• SBKislamzD1:~ islamz# sudo su
• sh-3.2# ORACLE_HOME=/Library/Oracle/instantclient_10_2
• sh-3.2# export ORACLE_HOME=$ORACLE_HOME
• sh-3.2# export TNS_ADMIN=$ORACLE_HOME
• sh-3.2# export LD_LIBRARY_PATH=$ORACLE_HOME
• sh-3.2# export DYLD_LIBRARY_PATH=$ORACLE_HOME
• sh-3.2# export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
• sh-3.2# /Applications/NetBeans/NetBeans\ 6.1.app/Contents/MacOS/netbeans

Data Consistency by Locking: Rails Way

In Databased based application data consistency is an important issue. Data inconsistency might occur when more than one users are updating the same model at the sametime. For example you have a model Book. The model has this properties:

- id
- title
- writer

There is one Book with this properties:
- id => 1
- title => "My book title"
- writer => "Mr. Y"

User1 selects a Book (id=1) in to edit and then User2 selects the same Book (id=1) to edit. Now the User1 changed the value of writer to 'Zahid' and saved it. In the database following update query will be executed:


UPDATE books SET title = 'My book title', writer = 'Zahid' WHERE id = 1


And the User2 changed the value of title to 'Updated Title' and saved it. In the database following update query will be executed:


UPDATE books SET title = 'Updated Title', writer = 'Mr. Y' WHERE id = 1


As a result the update done by User1 will get lost.


This is a common problem and there are two different solutions of this problem:
1) Pessimistic Locking
2) Optimistic Locking


Pessimistic Locking:

Pessimistic locking functionality is provided by the database. This locking happens at the database level. In this locking you have to lock the record while selecting the record to update. You can lock a record while updating by using "FOR UPDATE" with the "SELECT" query [NOTE: There are some other locking clause except "FOR UPDATE" and for that read the database documentation].
And the rails ways to do this locking are:

1) while selecting use :lock => true
2) use lock! method of a model that is already selected


Example:

Book.transaction do
book = Book.find(1, :lock=> true)
book.writer = 'Zahid'
book.save!
end


This will lock the book record (id = 1) through out the specified transaction and prevent updates of this record during this time. But if the model is already selected and then you want to lock it for update in that case you can lock the model with lock! method:


book = Book.find(1)
book.lock!


The problem with Passimistic Locking is, if a user select a book to update it and then forget about it or the locking time gets extended for any reason then if there is any other call to update this model then the second call will just HANG, no error will be raised.


Optimistic Locking:


This is a tricky way of confirming data consistency but not locking the record. As here no record is locked so there is no chance of HANG. In this method a new column is added to a table that column contains a version of the record. Unlike Pessimistic Locking the protection is provided at the application level instead of database level.


Lets say the column name is lock_version and the default value is 0. So when the User1 select a Book to update then
- the lock_version for this model is 0


User2 selects the same Book to update and this again
- the lock_version for this model is 0


If any of the user update the Book model then:
- save the udpated model
- the lock_version is incremented by one

Then if the other user wants to save his data the updated record will have lock_version = 0 and in the table the record has lock_version = 1 that means there is dirty data. In that case we can write code so that the second user will not be able to save the dirty data. He will need to refetch the data and update it.

In rails this support is already given with ActiveRecord. What you will need is only to add an extra column lock_version [aka. 'magic column'] with default value = 0 in the 'books' table. When updating the framework will confirm that the updating record has the same value as when the model was first read from database. If the values are equal then it will allow to update the record and then increment the value of lock_version column. Otherwise it will raise ActiveRecord::StableError.


You can deactivate this behavior by setting ActiveRecord::Base.lock_optimistically = false. If you want to override the name of the lock_version column you will need to invoke the set_locking_column method.