IT-eye

In this post i’ll explore some ways of using Ruby with an Oracle database. I’ve installed ruby on my windows laptop and i’m using Oracle Express Edition running on CentOS using vmware.

First, we’ll need to download ruby for windows. The easiest way to get ruby on windows is to use the Windows Ruby installer available on rubyforge. Install ruby by running the installer. Now we can test the installation. Create a test application e:\programs\ruby\tests\helloworld.rb:

puts "HelloWorld!"

When you run this ruby script you should get the following output:

E:\programs\ruby\tests>helloworld.rb
HelloWorld!

Ok, ruby seems to be working. Time to add Oracle connectivity. There are a couple of libraries out there that will let you connect with Oracle, but it seems like oci8 is the most up to date option. Download ruby oci8 from oci8 on rubyforge, and run the installer as follows:

e:\temp>ruby ruby-oci8-0.1.13-mswin.rb
Copy OCI8.rb to e:/programs/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8
Copy oci8.rb to e:/programs/ruby/lib/ruby/site_ruby/1.8
Copy oci8lib.so to e:/programs/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt
OK?
Enter Yes/No: yes
Copying OCI8.rb to e:/programs/ruby/lib/ruby/site_ruby/1.8/DBD/OCI8 ... done
Copying oci8.rb to e:/programs/ruby/lib/ruby/site_ruby/1.8 ... done
Copying oci8lib.so to e:/programs/ruby/lib/ruby/site_ruby/1.8/i386-msvcrt ... done
OK

You need the oracle sqlnet libries on your windows machine. If you do not have these, you can install oracle instant client. The 10Mb basic-lite is a 10M download contains everything you need to use sqlnet. Although not really needed, you may also want to download sqlplus for oracle instant client, which is another 720kb. Sqlplus is a command line sql interface for the oracle database.

Unzip both files, eg in e:\oracle. Test if you can connect to oracle using sqlplus. Start sqlplus.exe. I have an oracle express edition running on centos using vmware workstation. To connect to this database i can use the following connectstring: system/manager@//oraxe/xe, where oraxe is the machine name, and xe is the oracle instance name.

Add oracle instant client to your path:

E:\programs\ruby\tests>set PATH=%PATH%;e:\oracle\instantclient_10_2

We should now be able to connect to Oracle from ruby. The following ruby script uses the oci8 api to run a query. Create a ruby test application e:\programs\ruby\tests\oracle-test1.rb:

require 'oci8'
conn = OCI8.new('system', 'manager', '//oraxe/xe')
conn.exec('select * from all_users where username = :1','SYSTEM') do |r|
  puts r
end

When you run this script you should see the following:

E:\programs\ruby\tests>oracle-test1.rb
SYSTEM
5.0
2005/10/10 03:16:21

Another method for database access in ruby is Ruby DBI. Ruby DBI is a database independent interface for accessing databases, similar to perl’s DBI. Note that Ruby DBI seems pretty unsupported, the latest change on the changelog is dated september 7th 2003. Also, Ruby oci8’s website (you need it for dbi) says that support for dbi is experimental. So, this is not really a combination which you would want to use in a production environment. Anyway, lets see what a dbi script would like:

require 'dbi'

dbh = DBI.connect('DBI:OCI8://oraxe/xe','system','manager')

sth = dbh.prepare('select * from all_users where username = :1')
sth.execute('SYSTEM')

while row=sth.fetch do
  p row
end

dbh.disconnect

The result when you run this script:

E:\programs\ruby\tests>oracle-test2.rb
["SYSTEM", 5.0, 2005/10/10 03:16:21]

Most people who want to start with Ruby usually do this because of Ruby on Rails. Ruby on rails contains another method for connecting to databases: ActiveRecord. This is an implementation of the Active Record design pattern.

I want to create a ruby script which uses ActiveRecord, but outside rails. I first need to install Ruby on rails, to get the required libraries:

E:\programs\ruby\tests>gem install rails --include-dependencies

E:\programs\ruby\tests>"e:\programs\ruby\bin\ruby.exe" "e:\programs\ruby\bin\gem" install rails --include-dependencies
Attempting local installation of 'rails'
Local gem file not found: rails*.gem
Attempting remote installation of 'rails'
Updating Gem source index for: http://gems.rubyforge.org
Successfully installed rails-1.0.0
Successfully installed rake-0.6.2
Successfully installed activesupport-1.2.5
Successfully installed activerecord-1.13.2
Successfully installed actionpack-1.11.2
Successfully installed actionmailer-1.1.5
Successfully installed actionwebservice-1.0.0
Installing RDoc documentation for rake-0.6.2...
Installing RDoc documentation for activesupport-1.2.5...
Installing RDoc documentation for activerecord-1.13.2...
Installing RDoc documentation for actionpack-1.11.2...
Installing RDoc documentation for actionmailer-1.1.5...
Installing RDoc documentation for actionwebservice-1.0.0...

To test active record we first need some datbase objects. In this example i’m going to store todolists in a table. Create a user tl_owner, password tl_owner, a table to store todolists and a sequence for the table:

CREATE TABLE  "TODO_LISTS"
   (	"ID" NUMBER NOT NULL ,
	"NAME" VARCHAR2(30) UNIQUE,
	"DESCRIPTION" VARCHAR2(2000),
	 CONSTRAINT "TODO_LISTS_PK" PRIMARY KEY ("ID")
   )
create sequence todo_lists_seq;

The following ruby application will insert one record into this table and query it:

require 'active_record'

class TodoList < ActiveRecord::Base
end

ActiveRecord::Base.establish_connection(
  :adapter  => "oci",
  :host     => "oraxe/xe",
  :username => "tl_owner",
  :password => "tl_owner"
)

todoList = TodoList.new
todoList.name = "List 1"
todoList.description = "My first description"

todoList.save

todoList2 = TodoList.find(:first,:conditions =>["name = ?","List 1"])

puts "#{todoList2.id}, #{todoList2.name}, #{todoList2.description}"

Running this script results in the following output:

E:\programs\ruby\tests>oracle-test3.rb
104.0, List 1, My first description

I’ll finish this post by showing how you can do some simple reporting using ruby. The following script creates an html report listing Oracle users whose names contain ‘SYS’. For the report i’m going to use the ERB templating library, which is also used by Ruby on Rails.

require 'active_record'
require 'erb'

class AllUsers < ActiveRecord::Base
end

ActiveRecord::Base.establish_connection(
  :adapter  => "oci",
  :host     => "oraxe/xe",
  :username => "tl_owner",
  :password => "tl_owner"
)

users = AllUsers.find_all ['username like ?','%SYS%']

report = ERB.new <<-EOF
<html>
  <head><title>Users</title></head>
  <body>
    <table>
      <% users.each do | user | %>
        <tr>
          <td><%=user.user_id %></td>
          <td><%=user.username %></td>
          <td><%=user.created %></td>
        </tr>
      <% end %>
    </table>
  </body>
</html>
EOF

puts report.result(binding)

Running this gives the following result:

E:\programs\ruby\tests>oracle-test4.rb
<html>
  <head><title>Users</title></head>
  <body>
    <table>

        <tr>
          <td>0.0</td>
          <td>SYS</td>
          <td>Mon Oct 10 03:16:21 West-Europa (standaardtijd) 2005</td>
        </tr>

        <tr>
          <td>5.0</td>
          <td>SYSTEM</td>
          <td>Mon Oct 10 03:16:21 West-Europa (standaardtijd) 2005</td>
        </tr>

        <tr>
          <td>20.0</td>
          <td>TSMSYS</td>
          <td>Mon Oct 10 03:24:37 West-Europa (standaardtijd) 2005</td>
        </tr>

        <tr>
          <td>25.0</td>
          <td>CTXSYS</td>
          <td>Mon Oct 10 03:27:58 West-Europa (standaardtijd) 2005</td>
        </tr>

    </table>
  </body>
</html>

Ok, enough for this post. Next time i’ll have a look at Ruby on Rails.

resources:

• Ruby homepage
• ruby central
• Ruby forge
• Using the Ruby DBI module
• Rolling with Ruby on Rails
• Rolling with Ruby on Rails (2)
• Ruby on Rails – Oracle
• Programming Ruby- The Pragmatic Programmer’s Guide