Read data from MS SQL server Database in rails ubuntu

Question

I want to read data from MS SQL server and perform some operations and store it in my mysql database (two databases are involved here one is external for which i only have read access); I have established the connection with MS SQL server with tinytds and activerecord-sqlserver-adapter , but when i run User.first or User.find command on console its returning empty object( #< machinerawpunch > )

And i m establishing connection in my model class. I created 2 different models one is for MS SQL table and other is my rails mysql which is normal. My model class is here

class MachineRawPunch < ActiveRecord::Base
  ActiveRecord::Base.establish_connection :savior721
  self.table_name = 'machinerawpunch'
end

My database.yml file is

 savior721:
   adapter: sqlserver
   mode : dblib
   dataserver: 192.68.1.2\SQLEXPRESS
   database: savior721
   username: user
   password: user

default: &default
  adapter: mysql2
  encoding: utf8
  pool: 5
  username: root
  password: root
  socket: /var/run/mysqld/mysqld.sock

development:
  <<: *default
  database: assigment_app_development

test:
  <<: *default
  database: assigment_app_test

Please help me find the right way to access SQL server.That is i dnt want an empty array of object i need data which i can manupulate.A way which would return some thing like this #

how are you establishing the connection ? are you running RAILS_ENV=savior721 or something similar?, and where do you want to establish the connection, in your app? in rake task ? at the console? Does your SQL Server have a users table the follows rails conventions? — Doon
– Doon, Commented Aug 4, 2014 at 14:39
can you show the rails / dev logs of the queries being created when you query the sql server — Doon
– Doon, Commented Aug 4, 2014 at 18:00

Doon · Accepted Answer · 2014-08-04 15:37:44Z

1

The way I normally do this is to synchronize data in a rake task.

so for example in lib/tasks/import_legacy.rb

namespace :admin do
   def connect_legacy_db
    require 'tiny_tds'
    config = Rails.configuration.database_configuration['legacy']
    TinyTds::Client.new :host=>config['host'], :username=>config['username'], :password=>config['password'], :database=>config['database']
  end

 desc "import all users from existing legacy db"
 task :import_all_users => [:environment ] do
    start=Time.now
    puts "Starting  user import at #{start}"
    client = connect_legacy_db
    # then my queries / transforms here such as
    query="EXEC sp_user_migration;"
    res = client.execute query
    res.each do |row|
        User.create name: row['username'] 
    end
  end
end

and my database.yml has the config for the legacy in there. I pulled the connect_legacy_db out to a method since it gets called from most of the tasks in there to sync various things over to the rails app. My existing database doesn't come close to rails conventions, it was easier to just write stored procs to handle the data migration tasks, and use TinyTDS directly as opposed to the activerecord-sqlserver-adapter stuff.

answered Aug 4, 2014 at 15:37

Doon

20.3k3 gold badges42 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jaiswal Over a year ago

Cannot use stored procedure i have read only access to database.

Doon Over a year ago

You don't have to use Stored Procs, I was just showing how I did it, you can easily write query="Select * from users;" or any other valid query, to get your data back, as your question doesn't show the intended usage of the data I was showing one method of doing it..

jaiswal Over a year ago

just want normal query result something like #<MachineRawPunch id: nil, name: nil, email: nil, created_at: nil, updated_at: nil> ;

jaiswal · Accepted Answer · 2017-09-15 10:10:40Z

0

okay guys thanks for answering, and sorry for addressing this late; but the bug was with gem activerecord-sqlserver-adapter it wasn't working well with rails 4. after switching to rails 3.2, all was working properly.

edited Sep 15, 2017 at 10:10

answered Dec 25, 2014 at 13:58

jaiswal

1299 bronze badges

Collectives™ on Stack Overflow

Read data from MS SQL server Database in rails ubuntu

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related