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

Malcare WordPress Security