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.

Unknown 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

ri-aa-test 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

Unknown 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!

Unknown 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.

jessica said...

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

Unknown 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.

Unknown 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.

Unknown 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

Unknown 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.

Unknown said...

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

Sankar 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

Tejuteju 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.
Unknown 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

ptiacademy said...

Your blog post is amazing and full of knowledge, thanks for publishing your post. I want to tell you everyone that I also provide a Customize Ruby On Rails Services at a very prominent price.

Benish said...

thank you for sharing your information..
AngularJS interview questions and answers/angularjs interview questions/angularjs 6 interview questions and answers/mindtree angular 2 interview questions/jquery angularjs interview questions/angular interview questions/angularjs 6 interview questions

Venkatesh CS said...

Excellent Blog. Thank you so much for sharing.
best react js training in chennai
react js training in Chennai
react js workshop in Chennai
react js courses in Chennai
react js training institute in Chennai
reactjs training Chennai
react js online training
react js online training india
react js course content
react js training courses
react js course syllabus
react js training
react js certification in chennai
best react js training

Benish said...

Really nice post. Thank you for sharing amazing information.
Python training in Chennai/Python training in OMR/Python training in Velachery/Python certification training in Chennai/Python training fees in Chennai/Python training with placement in Chennai/Python training in Chennai with Placement/Python course in Chennai/Python Certification course in Chennai/Python online training in Chennai/Python training in Chennai Quora/Best Python Training in Chennai/Best Python training in OMR/Best Python training in Velachery/Best Python course in Chennai

Venkatesh CS said...

Thanks for sharing valuable information.
Digital Marketing training Course in chennai
digital marketing training institute in chennai
digital marketing training in Chennai
digital marketing course in Chennai
digital marketing course training in omr
digital marketing certification in omr
digital marketing course training in velachery
digital marketing training center in chennai
digital marketing courses with placement in chennai
digital marketing certification in chennai
digital marketing institute in Chennai
digital marketing certification course in Chennai
digital marketing course training in Chennai
Digital Marketing course in Chennai with placement
digital marketing courses in chennai

jose said...

Really nice post. Thank you for sharing amazing information.
Java Training in Credo Systemz/Java Training in Chennai Credo Systemz/Java Training in Chennai/Java Training in Chennai with Placements/Java Training in Velachery/Java Training in OMR/Java Training Institute in Chennai/Java Training Center in Chennai/Java Training in Chennai fees/Best Java Training in Chennai/Best Java Training in Chennai with Placements/Best Java Training Institute in Chennai/Best Java Training Institute near me/Best Java Training in Velachery/Best Java Training in OMR/Best Java Training in India/Best Online Java Training in India/Best Java Training with Placement in Chennai

Sravan Reddy said...

Very Nice And Very Informative Blog Form More ruby on rails online training

svrtechnologies said...

Thanks for the Informative Stuff...

oracle apex training

Jack sparrow said...

This post is really nice and informative. The explanation given is really comprehensive and informative . Thanks for sharing such a great information..Its really nice and informative . Hope more artcles from you. I want to share about the best learn java with free bundle videos provided and java training .