Nat On Testing

After spending a few hours trying to connect to an oracle database in ironruby using various gems, I gave up. None of the gems out there would work, each for a different reason. It was time to write my own class to do the job of managing connections and executing queries.

You’ll need a few things:

1. A copy of the ‘Oracle.DataAccess.dll’ that you can find somewhere inside this outrageously large download:
   http://www.oracle.com/technology/software/tech/windows/odpnet/index.html
   Copy the ‘Oracle.DataAccess.dll’ into your load path (you’ll find it somewhere in the bowels of the directory structure that the above installs)
2. An oracle database you can point at
3. The connection string required to connect to the database. It’ll look something like:
   Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROT...

Once you’ve got those details, you can use the following class:

require 'System'
require 'System.Data'
require File.join(File.expand_path(File.dirname(__FILE__)), "Oracle.DataAccess.dll")

class IronRubyOracleClient
  #pass in oracle connection string, eg, for Test env:
  def initialize(connection_string)
    @connection = Oracle::DataAccess::Client::OracleConnection.new(connection_string)
  end

  #opens connection
  def open
    @connection.open
  end

  #returns 2D array
  def execute(query)
    @query = query
    cmd = Oracle::DataAccess::Client::OracleCommand.new(@query, @connection)
    cmd.CommandType = System::Data::CommandType.Text
    data_reader = cmd.ExecuteReader()
    column_count = data_reader.visible_field_count.to_i

    result_rows = ::System::Collections::ArrayList.new

    while(data_reader.read) do
      row = ::System::Collections::ArrayList.new
      column_count.times do |i|
        row.add(data_reader.get_oracle_value(i).to_string)
      end
      result_rows.add(row)
    end

    result_set = []

    result_rows.each do |result_row|
      ruby_row = []
      result_row.each do |cell|
        ruby_row << cell.to_s
      end
      result_set << ruby_row
    end

    result_set
  end

  #close connection
  def close
    @connection.close
  end
end

And here’s how you use it:

#create your connection string
connection_string  = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=..." #etc...
#create an instance of the client, passing in the connection string
my_client = IronRubyOracleClient.new(connection_string)
#open a connection to the database
my_client.open
#execute a query and save the result
results = my_client.execute("select * from some_table")
#dump the results (a 2D array of values)
puts results.inspect
#close the connection
my_client.close

It’s fairly slow, but it works. Which is an improvement on what’s out there…

Note that everything is returned as a string. For some reason, the unless the data is a basic string or is a number that fits into an integer, the data gets garbled somewhere between the dll and ironruby. I can’t find out where, so everything-returned-as-a-string is the current compromise. If you can get it to work with all data types, send it along!

Bookmark to: