SQL Server via DBI/ODBC configuration

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:in `synchronize'   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:in `sqlserver_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:in `new_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)

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

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:in `synchronize'   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:in `sqlserver_connection'

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

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.

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

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