Rails - Aug 2006 - Ad-hoc database queries from models

Hi,

I''m looking at how to do ad-hoc database queries from RoR models - more
specifically I have built an app which tracks issues which uses its own 
nice clean database containing only issues that I care about. I would 
like to be able to (optionally) populate an issue from a few big, slow, 
unwieldly backend databases and want to know the best way to do it. It 
looks like doing something like this:
>> require ''dbi''
=> true
>> conn = DBI.connect(''dbi:ODBC:mssqlserver'',
''username'', ''password'')
=> #<DBI::DatabaseHandle:0xb750bf58 @trace_mode=2, 
@handle=#<DBI::DBD::ODBC::Database:0xb750bea4 @attr={}, 
@handle=#<ODBC::Database:0xb750becc>>,
@trace_output=#<IO:0xb7d35fec>>
>> conn.select_one(''SELECT Subject FROM CASE WHERE CaseId =
12345678;'')
=> ["Test Case"]
>> conn.disconnect
Assuming this is the best way to achieve this (unless there''s good 
reason to do so I don''t want to create models as the SQL code above is 
greatly simplified: in fact to get a name, email and phone number I need 
to join across 4 tables with potentially millions of rows), where''s the
best place to put the DSN, username, password, etc.?

Thanks in advance,

Sam

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

> I''m looking at how to do ad-hoc database queries from RoR models
#find_by_sql can be called from your models and accepts a complete SQL 
string.

I would create an appropriately named model and add a method to it that 
uses your SQL command. Example...

class Issue < ActiveRecord::Base

  # Call by saying Issue.populate
  #
  def self.populate
    find_by_sql(...)
  end

end

Hope that helps.

- Daniel

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