Rails: Accessing a database not meant for Rails?

Question

I have a standard rails application, that uses a mysql database through Active Record, with data loaded through a separate parsing process from a rather large XML file.

This was all well and good, but now I need to load data from an Oracle database, rather than the XML file.

I have no control how the database looks, and only really need a fraction of the data it contains (maybe one or two columns out of a few tables). As such, what I really want to do is make a call to the database, get data back, and put the data in the appropriate locations in my existing, Rails friendly mysql database.

How would I go about doing this? I've heard* you can (on a model by model basis) specifiy different databases for Rails Models to use, but that sounds like they use them in their entirety, (that is, the database is Rails friendly). Can I make direct Oracle calls? Is there a process that makes this easier? Can Active Record itself handle this?

A toy example:

If I need to know color, price, and location for an Object, then normally I would parse a huge XML file to get this information. Now, with oracle, color, price, and location are all in different tables, indexed by some ID (there isn't actually an "Object" table). I want to pull all this information together into my Rails model.

Edit: Sounds like what I'd heard about was ActiveRecord's "establish_connection" method...and it does indeed seem to assume one model is mapped to one table in the target database, which isn't true in my case.

Edit Edit: Ah, looks like I might be wrong there. "establish_connection" might handle my situation just fine (just gotta get ORACLE working in the first place, and I'll know for sure... If anyone can help, the question is here)

Answer 1

I don't have points enough to edit your question, but it sounds like what you really need is to have another "connection pool" available to the second DB -- I don't think Oracle itself will be a problem.

Then, you need to use these alternate connections to "simply" execute a custom query within the appropriate controller method.

Answer 2

This may not be exactly what you are looking for, but it seems to cover you situation pretty well: http://pullmonkey.com/2008/4/21/ruby-on-rails-multiple-database-connections/

It looks like you can make an arbitrarily-named database configuration in the the database.yml file, and then have certain models connect to it like so:

class SomeModel < ActiveRecord::Base
  establish_connection :arbitrary_database

  #other stuff for your model
end

So, the solution would be to make ActiveRecord models for just the tables you want data out of from this other database. Then, if you really want to get into some sql, use ActiveRecord::Base.connection.execute(sql). If you need it as a the actual active_record object, do SomeModel.find_by_sql(sql).

Hope this helps!

Answer 3

Are you talking about an one-time data conversion or some permanent data exchange between your application and the Oracle database? I think you shouldn't involve Rails in. You could just make a SQL query to the Oracle database, extract the data, and then just insert it into the MySQL database.

Answer 4

You can create a connection to Oracle directly and then have ActiveRecord execute a raw SQL statement to query your tables (plural). Off the top of my head, something like this:

class OracleModel < ActiveRecord::Base
  establish_connection(:oracle_development)

  def self.get_objects
    self.find_by_sql("SELECT...")
  end
end

With this model you can do OracleModel.get_objects which will return a set of records whereby the columns specified in the SELECT SQL statement are attributes of each OracleModel. Obviously you can probably come up with a more meaningful model name than I have!

• Create an entry named :oracle_development in your config/database.yml file with your Oracle database connection details.

Answer 5

If you only need to pull data from your Oracle database, and if you have any ability to add objects to a schema that can see the data you require . . . .

I would simplify things by creating a view on the Oracle table that projects the data you require in a nice friendly shape for ActiveRecord.

This would mean maintaining code to two layers of the application, but I think the gain in clarity on the client-side would outweigh the cost.

You could also use the CREATE OR REPLACE VIEW Object AS SELECT tab1., tab2. FROM tab1,tab2 syntax so the view returned every column in each table.

If you need to Insert or Update changes to your Rails model, then you need to read up on the restrictions for doing Updates through a view.

(Also, you may need to search on getting Oracle to work with Rails as you will potentially need to install the Oracle client software and additional Ruby modules).