Rails App Database in MySQL - OK, However what if I need to access multiple Databases of different types ie. Sybase, MSSQL and Oracle

Hi,

Although I am an experienced developer, I am new to rails and enjoying how easy it is to build applications. I seem to have come across a stumbling block and was wondering if anyone out there has come across the same problem and could suggest possible solutions.

Problem:

I have an intranet-based application in a company network - using rails. The application has many sub-applications which may be composed of a handful of pages each.

The web application has its own database using MySQL which contains all application specific data, however, I need to retrieve data from different sources which maybe, MSSQL, Oracle or Sybase databases.

For example, I need to access a well establish Sybase database server which contains trade data so I can create a summary of daily Profit & Loss - after filtering, various calculations I will archive this summary data in the Applications MySQL DB for performance views etc.over time.

The Question:

In rails how do I do this neatly, rails is designed to use one database right? Would anyone be able to suggest ways in which I can access such data?

Many thanks in advance.

H

You'll need to take over the connection for your different models (this model is MySQL, that model is Sybase, etc).

Start here, and google for other examples:

http://api.rubyonrails.com/classes/ActiveRecord/Base.html

(I'm currently trying to avoid this issue myself in a company app, but I don't think it will go away, and I won't be able to avoid it for much longer - I'll have to connect to MySQL, SQL Server, and Oracle - sigh)

Problem:

I have an intranet-based application in a company network - using rails. The application has many sub-applications which may be composed of a handful of pages each.

The web application has its own database using MySQL which contains all application specific data, however, I need to retrieve data from different sources which maybe, MSSQL, Oracle or Sybase databases.

For example, I need to access a well establish Sybase database server which contains trade data so I can create a summary of daily Profit & Loss - after filtering, various calculations I will archive this summary data in the Applications MySQL DB for performance views etc.over time.

The Question:

In rails how do I do this neatly, rails is designed to use one database right? Would anyone be able to suggest ways in which I can access such data?

Set up the database connection in database.yml. Let's say it's named "sybasefu".

In the models that need to access that database do this:

class SomeThing < ActiveRecord::Base    establish_connection "sybasefu"    #.... end

One thing to check is if you have several models that need to connect to sybasefu, *each* one will open it's own connection. Unlike your "normal" models which share a single database connection. This was true for Rails 1.1.6 (and yes, I realize how old that is :). To get around it I did this:

class MySybaseClasses < ActiveRecord::Base    establish_connection "sybasefu" end

class Something < MySpaceClasses    #.... end

class SomethingElse < MySpaceClasses    #... end

That may or may not still be an issue, but it's something to check. If it isn't, let me know :slight_smile:

-philip

Thanks Phillip, checking it out right now... this looks so obvious!! Will let you know how it all pans out, but I am thinking this should do the trick in combination with find_by_sql() for the query.

Very helpful thank you :slight_smile: