How do I pull data from a dual database in Rails (Postgresql and SQL Server)

Question

I was working with just one database initially but I needed to add the clients other database which is a SQL Server database. I was able to connect but I am running into a few problems.

Original full database.yml

development:
  adapter: postgresql
  database: martin_development
  username: *******
  password: *******
  pool: 5
  timeout: 5000

development_sec:
  adapter: sqlserver
  host: *******
  port: 1433
  database: Database1
  username: *******
  password: *******

I get the following error:

TinyTds::Error (Database 'DATABASE1' does not exist. Make sure that the name is entered correctly.)

If I take out the database name so it looks like this:

development_sec:
      adapter: sqlserver
      host: *******
      port: 1433
      database:
      username: *******
      password: *******

Everything appears to run normal and I do not get any error messages in regards to a database not being found. However, that database does exist on the clients side.

I am confused on how to extract data from my customers SQL Server database. I am trying to follow along with some resources I found so my project files so far are looking like this:

Model (Mssql.rb)

class MssqlBase < ActiveRecord::Base
  establish_connection :development_sec
  self.abstract_class = true
end

Model(site.rb)

class Site < MssqlBase


end

Controller(sites_controller.rb)

class SitesController < ApplicationController
  def index

      @sites = Site.all
      @hash = Gmaps4rails.build_markers(@sites) do |site, marker|
      marker.lat site.latitude
      marker.lng site.longitude

    end
  end


end

database.yml

development:
      adapter: postgresql
      database: martin_development
      username: *******
      password: *******
      pool: 5
      timeout: 5000

development_sec:
      adapter: sqlserver
      host: *******
      port: 1433
      database:
      username: *******
      password: *******

My goal is to start pulling data from the database but as of right now with this look in regards to my file I am getting the following error:

ActiveRecord::StatementInvalid (TinyTds::Error: Invalid object name 'sites'.: SELECT [sites].* FROM [sites]):

So just to recap I need it to read from the 'DATABASE1' but it is saying it does not exist. When I leave it out for some reason it is connecting with the server but I do not know which database. Now I am trying to pull the data but am getting invalid statements. Any help would be appreciated.

What version of MSSQL are you connecting to? And what gems are you using to connect. As far as I know you can't just set it up like MySQL without an adapter installed. — Beartech
I am using the tiny-tds gem and activerecord -sqlserver-adapter. The version of it is 2008 r2. — Jermaine Subia
Can you try connecting from irb using: require 'rubygems'; require 'tiny_tds'; client = TinyTds::Client.new :username => '*******', :password => '*****', :host => 'host_url'; Obviously replace username, password, and host-url with your info. — Beartech
This is the message I get => #<TinyTds::Client:0x00564a1d9c8ed0 @query_options={:as=>:hash, :symbolize_keys=>false, :cache_rows=>true, :timezone=>:local, :empty_sets=>true}> — Jermaine Subia
Well it appears that Tiny TDS is creating a connection. When you do something like this to troubleshoot I recommend editing your question and adding what you tried and the result at the bottom of your original post. That way people don't have to read through all of these comments to find out what you have tried. — Beartech

Ruslan Ruslan · Accepted Answer · 2017-08-09T19:32:04

You can do this with Rails.

Create this method.

def with_connection(database, &block)
  ActiveRecord::Base.establish_connection(database)
  yield
  ActiveRecord::Base.establish_connection
end

It changes what database your app is connected to, runs the query and then restores it to default. Not sure if thread safe. Using establish_connection on its own, will not revert to original (as your app can only be connected to one database at a time)

Then use:

with_connection :development_sec do
  MssqlBase.first # will call development_sec database
end

How do I pull data from a dual database in Rails (Postgresql and SQL Server)

1 Answers