SQL Server via DBI/ODBC configuration


#1

I have followed the cookbook on
http://wiki.rubyonrails.org/database-support/ms-sql
but am having problems with the rails part. I can connect to the
database using sqsh so I think all the freetds plumbing is good. When
I try to connect to the db with a rake db:migrate or just using script/
console I get this error:

Client.find(:first)
DBI::InterfaceError: Unable to load driver ‘ODBC’
from /usr/lib/ruby/gems/1.8/gems/dbi-0.4.0/lib/dbi.rb:286:in
load_driver' from /usr/lib/ruby/1.8/monitor.rb:242:insynchronize’
from /usr/lib/ruby/gems/1.8/gems/dbi-0.4.0/lib/dbi.rb:236:in
load_driver' from /usr/lib/ruby/gems/1.8/gems/dbi-0.4.0/lib/dbi.rb:154:in_get_full_driver’
from /usr/lib/ruby/gems/1.8/gems/dbi-0.4.0/lib/dbi.rb:139:in
connect' from /usr/lib/ruby/gems/1.8/gems/activerecord-sqlserver- adapter-1.0.0.9250/lib/active_record/connection_adapters/ sqlserver_adapter.rb:47:insqlserver_connection’
from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/
connection_adapters/abstract/connection_pool.rb:223:in send' from /usr/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/ connection_adapters/abstract/connection_pool.rb:223:innew_connection’

database.yml is:

development:
adapter: sqlserver
mode: ODBC
dsn: CONTENT_STORE
username: joebob
password: secret

So I looked into dbi.rb and I see where it throws the exception. It
can’t seem to get the Driver called ODBC. I do have dbd-odbc-0.2.4
installed. Seems like the dbi part is not connecting to the odbc
part.

Rails is running on ubuntu 8.1.

Any pointers would be very welcome.

Sincerely,

Mike Vargo

*** LOCAL GEMS ***

actionmailer (2.3.2)
actionpack (2.3.2)
activerecord (2.3.2)
activerecord-sqlserver-adapter (1.0.0.9250)
activeresource (2.3.2)
activesupport (2.3.2)
capistrano (2.5.5)
cgi_multipart_eof_fix (2.5.0)
daemons (1.0.10)
dbd-odbc (0.2.4)
dbi (0.4.0)
deprecated (2.0.1)
fastthread (1.0.7)
gem_plugin (0.2.3)
highline (1.5.0)
linecache (0.43)
mongrel (1.1.5)
net-scp (1.0.2)
net-sftp (2.0.2)
net-ssh (2.0.11)
net-ssh-gateway (1.0.1)
rails (2.3.2)
rake (0.8.4)
ruby-debug-base (0.10.3)
ruby-debug-ide (0.4.5)
rubygems-update (1.3.1)
sqlite3-ruby (1.2.4)


#2

Try this gem out, works for me I think your on the right path…
http://github.com/rails-sqlserver/2000-2005-adapter/tree/master


#3

Dear Chris,

Thank you for reponding. I tried that. I get the same error from a
different stack. It finds the new sqlserver.rb from the gem you
suggested. But it still barfs in DBI because it can’t find the ODBC
driver. I wonder if it’s some kind of path thing for finding the dbd-
odbc thing. It’s beyond my rails capabilities to figure out how the
gems find each other. I’m sure it’s some lame thing (it always is!).

Any other advice would be greatly appreciated.

Mike

subscriber = Subscriber.find(1)
DBI::InterfaceError: Unable to load driver ‘ODBC’
from /usr/lib/ruby/gems/1.8/gems/dbi-0.4.0/lib/dbi.rb:286:in
load_driver' from /usr/lib/ruby/1.8/monitor.rb:242:insynchronize’
from /usr/lib/ruby/gems/1.8/gems/dbi-0.4.0/lib/dbi.rb:236:in
load_driver' from /usr/lib/ruby/gems/1.8/gems/dbi-0.4.0/lib/dbi.rb:154:in_get_full_driver’
from /usr/lib/ruby/gems/1.8/gems/dbi-0.4.0/lib/dbi.rb:139:in
connect' from /home/mvargo/.gem/ruby/1.8/gems/rails-sqlserver-2000-2005- adapter-2.2.15/lib/active_record/connection_adapters/ sqlserver_adapter.rb:26:insqlserver_connection’


#4

Mike, in my experience this has been painful. Here’s a few things to
look for:

First, make sure that TDS is set up fine. Verify it with this command:
tsql -S server -U user -P password

Make sure you have ruby-odbc to have ruby load the ODBC driver (think
this could potentially solve your issue). My instructions on a fedora
box are:
RubyODBC:
wget http://ch-werner.de/rubyodbc/ruby-odbc-0.9996.tar.gz
tar -xvzpf ruby-odbc-0.9996.tar.gz
cd ruby-odbc-0.9996
ruby extconf.rb
make
sudo make install

Test it out again. If you have a problem with the data source, a way
to test it directly is:

isql -v datasource username password

Hope that helps,
-Harold


#5

mvargo:

Did you have any luck with this?

I can get freetds and iodbc to connect successfully but ruby-odbc just
doesn’t seem to support a shared (non-root access) linux installation
like everything else.

I’m blocked as make install tries to put the files in the standard
locations and to make this work I need to use a --prefix= type
install. Looking through the makefile, I just didn’t seem to see any
support for this.

Rob

P.S. Sorry for the slight hijack - but as long as you have root access
this looks to be pretty reliable to install and get working.


#6

There’s a rails-sqlserver google group:
http://groups.google.com/group/rails-sqlserver-adapter. You could try
asking your question there as the group is more focussed to your setup.

From what I recall I think that this problem is related to ruby DBI &
ruby
odbc version clashes.

Cheers,

Murray

2009/5/5 Rob removed_email_address@domain.invalid


#7

Mike,

Did you have any luck with this? Whats described by Harold seems to
match everyone elses experience.

By any chance would you be on a server for which you don’t have root
access? I have no troubles with freetds and iodbc getting to the SQL
Server that I’m intending to use, but I cannot get either ruby-odbc or
rails-sqlserver2000-2005-adapter to install at all on a shared linux
host.

Rob