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.