views:

1402

answers:

3

Here is the case.

I'm writing a Rails application which will monitor data quality over some specific databases. In order to do that, I need to be able to execute direct SQL queries over these databases - which of course are not the same as the one used to drive the Rails application models. In short, this means I can't use the trick of going through the ActiveRecord base connection.

The databases I need to connect to are not known at design time (i.e.: I can't put their details in database.yaml). Rather, I have a model 'database_details' which the user will use to enter the details of the databases over which the application will execute queries at runtime.
So the connection to these databases really is dynamic and the details are resolved at runtime only.

How can I do that?

Thanks,
Rollo

A: 

You may be able to do this through self.establish_connection

Mr. Matt
Hello Mat,Actually, that won't do because you have to 'hardcode' the remote database in database.yaml and mines are dynamic, which means I have to connect to them by resolving their connection details at runtime from the model itself.I'll update my question to make this clearer!Thanks
Rollo Tomazzi
+3  A: 

You can programmatically establish connection using a call like this

ActiveRecord::Base.establish_connection(
   :adapter  => "mysql",
   :host     => "localhost",
   :username => "myuser",
   :password => "mypass",
   :database => "somedatabase"
)

As you see you can replace the somedatabase by a database_model.database_name value. same with the adatpter and all

ActiveRecord::Base.establish_connection documentation

Then you can use

ActiveRecord::Base.find_by_sql("select * ")

to execute your sql query.

ActiveRecord::Base.find_by_sql documentation

Mr Matt was right if incomplete

More information (outdated but still useful for the design approach) can be found here and remember to reconnect to the normal database when you are done :)

Jean
Thanks! I'll look into all this
Rollo Tomazzi
+1  A: 

I had a situation like this where I had to connect to hundreds of different instances of an external application, and I did code similar to the following:

  def get_custom_connection(identifier, host, port, dbname, dbuser, password)
      eval("Custom_#{identifier} = Class::new(ActiveRecord::Base)")
      eval("Custom_#{identifier}.establish_connection(:adapter=>'mysql', :host=>'#{host}', :port=>#{port}, :database=>'#{dbname}', " +
      ":username=>'#{dbuser}', :password=>'#{password}')")  
    return eval("Custom_#{identifier}.connection")
  end

This has the added benefit of not changing your ActiveRecord::Base connection that your models inherit from, so you can run SQL against this connection and discard the object when you're done with it.

brokenbeatnik
Indeed, the approach is better suited to the problem I had at the time. Thanks a lot for sharing!
Rollo Tomazzi