Last Updated on November 30, 2019 by Christopher G Mendla
This covers a situation where you need to have rails access an external sql database other than the database used for the application. For example, you might need to pull information from one table to populate fields in your current application. This is further complicated if your application is using a schema prefix.
1. Set a database connection in config/database.yml. You need to set schema_search_path especially if the external sql table is using a schema prefix other than the schema prefix used by the application.
adapter: sqlserver
host: sql
port: 1433
database: SQLDATA
username: myuser
password:
schema_search_path: dbo
2. Set up a class.
- If the foreign SQL table is using a different schema prefix, call out the proper schema prefix with a def self.table_name_prefix
- Use self.abstract_class = true to tell Rails that this is a model without a table
- in the classes for the actual tables, specify self.table_name
- Note – the code below for the ExternalTable and Address Classes goes into one file, not separate files. You add as many additional classes as there are tables that you need to access.
class ExternalTable < ActiveRecord::Base
self.abstract_class = true
def self.table_name_prefix
‘dbo_’
end
establish_connection “external_sql_tables”
end
class Address < ExternalTable
self.table_name = “Address”
end
<% @test = ExternalTable::Address.all %>
<% @test.each do |test| %>
<%= test.Addr_Address1 %><br>
<% end %>