Ad-hoc database queries from models

Rails
1

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

2

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