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 :


development:
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:

http://localhost:3000/clients

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 !

53 comments:

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.

Thanks!

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 1.0.0.9250)

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

development:
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...

Buywindows10keysale.com
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...

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.

Regards,
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

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

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.

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 .

hami said...

Click here to tech updates.
............................................................

Anand Shankar said...
This comment has been removed by the author.
onesiti said...

This is a topic that's near to my heart... free Take care! Exactly where are your contact details though?

aj said...

Snapdeal Prize list and Snapdeal prize department. Here you can win the exciting prizes and the special offer just playing a game. For more information visit our website: Snapdeal lucky customer.
Snapdeal winner name 2020
Snapdeal lucky draw
Snapdeal lucky customer 2020
Snapdeal winner name list

Joyal said...

Effective blog with a lot of information. I just Shared you the link below for Courses .They really provide good level of training and Placement,I just Had Ruby on Rails Classes in this institute , Just Check This Link You can get it more information about the Ruby on Rails course.


Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

Unknown said...

I have recently visited your blog profile. I am totally impressed by your blogging skills and knowledge.

Salesforce CRM Online Training

Salesforce CRM Classes Online

Salesforce CRM Training Online

Online Salesforce CRM Course

Salesforce CRM Course Online

Unknown said...

Thank you for excellent article.You made an article that is interesting.

Hyper-V Server Virtualization Online Training

Hyper-V Server Virtualization Classes Online

Hyper-V Server Virtualization Training Online

Online Hyper-V Server Virtualization Course

Hyper-V Server Virtualization Course Online

Aditi Gupta said...

I feel very grateful that I read this. It is very helpful with amazing content and I really learned a lot from it. thanks and keep it up. Rajasthan Budget Tours

Pathway for German Language said...
This comment has been removed by the author.
Josh said...

Infertility specialist in chennai
Sexologist in chennai
Sexologist doctor in chennai

David Smith said...


Nice Article. Thank you for sharing the informative article with us.You gave some quality information here that any reader would enjoy. Sharetipsinfo provides latest Indian stock market tips and Live BSE/NSE share market tips
Sensex & Nifty updates.Find the relevant updates regarding Buy & Sell....

Seema said...

feel exceptionally appreciative that I read this. It is exceptionally useful with astounding substance and I truly took in a ton from it. much appreciated and keep it up
Enterprise Web Design Services
Much obliged to you for this incredible piece of substance
Enterprise ECommerce
Enterprise Resource Planning
Human Resource Outsourcing

chellam said...

thanks for sharing an informative article.useful stuff.Angular training in Chennai

Unknown said...

what is contrave

silicon wives

sky pharmacy

atx 101 uk

macrolane buttock injections london

hydrogel buttock injections

buying vyvanse online legit

buy dermal fillers online usa

mesotherapy injections near me

xeomin reviews

BEST IT CERTIFICATIONS ONLINE WITHOUT EXAMS said...

Offensive Security usa you for an increasingly critical field: information security. Our courses focus on real-world penetration testing techniques and methods, preparing you to tackle real-life challenges. Our certifications prove to employers that you have the in-demand skills they need.

OSED certification online.

OSWE certification without exam

osep certificate without exam

OSWP certificate online

OSCE online certification

OSCP certificate without exam online

BEST IT CERTIFICATIONS ONLINE WITHOUT EXAMS said...

CEH certification without exam

cissp certification Canada

<a href="https://specialistcert.com/cissp-certification-luxembourg/“>cissp certification Luxembourg</a>

Certproxies said...

How to pass PMP Exam?

Valluva said...

view 6l splicing machine

Alex said...

"Great Write - Up"
Visit page for more information

Nate Lamaro said...

Very informative one, as it details about the blog and its contents clearly.
For further informations Find Here

Anonymous said...

Amazing write up, For more informations Check Here

Bus rental Dubai said...

SQL server is now more advanced and updated bus rental Dubai

Manisha said...

it is really explainable very well and i got more information from your blog.

Ava Volkov said...

Your ability to give a balanced viewpoint that takes into account other viewpoints and addresses potential problems stands out above all else. DevOps Certification

Ava Volkov said...

You have recently made some very outstanding contributions. Your breadth of knowledge, astute analysis, and lucidity of expression are admirable. Your knowledge enriches our conversations and offers priceless direction. DevOps Course

Ava Volkov said...

You have recently made some very outstanding contributions. Your breadth of knowledge, astute analysis, and lucidity of expression are admirable. Your knowledge enriches our conversations and offers priceless direction. Blue Prism Training

VISWA Technologies said...

Hi Thanks for such a wonderful article, this informative article is a lifesaver for those who want to rank their website in the top search results. Keep sharing! thanks
SAP Ariba Training
OTM Training
Salesforce Vlocity Training
SharePoint Spfx Training

techarinatips said...

Explore unparalleled excellence with Supportfly, your go-to Google Cloud managed services provider. Elevate your cloud experience with top-notch solutions tailored to your needs. Trust us to optimize, secure, and streamline your operations, ensuring peak performance and peace of mind. Unleash the full potential of Google Cloud with Supportfly – your key to efficient, reliable, and cutting-edge managed services.