Jump to content

The ultimate community for Ruby on Rails developers.


Photo

The proper way to connect to a remote db in rails 4


  • Please log in to reply
10 replies to this topic

#1 Vell

Vell

    Inspector

  • Members
  • 93 posts
  • LocationWashington, DC

Posted 23 June 2014 - 07:47 PM

Hello all,

 

I am attempting to connect to a remote MSSQL server to use an authentication server and I am finding myself lost on the best way to finish the implementation.

 

I have added a section for the MSSQL server in my database.yml file but from there I am not sure the best way to proceed. The original way that I learned how to do this was to create model that establishes a connecting to the remote server and use that as my go between using ActiveRecord.

 

I was told at some point this was not the best approach. I am curious what the proper way is.

 

Ideally the end result of this is that I covert this into a gem to be used with my other rails apps but I have never created a gem before. But first thing is first, implementing it correctly from within my app.

 

Any thoughts, feedback, or references would be greatly appreciated.

 

Thanks,



#2 Ohm

Ohm

    Driver

  • Moderators
  • 520 posts
  • LocationCopenhagen

Posted 23 June 2014 - 07:59 PM

You can use the establish_connetion method in ActiveRecord to connect to multiple databases. (http://apidock.com/r...onnection/class)

 

As GavinLaking is saying on the above linked page, in the models that need this other database, you can just call

establish_connection @connection_object

Blog: http://ohm.sh | Twitter: @madsohm | Work: Lokalebasen.dk


#3 Vell

Vell

    Inspector

  • Members
  • 93 posts
  • LocationWashington, DC

Posted 24 June 2014 - 01:46 PM

It looks like I am close. It looks like I am getting a ,for lack of better terminology, translation problem. Where I have called my class person and the query is searching for a table called people when the table is actually called person. I am not sure how to address that.

 

example:

Person.first

returns the following:

Person Load (1.7ms)  EXEC sp_executesql N'SELECT TOP (1) [people].* FROM [people] ORDER BY [people].[id] ASC'
TinyTds::Error: Invalid object name 'people'.: EXEC sp_executesql N'SELECT TOP (1) [people].* FROM [people] ORDER BY [people].[id] ASC'
ActiveRecord::StatementInvalid: TinyTds::Error: Invalid object name 'people'.: EXEC sp_executesql N'SELECT TOP (1) [people].* FROM [people] ORDER BY [people].[id] ASC'
from /Users/lovell/.rvm/gems/ruby-2.0.0-p451/bundler/gems/activerecord-sqlserver-adapter-34d5c731059b/lib/active_record/connection_adapters/sqlserver/database_statements.rb:423:in `each' 

So far my set up looks like so:

 

$HOME/.freetds.conf

[sqlserver]
  host =  server.domain
  port = 1433
  tds version = 7.0
  database = db_name

config/database.yml

sqlserver_development:
  adapter: sqlserver
  host: server.domain
  mode: :dblib
  database: db_name
  username: username
  password: password

models/sql_server.rb

class SqlServer < ActiveRecord::Base
  establish_connection :sqlserver_development
  self.abstract_class = true
end

models/person.rb

class Person < SqlServer
end


#4 Vell

Vell

    Inspector

  • Members
  • 93 posts
  • LocationWashington, DC

Posted 24 June 2014 - 01:55 PM   Best Answer

Ah I found it. Setting self.table_table = "table_name" works. I was able to do Person.first and get a result back. Painful but not as painful as I thought it would be.



#5 jasmo2

jasmo2

    Passenger

  • Members
  • 4 posts

Posted 22 October 2015 - 12:12 AM

Hello @Vell #Vell Vell,
I am trying just like you to access a remote SQL-Server while I have another principal database.
What exacly you mean with:

self.table_table = "table_name"
table_table==method name?
"table_name" == remotetable name?
thanks for your colaboration



#6 Vell

Vell

    Inspector

  • Members
  • 93 posts
  • LocationWashington, DC

Posted 22 October 2015 - 12:17 AM

Hey Jasmo2,

 

What I meant in my reponse self.table name should equal the table that you wish to point to on the remote db. Idealing your class name and your table name have the same name or as close to it as possible.

 

So take my previous post where I used the person model that inherits from a class that is the base connection to the sql server

class Person < SqlServer
end

At the top of the class I would specify the following:

class Person < SqlServer
  self.table_name = 'Persons'
end

This makes it so that your class only points to the data in the persons table on the remote ms sql db (in this case).



#7 jasmo2

jasmo2

    Passenger

  • Members
  • 4 posts

Posted 22 October 2015 - 05:52 AM

Another query for you Mr Vell,

How do I query this new Object?



#8 Vell

Vell

    Inspector

  • Members
  • 93 posts
  • LocationWashington, DC

Posted 22 October 2015 - 12:06 PM

You query it the same as you would any other ActiveRecord object.

Person.first
Person.last
Person.find(params[:id])
Person.where(first_name: 'vell')

These all work as you would expect.

 

 

The only issue I have found is when dealing with activerecord associations. Such things as :has_many and :belongs_to work fine for grabbing associated records. But adding or removing associated records you have to write the code manually to do.  Those particular actions seem to rely on the data being in the same db instance. Adding a removing data using those relations would only work if the data was in the same database or there were 2 databases running in the same mysql instance using a database prefix.

Because the data is located in a separate place physically, this does not function properly so you will have to write the methods to remove or add the association. There was a gem out in the world that did this in rails 3 but there currently isn't one out there for rails 4. Here is the write up for what a person has written in rails 3 so you can (sort of) get an idea of my explanation.

 

http://emphaticsolut..._databases.html



#9 jasmo2

jasmo2

    Passenger

  • Members
  • 4 posts

Posted 22 October 2015 - 04:43 PM

I found this very interenting, but I am unable to query the model from the controller:

development_sqlserver:
  adapter: sqlserver
  host: <%= ENV['O_IP'] %>
  port: <%= ENV['O_PORT'] %>
  database: <%= ENV['O_DB'] %>
  username: <%= ENV['O_USERNAME'] %>
  password: <%= ENV['O_PASSWORD'] %>
class SqlServer < ActiveRecord::Base
  establish_connection "#{Rails.env}_sqlserver"
  self.abstract_class = true
end
class ChequeoVirtual < SqlServer
  self.table_name = "CHEQUEO_VIRTUAL"
end
ChequeoVirtual.first

I get this error in the controller:
NameError (uninitialized constant WelcomeController::ChequeoVirtual):



#10 Vell

Vell

    Inspector

  • Members
  • 93 posts
  • LocationWashington, DC

Posted 22 October 2015 - 09:18 PM

A couple of things.

 

You should try to query your data in the console first before using it in your controller. Try opening a rails console and seeing if you can query the data directly from the model. That is how you can pretty much tell if things are working correctly. If you can't query from there then there isn't much point in trying from anywhere else.

 

Can you tell me the steps you used to set up your connection to an MSSQL server? Your database settings don't look right to me? If you look at my settings in an earlier post you will see I have a line that contains

mode: dblib

Also, from first glance at your response it looks like your name spacing in some way that I don't quite understand. Can you past what you have in your application controller and what you have in the ChequeoVirtual controller?. But only do this if you actually can query data in the console. If you can't query data in the console then we will attempt to investigate what is going on there before moving to your controller.



#11 jasmo2

jasmo2

    Passenger

  • Members
  • 4 posts

Posted 24 October 2015 - 07:14 PM

Thanks Vell for all the patience






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users