website designer singapore,developers in singapore,website design singapore,web development singapore,ios app development singapore,singapore mobile app developer,singapore web design services,singapore web design,singapore web development,mobile app development singapore,android developer singapore,web design services singapore,website development singapore,mobile application developer singapore,mobile developer singapore,mobile app developer singapore,singapore app developer,ios developer singapore,singapore mobile application developer,website developer singapore,design agency singapore,app development singapore,mobile game developer singapore,web design singapore,web design company singapore,mobile apps singapore,mobile application development singapore,singapore website design,web development company singapore,web designer singapore,app developer singapore,graphic designer in singapore,design firms in singapore,web application singapore,mobile apps development singapore,developer in singapore,ruby on rails developer singapore

Using ActiveRecord to access Microsoft SQL Server via ODBC

Recently I need to insert 400+ rows of data into an eight year old MSSQL 2000 and MSSQL 2008 database. It’s really a pain in the ass if I have to do it by hand or learning a new tool to do it. Luckily, I can use ActiveRecord to do that easily thanks to activerecord-sqlserver-adapter and and auto table field mapping of ActiveRecord.

Here is a quick step-by-step guide:

Install Ruby on Windows machine (that run SQL server). Download One-Click Ruby Installer for Windows for Ruby 1.8.6 P26 at
http://rubyforge.org/frs/download.php/29263/ruby186-26.exe

And install it. If you use the default setting, it will setup ruby at c:ruby folder with rubygem 0.9.3

Update RubyGems

Open “Command Prompt”

cd c:rubybin gem update –system

Install ActiveRecord with SQLServer Adapter

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

If the installation result is:

Successfully installed activesupport-2.2.2 Successfully installed activerecord-2.2.2 Successfully installed activerecord-sqlserver-adapter-1.0.0.9250 3 gems installed

Create a DSN (Data Source Name) Luckily I can reuse DNS from current ASP project so I don’t need to create new one. If you have to, you can follow guideline in [1].

Setup connection and create property ActiveRecord based classes

require “active_record”
ActiveRecord::Base.establish_connection(

:adapter => “sqlserver”,
:mode => “odbc”,
:username => “yourusername”,
:password => “yourpassword”,
:dsn => “yourDSN”

)

class AnyNameYouWant < ActiveRecord::Base
set_table_name “TableNeedToAccess”
set_primary_key “PrimaryID”
end

Start irb by:

cd c:rubybin irb

And paste above chuck of code in to irb. From now, you can use ActiveRecord based classes to read/write/add/remove SQL Server table as in a Rails script/console environment.

I also got a problem with Time.mktime and need to some code adjust by hand

Open

c:rubylibrubygems1.8gemsactiverecord-sqlserver-adapter-1.0.0.9250libactive_recordconnection_adapterssqlserver_adapter.rb

Replace “Time.mktime” by “Datetime.new” will fix the problem [2]

References

[1] http://piao-tech.blogspot.com/2008/02/using-activerecord-with-microsoft-sql.html
[2] http://papillon.nwnx.org/index.php/archives/19

%d bloggers like this:
WordPress