Rails - May 2010 - 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.



-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On May 18, 8:39 pm, Kim
<kim.gri...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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 Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On May 18, 8:39 pm, Kim
<kim.gri...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group
athttp://groups.google.com/group/rubyonrails-talk?hl=en.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Wed, May 19, 2010 at 1:50 PM, Kim
<kim.griggs-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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/
Github: http://github.com/rubyredrick
Twitter: @RickDeNatale
WWR: http://www.workingwithrails.com/person/9021-rick-denatale
LinkedIn: http://www.linkedin.com/in/rickdenatale

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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.




On May 19, 11:21 am, Rick DeNatale
<rick.denat...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Wed, May 19, 2010 at 1:50 PM, Kim
<kim.gri...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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/
> Github:http://github.com/rubyredrick
> Twitter: @RickDeNatale
> WWR:http://www.workingwithrails.com/person/9021-rick-denatale
> LinkedIn:http://www.linkedin.com/in/rickdenatale
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group
athttp://groups.google.com/group/rubyonrails-talk?hl=en.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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.



-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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. :)
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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.


On May 19, 1:18 pm, Jonathan Rochkind
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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. :)
> --
> Posted viahttp://www.ruby-forum.com/.
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group
athttp://groups.google.com/group/rubyonrails-talk?hl=en.
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.