web development singapore,singapore app developer,singapore web design,mobile app development singapore,design firms in singapore,app development singapore,mobile developer singapore,mobile application developer singapore,graphic designer in singapore,mobile game developer singapore,web development company singapore,mobile application development singapore,web designer singapore,singapore website design,developers in singapore,android developer singapore,app developer singapore,website design singapore,developer in singapore,website designer singapore,web design services singapore,ios app development singapore,website developer singapore,mobile apps development singapore,mobile app developer singapore,singapore mobile application developer,design agency singapore,ios developer singapore,web design company singapore,ruby on rails developer singapore,website development singapore,singapore mobile app developer,singapore web design services,singapore web development,mobile apps singapore,web application singapore,web design 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