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]
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.
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.
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.
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.
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.
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.