Well I think you can understand the feeling of struggling in so many posts to reference in the internet, while after following the steps of them, you really failed every time. Even with the brief instruction on the official page. I know every detailed setting of the environment may affect the final result, whether it’ll succeed or not. However, just to put down the overall process which I have gone through and brought about good news.
For Ruby 1.8, you can access SQL Server using ADO, however, this is not smoothly supported in Ruby 1.9. There are many methods and suggestions talking about ‘gem install ruby’ first, then copy the ado.rb file from an earlier-version ruby package. Anyway, it didn’t work for me. And also, some people said it’s easy to use Tiny_tds to connect to SQL Server. And it didn’t work for me as well. So at last, I succeeded with ODBC.
1. Install Ruby. my version is Ruby 1.9.2.
2. Most of this part are from Thedwick, I just added some words of my own in blue color about my adjust during following this process.
Secondly, and this is very important, I have DevKit installed.
I downloaded http://github.com/downloads/oneclick/rubyinstaller/DevKit-4.5.0-20100819-1536-sfx.exe and installed it according to the instructions on the DevKit wiki. Many of the tutorials out there neglect to mention that you must have DevKit installed in order to install some of the gems required.
I have to enhance once again that it’s very important, I don’t want you to experience a lot again as me. Being distracted by other directions is not always a good choice, but we usually have no choice at the very beginning:) DevKit is helpful because when you use it to gem install a software package, it can decide which dependencies this software package needs, fetch down these packages and bind the relationship between them for you. If we install this software manually by ourselves, the relationship will not be bound and sometimes we can’t realize which steps we have missed. So a weird situation occurs. I install dbi library. I also install adaptsqlserver. Then an error told me the adapter can’t be loaded. Well, I know the program can’t find where the adapter is, but even after I tracked deeper into the source code of ruby program in some way, I still can’t find where I can tell the program how to find the adapter. So remember DevKit can help you to figure out unexpected problems and save much time for you.
The instructions I’m giving you here only work with *exactly* the setup I described. If you have a slightly different setup then this might not work. It’s very finicky. Even having a different one click installer can make it not work.
Step By Step From a Fresh Ruby Install
I installed my ruby to d:\Ruby191 with the one click installer. Then I extracted DevKit to d:\Ruby191\devkit and ran:
cd d:\Ruby191\devkit
ruby dk.rb init
ruby dk.rb review
ruby dk.rb install
When running “ruby dk.rb review” make sure it says it’s pointing to your Ruby install.
Well, just a kind reminder here. If you type ‘ruby’, and the system prompts it’s not a recognized command, it’s because of the reason that the full path of ruby.exe has not been added to the system path. You can also type the full path of ruby.exe instead.
And when I checked the ‘config.yml’ file, I found there is no content there. Normally, my installed ruby location should be list there. Relatively, ruby dk.rb review will also reports that there is no ruby found. I estimated it’s because my installed ruby is on a network shared disk. It may not be checked by dk.rb. So I need to add my ruby location to this file manually. The format is as follows.
— # don’t forget this part
- S:/Ruby192
Next I downloaded dbi-0.4.3.gem, dbd-odbc-0.2.5.gem and ruby-odbc-0.99992.gem to my local file system.
Next, I ran exactly these commands. The versions and extra arguments matter!set RUBY_HOME=D:\Ruby191
set LIBS=..\..\libs\ruby
gem install rake
gem install rubygems-update
gem install --include-dependencies rails I failed with this step becuase it is said that a ruby version greater than 1.9.3 is needed. The final result showed this step will not take big impact on the overall process. So just ignore it if you meet with them.
gem install deprecated --version=2.0.0 @rem the version matters. get exactly this one
gem install --local %LIBS%\dbi-0.4.3.gem
gem install --local %LIBS%\dbd-odbc-0.2.5.gem
gem install --local %LIBS%\ruby-odbc-0.99992.gem @rem this requires devkit
Lastly, I wrote this sanity check script and it worked:
require 'dbi'
# Replace MY_DSN with the name of your ODBC data
# source. Replace and dbusername with dbpassword with
# your database login name and password.
DBI.connect(‘dbi:ODBC:MY_DSN_NAME’, ‘username’, ‘password’) do | dbh |
# Replace mytable with the name of a table in your database.
p “selecting getdate”
dbh.select_all(‘select getdate()’) do | row |
p row
end
p “done”
end
Well, here is also a tricky part. You can try a less-DSN method to connect to SQL Server. Because you may need to set an environment variable value of your computer system. So try another method as follows if you find the above method is a little complex. You must specify the Dirver as ‘SQL Server’.
DBI.connect(“DBI:ODBC:Driver={SQL Server};Server=#{servername};Database=#{database};Trusted_Connection=yes”) # for Windows Authentication.
DBI.connect(‘DBI:ODBC:Driver={SQL Server};Server=#{servername};Database=#{database}’,#{username},#{password}) # for Windows Authentication.
DBI.connect("DBI:ODBC:Driver={SQL Server};Server=#{servername};UID=#{username};PWD=#{password}") # for SQL Server Authentication
A little more update here as my colleague used the above second method with username and password to connect to SQL Server and got an error message that 'login failed for user #{user}' and this username is her domain account instead of her designated SQL Server user account. I checked again and found that the reason why I didn't meet with this problem was because I once added my domain account to SQL Server security logins. So it was expected that the input username was used to connect to SQL Server, but actually the default domain account was used! The new added third method colored in red is the correct connection string for SQL Server Authentication which has been verified.
That’s done! Have fun and good luck!
