CODEWITHSUNDEEP
ruby-on-railssql-serveractiverecordodbc
Ganesh Shankar
Ganesh Shankar

Reputation: 4864

Connecting to an MSSQL database from a Ruby on Rails application running on Ubuntu

I have a situation where I'm trying to build a web app which takes a total count of records in a table and outputs it to the screen. Sounds simple right...?

The main problem I'm having is that the DB I want to look at is MSSQL. I haven't set up this kind of DB connection from Rails before so I was hoping someone could point me in the right direction.

My RoR application will live on a Ubuntu server (and is being developed on a OSX Leopard system).

EDIT:

Thanks!

Upvotes: 6

Views: 15794

Answers (7)

dimitry_n
dimitry_n

Reputation: 3019

A gem called active-record-sql-adapter lets you connect to an SQL Server db via ActiveRecord. You can do something like

class RemodeDB
  establish_connection(:remote_db) #<=
  self.abstract_class = true # to avoid Rails' no associated model exception
end

then have your classes inherit the connection like so

class Product < RemoteDB
   self.table_name ... 
end

Note that in order to connect to earlier versions of SQL Server (2005 in your case), you would need an earlier version of the gem which may not be compatible with your current version of Rails.

Upvotes: 0

fmnoise
fmnoise

Reputation: 109

I used activerecord-sqlserver-adapter with tiny_tds and it works!

Here's database.yml 

development:
  adapter: sqlserver
  username: 'user'
  password: 'secret'
  dataserver: 'dbserver_name\instance_name'
  database: 'dbname'
  appname: 'my app name'

Upvotes: 0

Paulo Fidalgo
Paulo Fidalgo

Reputation: 22331

Take a look at an example I've made on how to use the mentioned actionrecord-sqlserver adaptor here

You can use mappings with rails models and use the ActiveModel helpers.

Upvotes: 2

Cody
Cody

Reputation: 1

I used Sequel with DBI and ODBC and it seems to be working.

require "dbi"
require "sequel"
Sequel.datetime_class = DateTime
p "testing dbi"
# to setup a DSN, Start->Settings->CtlPanel->AdminTools->DataSources
conn = DBI.connect('DBI:ODBC:dsn')
p conn.connected?
p conn.select_one("SELECT @@VERSION")
conn.disconnect
p "testing sequel"
db = Sequel.odbc('(odbc_dsn_goes_here)', :db_type=>'mssql')
db.fetch("SELECT TOP 2 * FROM TABLE") do |row|
    p row
end

Upvotes: 0

wantrapreneur
wantrapreneur

Reputation: 420

This gem is great and easy to set up: https://github.com/rails-sqlserver/activerecord-sqlserver-adapter

Upvotes: 0

David Barlow
David Barlow

Reputation: 4974

I'll have a stab, and say that you'll probably need to connect to a mssql DB via ODBC. There appears to be a few gems that do this. hopefully one of them will have docs to put you on the right track.

ODBC rubygems

Upvotes: 0

Clinton
Clinton

Reputation: 3648

This Stackoverflow question might help: Rails & MSSQL 2008 - Will We Hit Barriers?

Basically you will need to install a MSSQL database adapter (rather than MySQL or Postgres that most tutorials step you through), and configure your database.yml appropriately:

http://rorblog.techcfl.com/2008/04/14/ruby-on-rails-connection-to-sql-server/

http://the-banana-peel.saltybanana.com/2008/06/connecto-to-microsoft-sql-server-from.html

http://wiki.rubyonrails.org/database-support/ms-sql (Although the rails wiki looks down at time of writing)

P.S. I am assuming the MSSQL server will be running on a separate Microsoft server someplace.

Upvotes: 8

Related Questions