# Multiple Databases using an abstract class - help **URL:** \ **Category:** Rails\ **Created:** [May 18, 2010, 9:41pm UTC](https://www.ruby-forum.com/t/multiple-databases-using-an-abstract-class-help/188817 "2010-05-18T21:41:17Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![kim](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/9f8e36/32.png) [@kim](https://www.ruby-forum.com/u/kim)\ **Post date:** [May 18, 2010, 9:41pm UTC](https://www.ruby-forum.com/t/multiple-databases-using-an-abstract-class-help/188817/1 "2010-05-18T21:41:17Z")
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. ---
**Author:** ![kim](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/9f8e36/32.png) [@kim](https://www.ruby-forum.com/u/kim)\ **Post date:** [May 18, 2010, 10:34pm UTC](https://www.ruby-forum.com/t/multiple-databases-using-an-abstract-class-help/188817/2 "2010-05-18T22:34:34Z")
On May 18, 8:39 pm, Kim [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > 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. 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 ---
**Author:** ![kim](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/9f8e36/32.png) [@kim](https://www.ruby-forum.com/u/kim)\ **Post date:** [May 19, 2010, 7:52pm UTC](https://www.ruby-forum.com/t/multiple-databases-using-an-abstract-class-help/188817/3 "2010-05-19T19:52:33Z")
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. On May 18, 1:33 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![kim](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/9f8e36/32.png) [@kim](https://www.ruby-forum.com/u/kim)\ **Post date:** [May 19, 2010, 8:23pm UTC](https://www.ruby-forum.com/t/multiple-databases-using-an-abstract-class-help/188817/4 "2010-05-19T20:23:35Z")
On Wed, May 19, 2010 at 1:50 PM, Kim [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > 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. 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 – Rick DeNatale Blog: [http://talklikeaduck.denhaven2.com/](http://talklikeaduck.denhaven2.com/) Github: [rubyredrick (Rick DeNatale) · GitHub](http://github.com/rubyredrick) Twitter: @RickDeNatale WWR: [http://www.workingwithrails.com/person/9021-rick-denatale](http://www.workingwithrails.com/person/9021-rick-denatale) LinkedIn: [http://www.linkedin.com/in/rickdenatale](http://www.linkedin.com/in/rickdenatale) ---
**Author:** ![kim](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/9f8e36/32.png) [@kim](https://www.ruby-forum.com/u/kim)\ **Post date:** [May 19, 2010, 9:20pm UTC](https://www.ruby-forum.com/t/multiple-databases-using-an-abstract-class-help/188817/5 "2010-05-19T21:20:23Z")
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. ---
**Author:** ![kim](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/9f8e36/32.png) [@kim](https://www.ruby-forum.com/u/kim)\ **Post date:** [May 19, 2010, 10:18pm UTC](https://www.ruby-forum.com/t/multiple-databases-using-an-abstract-class-help/188817/6 "2010-05-19T22:18:42Z")
Jonathan R. 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](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. 🙂 ---
**Author:** ![kim](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/9f8e36/32.png) [@kim](https://www.ruby-forum.com/u/kim)\ **Post date:** [May 19, 2010, 11:15pm UTC](https://www.ruby-forum.com/t/multiple-databases-using-an-abstract-class-help/188817/7 "2010-05-19T23:15:08Z")
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. ---
**Author:** ![kim](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/k/9f8e36/32.png) [@kim](https://www.ruby-forum.com/u/kim)\ **Post date:** [May 19, 2010, 10:16pm UTC](https://www.ruby-forum.com/t/multiple-databases-using-an-abstract-class-help/188817/8 "2010-05-19T22:16:58Z")
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](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](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.