Tuesday, March 25, 2008

Rails 2 and SQL Server 2008 on Windows made simple

I always wondered if rails was really working well with SQL Server. A lot of posts on the web concern every database on earth excepted SQL Server. Most posts concerning SQL Server and Rails are written for previous version of rails and deal with specific issues. And sorry, but the official documentation is the jungle ;-)

So I thought it was time for a simple tutorial on how to use SQL Server and rails 2.0 under MS Windows. The goal of this tutorial is to create a basic CRUD application from scratch. The targeted audience is 0 or little knowledge of rails. I'm using SQL Server 2008 CTP6 but this works as well with other versions of SQL Server (2005 or 2000).

After this simple tutorial, I'll try to go more into details in subsequent posts.

First of all, we have to create our application environment :

1. Open a ruby console, go to your rails_apps directory and type

rails sqlserverapp

This will create your application file (you should see a lot of files preceded by the create statement).

Then you have to install the SQL Server adapter which was removed from the standard rails 2.0 distribution.

2. In the ruby console, type :

gem install activerecord-sqlserver-adapter --source=http://gems.rubyonrails.org

If you have an error message, try to turn off temporarily the windows firewall (the installation won't take more than a few minutes).

Then you have to create a database on your SQL Server

3. Open your SQL Server client tool and create a database called sqlserverapp

4. Add a user with SQL Server authentication called user with secret as password to the database you just created.

5. Create an ODBC link name sqlserverapp to connect to this database.

Then you have to edit your database configuration file to tell rails to use SQL Server and your new database.

6. In the rails console, go to the configuration directory and edit the database.yml file.

cd configuration
edit database.yml

Change the section under development with the following :

adapter: sqlserver
mode: odbc
dsn: sqlserverapp
username: user
password: secret
host: mycomputer

Note : replace mycomputer with the name of your instance of SQL Server (in most cases, locahost should do the job).

Repeat the process for the test and production database if needed.

Then the basic configuration is done. We can start writing the application.

7. In the rails console, to generate the scaffold type :

ruby script/generate scaffold Client name:string address:string email:string remark:text

And ... that's almost finished ! This instruction has done all the work, i.e. created the views, prepared your model, your controller, your test files etc...

You just have to run your db migrations and start your server now.

8. In the rails console, type:

rake db:migrate

This will create your model into SQL Server. If you look into your SQL Server database you should have a table named clients with the columns stated in your scaffold plus two columns for timestamps and one for the primary key which are added automatically.

The last thing you have to do now is to start the web server and test your application.

9. In the rails console, type:

ruby script/server

10. Open your web browser and type:


You should now see a form where you can edit your first client. You can also edit/update/delete any client straight away out of the box.

Yes Ruby on rails is magic ! Remember that the whole application took you 2 lines and 10 minutes to write (spending most of the time reading and watching rails work for you).

So now I can say it ! YES, rails does work simply and efficiently with SQL Server 2008 and active records. It's even very simple to use. I no longer will be a frustrated rails and SQL Server user !


cona said...

please help me

when do the step:rake db:migrate

no such file to load -- dbi
rake db:migrate
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:migrate
rake aborted!
no such file to load -- dbi
/usr/local/lib/site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
/usr/local/lib/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
/usr/lib/ruby/gems/1.8/gems/activesupport-2.1.2/lib/active_support/dependencies.rb:510:in `require'

why? Thank U very much, and you have done the good blog, i have learned much more about rails.

Vincent Jacquel said...

Did you install the SQL-Server adapter ? Did you set-up the odbc ?

Noli San Jose said...

thanks for the tip. i was able to make it work. however, what do you need to do to make it work using the ADO. It seems all the tutorial on using ADO does not work.

Derek said...

I am newbie to SQL and Rails

I have followed your in instructions but I am getting this error:

>ruby script/generate scaffold Client name:string address:string email:string remark:text
c:/ruby/lib/ruby/1.8/yaml.rb:133:in `load': syntax error on line 3, col 5: ` dsn: sqlserverapp' (ArgumentError)
from c:/ruby/lib/ruby/1.8/yaml.rb:133:in `load'
from c:/ruby/lib/ruby/gems/1.8/gems/rails-2.2.2/lib/initializer.rb:819:in `database_configuration'
from c:/ruby/lib/ruby/gems/1.8/gems/rails-2.2.2/lib/initializer.rb:391:in `initialize_database'
from c:/ruby/lib/ruby/gems/1.8/gems/rails-2.2.2/lib/initializer.rb:139:in `process'
from c:/ruby/lib/ruby/gems/1.8/gems/rails-2.2.2/lib/initializer.rb:112:in `send'
from c:/ruby/lib/ruby/gems/1.8/gems/rails-2.2.2/lib/initializer.rb:112:in `run'
from C:/Users/ddamko/Documents/Aptana Studio/sqlserverapp/config/environment.rb:13
from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
from c:/ruby/lib/ruby/gems/1.8/gems/rails-2.2.2/lib/commands/generate.rb:1
from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
from script/generate:3

albrad84 said...

Hey Vincent, I am not sure what you mean by "Create an ODBC link name sqlserverapp to connect to this database". I only know the basics about MS Sql Server.


chrisc said...

I get a similar problem to cona,
rake db:migrate

(in C:/dev/sqlserverapp)
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:migrate
rake aborted!
no such file to load -- deprecated
C:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
C:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in `require'

There is lots more error info

I have got database set up and tested odbc connector and installed sql server adapter. In fact until all this worked I did not get a file to migrate.

(ruby 1.8.6)(rails 2.3.2)(active-record-sqlserver-adapter

bbryant said...

As an alternative...

1. install activerecord sqlserver gem as above

2. create the database on your server

3. use the following for the database.yml

adapter: sqlserver
mode: odbc
dsn: Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=rails_database_name;

Where SQLEXPRESS is replaced with your SQL Server instance, and rails_database_name is the name of the database you creasted

Note: this assumes you are using Integrated Security for your sql server, you will need to addjust the connection string (dsn) accordingly for your setup.

This works under a normal windows rails install, as well as a cygwin installation

I Varandas said...

Is it possible to connect to a sql server in another machine?

Michael Ceranski said...

I wrote a tutorial which explains how to use RoR with SQL Server 2008. The tutorial is geared for .NET developers: A Ruby on Rails Tutorial for .NET Developers

Arjan said...

Thank you for the clear documentation. After visiting a lot of websites without result your post did the job immediately. THANKS!!!!

Courtney said...

I just tried this, and I am connecting to a SQL server that is not the localhost. I can do each step, but when I do the rake db:migrate I get this error:

rake aborted!
TypeError: Cannot load `Rails.application.database_configuration`:
can't convert nil into Hash
C:/Users/myname/atdapp/config/environment.rb:5:in `'
Tasks: TOP => db:migrate => environment
(See full trace by running task with --trace)

Any idea what is wrong? I'm trying to learn this process, so I can create a web app that connects to my SQL db. Thanks!

Georgia Lawrence said...

To activate SQL Server, you can visit this site: www.cdekey.com, to got an activation key, it is genuine and the price is low.


Just wanted to recommend cheap windows 10 product key sale
I have helped people "who cannot afford" computing, refurbish old systems donated by schools and http://www.Buywindows10keysale.com/ has really enabled me to give these people good software that would normally be out of their reach ( I don't charge for my service,) just thought I would pass the word on

Anudv52 Tfppq86 said...

I just got a product key from www.vinhugo.com. it worked perfectly. I'm so excited to share my experience here.their customer service is 1st class and I will definitely be recommending the site and I will be using the site again.

Brayden Barnell said...

The points given in this blog for leaning ruby on rails are very useful and important to become perfect in ruby on Rails developers.Thanks for sharing such a helpful tips.

Crish Watson said...

I found this piece of information very useful. I real so many articles on ruby on rails and SQL server but nothing helped me more than this blog post. Thank you for sharing this interesting information.

Crish Watson
Pass Microsoft Certification Without Taking Exam

mayra crespo said...

If you need cheap windows product key,you can go to: www.softkeyhome.com to got. My key bought here. It's working great and cost me a little money.

Janvi Sanvi said...

It is nice post on Ruby on Rails Test pride .Keep updating the postRuby on Rails Online Training

Sankar lp said...

Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here.
Kindly keep blogging. If anyone wants to become a Front end developer learn from javascript and jquery training in chennai .
or learn thru Javascript Training in Chennai.
Nowadays JavaScript has tons of job opportunities on various vertical industry. javascript and jquery training in chennai

Teju Teju said...

It was really a nice article and I was really impressed by reading this Ruby on Rails Online Training India

harikasri.blogspot.com said...
This comment has been removed by the author.
sudha P said...

It is better to engaged ourselves in activities we like. I liked the post. Thanks for sharing.
Java training in Chennai

Java training in Bangalore

sathish said...

The post is written in very a good manner and it entails many useful information for me. I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement the concept.

Oracle Training in chennai
web designing training in chennai
web designing training in chennai | angularjs training in chennai
sas training institute in chennai
software testing training in chennai

service care said...

I am really enjoyed a lot when reading your well-written posts. It shows like you spend more effort and time to write this blog. I have saved it for my future reference. Keep it up the good work.
mobile service center chennai
mobile service centre near me
mobile service centre chennai
best mobile service center in chennai
best mobile service center in chennai

kavithasathish said...

A good blog for the people who really needs information about this.
Good work keep it up.
TOEFL Coaching in Chennai
TOEFL Classes in Chennai
German Classes in Chennai
IELTS Coaching in Chennai
Japanese Classes in Chennai
spanish language in chennai
TOEFL Coaching in OMR
TOEFL Coaching in Porur