I have a need to connect to a separate database in order to get some
information from that DB but I am trying to figure out the most
elegant way to do it. With this application there will be times when
a new DB will be added to the mix and I would rather not have to add
models to my app every-time a new database is added. I could do it
this way if that is how everyone tells me to go, but when looking at
what I need from the DB It boils down to every once in a while I need
one value from the DB. I figured since both DB’s are running on the
same server and are MySQL that I could just execute a query that is
similar to this:
SELECT DB1.table1.field1
FROM DB1.table1 WHERE DB1.table1.field2 = @object.id
The problem I am having is that i can’t figure out how to execute
this query without a model associated with it. I have tried:
newobject2 = Object.find_by_sql(“SELECT DB1.table1.field1FROM
DB1.table1 WHERE DB1.table1.field2 = @object.id”)
But I get an error that says that Object has no idea what field1 is
(which is true since field1 is not part of the Object. is just a
value (and in all instances will be just one value returned)
How can you execute a querry for a value that I want to be stored in
memory but never have an associated object? Is it possible? Or do I
have to create an Model with an establish_connection as mentioned by
most everyone else from the list, as well as the Rails Recipes Beta
Book? To me the second way is not the most DRY. Just executing a
query to get the data I need is the most dry way as creating a model
for every DB I need to connect to when All i have to do is get one
value seems to carry overhead that is not needed.
The Ideal would be to create my Object object that is a Join of the
two tables (which my DBA friends tell me is possible as well) but I
can’t figure that one out either.
So to summarize for those that skim through these messages I would
either like to do one of the the following two things:
Have a model called Object. The find for this model would return a
join of two tables from two different DB’s using the same connection.
or
Execute a DB query that doesn’t have a model associated with it in
order to get a value that I can store in memory (instance variable).
Thanks for all the help
Andrew