Multiple Databases using an abstract class - help

I am trying to figure out how to use an abstract class to access multiple databases. I can connect just fine using establish_connections in an abstract class, and I can query the database using find_by_sql on the abstract class. I am having problems with then using the data returned from the query. I am looking on any help on best practices and such.

Here's the setup (simplified for the post)

Computers.rb self.abstract_class=true establish_connection :computer_availability (defined in database.yml)

computer_controller.rb def index   db= Computer.connection   @computers = Computer.find_by_sql(SELECT * FROM compstatus)                --> Returns an array of Computers end

index.html.erb Now I want to loop through @computers and show the data from the query. Since I have a Computer shouldn't I be able to interact with it like any Rails model: @computers.each do |computer|          computer.name end

Nope. This give me this error: ActionView::TemplateError (Mysql::Error: Table 'computer_availability.computers' doesn't exist: SHOW FIELDS FROM `computers`) and in the logs the problem comes form a no_method error on .name

Tried: @computers.each do |computer|          computer[:name] || computer.index_at(0) and various others end Nope. Same Error. no-method error on

So what can I do with @computers? How do I get at the data? What am I missing? Where should I kill the connection?

FYI: If I change find_by_sql with connection.execute I can access the data by doing row[0]

Thanks in advance.

Why are you calling find_by_sql on an abstract class ? When you try and use the computer object (by calling name etc.) rails tries to work out what it should do with attributes (eg is it a string, a date, a number) and things of that order, and to do so it examines the schema for the table it thinks the objects come from.

Fred

Well like I said, I am trying to figure out how to use an abstract class to access a separate database. I understand why calling the object attribute is not working, what I am looking for is the proper way to interact with the separate database. If an abstract class is not the way, what is? If not find_by_sql then is connection.execute the right way? Looking for an actual solution.

Thanks.

I think you've got two things mixed up here.

1) the establish connection is what causes a model to connect to a specific database, effectively overriding the standard connection setup in ActiveRecord::Base.

2) If you want to have multiple models/tables connected to the same database, the way to do that is to have an abstract class from which they inherit the connection (much like normal models inherit the connection from ActiveRecord::Base).

So if you only have a single model/table in the database, then just use 1 and not 2, I think.

HTH

To clarify: Some of the data for my rails app is stored in a non-rails DB table. I need to query this other database and then show the data in the rails app.

I have a model (have tried with abstract class and without) Computers that is connected to the non-rails DB with establish_connection. Then in my controller I want to find all the computers in the non- rails DB and in my view I want to loop through the computers and show their name.

I am trying to figure out the best way to query and show the data in the view.

If I do row = Computer.connection.execute(query) I can then loop through the MySQL::Result list and access the data as an array - so row[0] gives me computer.name

The problem with this is that I then need to know the position of the data in the result. I am wondering if there is a better way to query and then show the data.

Kim wrote:

To clarify: Some of the data for my rails app is stored in a non-rails DB table. I need to query this other database and then show the data in the rails app.

I can only tell you what I did when I needed to do this. I did NOT use find_by _sql, I actually set up ActiveRecord classes for each table in the 'non rails DB'. You can actually specify everything you need in ActiveRecord to over-ride rails conventions. I did have several of these tables in the same 'non-rails' DB, so they DID have a common abstract superclass with a connection.

Here's the code, actually why copy and paste when I can link you to svn.

An AR model for a particular table, as you can see you can even use AR associations (to other tables within the same db! Trying to make associations cross-db tends not to work): http://umlaut.rubyforge.org/svn/trunk/app/models/sfx_db/object.rb

And the superclass that pretty much just establishes the connection (also note it uses an AR feature to make everything read-only, cause that's what I wanted in this case): http://umlaut.rubyforge.org/svn/trunk/app/models/sfx_db/sfx_db_base.rb

Once I set that up, I can and do use ordinary AR stuff with those models, no need for find_by_sql and such.

Jonathan Rochkind wrote:

An AR model for a particular table, as you can see you can even use AR associations (to other tables within the same db! Trying to make associations cross-db tends not to work): http://umlaut.rubyforge.org/svn/trunk/app/models/sfx_db/object.rb

Heh, I also see reviewing old code I haven't looked at for a while, that naming one of my own classes "Object" probably wasn't a great idea, even if it is in a module namespace. Don't copy that part. :slight_smile:

Thanks Jonathan for the example. I had been using an AR model for my non-rails table. The piece I was missing was setting the table name!

So for others this is what I did

class Whatever < AR self.establish_connection :whatever (non-rails DB defined in .yml) self.set_table 'name of non-rails table' end

whatever_controller.rb def index @rows = Whatever.find(:all) (find_by_sql works too) end

now you can do AR stuff with @rows - like loop on it and call row.name

Hope that helps someone else!

Thanks.