Ruby Forum Ruby on Rails > Is SQL Server not supported in RoR on the Mac?

Hi -- I've been having problems connecting to a SQL Server database that
I thought might be related to the fact I was upgrading Rails to 2.0.
However, I also was moving the project to my shiny new macbook.
Anyway, after jumping through a few hoops (instaling DBI, etc.), I
finally get to my latest error message:

<error>
DBI::InterfaceError: Could not load driver (no such file to load --
win32ole)
  from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:344:in `load_driver'
  from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:227:in
`_get_full_driver'
  from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:213:in `connect'
  from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-1.0.0/lib/active_record/connection_adapters/sqlserver_adapter.rb:46:in
`sqlserver_connection'
  from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:291:in
`send'
  from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:291:in
`connection='
  from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:259:in
`retrieve_connection'
  from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:78:in
`connection'
  from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:2438:in
`quoted_table_name'
  from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:1353:in
`construct_finder_sql'
  from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:1233:in
`find_every'
  from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:1227:in
`find_initial'
  from
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:502:in
`find'
  from (irb):1
</error>

This seems to suggest that the SQL Server connection won't work on my
Mac, which also suggests that the Unix web server I'm wanting to build
that will connect to
the SQL Server database won't either.
Is this correct?
Can anyone advise any workarounds, or maybe a place to start looking
into fixing this?
Cheers,
   Doug.

PS -- I have no choice but to work with SQL Server.

Hi,

You can't use ADO on anything but Windows, as using ADO depends on 
win32ole.
However, you should be able to find plenty of advice for connecting to 
SQL
Server from OS X, Unix and Linux using ODBC. I haven't done it myself, 
so
I'll leave it to you to search the group or Google or wait for replies 
from
those with experience.

Regards,
Craig

On Fri, Feb 29, 2008 at 11:49 AM, Doug Livesey <

Cheers, Craig -- what I was needing there was a little hope, and you 
just provided it! I'll see what crops up here, and get searching.
Thanks again,
   Doug.

Doug Livesey wrote:
> Cheers, Craig -- what I was needing there was a little hope, and you 
> just provided it! I'll see what crops up here, and get searching.
> Thanks again,
>    Doug.

I connect to SQL Server, from both a Mac, and a redhat box.

Been awhile since I wrote this, but it worked for the last person who 
tried it.

How to get Ruby on Rails (unix / mac) to work with SQL Server

    *

      {*} The bulk of this page is a variation of this Wiki
          o

            http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServerFromRailsOnLinux

This page is getting a bit out of date.

    *

      sudo gem install activerecord-sqlserver-adapter 
--source=http://gems.rubyonrails.org Currently server27 is setup with 
SQL Server and Ruby on Rails

Four pieces of software need to be installed (aside from Ruby and Rails)

   1. Unix ODBC
   2. FreeTDS
   3. Ruby ODBC Driver
   4. Ruby DBI

Download Software

    * Download software from above link.

As installed on a Mac and Redhat Enterprise

Env Variables

run in the console

   export ODBCINI=/etc/odbc.ini
   export ODBCSYSINI=/etc
   export FREETDSCONF=/etc/freetds.conf

Add the following to /etc/profile

  ODBCINI=/etc/odbc.ini
  ODBCSYSINI=/etc
  FREETDSCONF=/etc/freetds.conf

Unix ODBC

./configure --prefix=/usr/local --sysconfdir=/etc --disable-gui
make
make install

Add the following to /etc/odbc.ini

    *

      [aspect]
      Driver          = FreeTDS
      Description     = ODBC connection via FreeTDS
      Trace           = No
      Servername      = aspect
      Database        = cnxrpt_Aspect

      [assurantivr]
      Driver          = FreeTDS
      Description     = ODBC connection via FreeTDS
      Trace           = No
      Servername      = assurantivr
      Database        = AssurantIVR

      [aqm]
      Driver          = FreeTDS
      Description     = ODBC connection via FreeTDS
      Trace           = No
      Servername      = aqm
      Database        = AQM

FreeTDS

./configure --with-unixodbc=/usr/local --sysconfdir=/etc # Mac's and 
most Unix Systems
./configure --with-odbc=/usr/local/lib --sysconfdir=/etc # If RedHat
make
make install

Add the following to /etc/freetds.conf

    *

      #Assurant IVR
      [assurantivr]
          host = orcxsdb001
          port = 1433
          tds version = 8.0
      #AQM (Aspect Managment)
      [aqm]
          host = orcxpdb0aa
          port = 1433
          tds version = 8.0
      #Aspect (Aspect Reporting Database)
      [aspect]
          host = orcxpdb1c2
          instance = SQL
          tds version = 8.0

Add the following to /etc/odbcinst.ini

    *

      [FreeTDS]
      Description     = TDS driver (Sybase/MS SQL)
      Driver          = /usr/local/lib/libtdsodbc.so
      Setup           = /usr/local/lib/libtdsS.so
      CPTimeout       =
      CPReuse         =
      FileUsage       = 1

Test 1

    * tsql -S aspect -U 'workgroups\dsmith' -P fakepassword -I 
/etc/freetds.conf

Test 2

    * isql aspect 'workgroups/dsmith' 'fakepassword'

Ruby ODBC Driver

tar -xzf ruby-odbc-0.9995.tar.gz
cd ruby-odbc-0.9995
ruby extconf.rb
make
make install

Ruby DBI

tar -xzvf dbi-0.1.1.tar.gz
cd ruby-dbi
ruby setup.rb config --with=dbi,dbd_odbc
ruby setup.rb setup
ruby setup.rb install

Test Everything

    *

      irb
      # irb1.8
      irb(main):001:0> require "dbi"
      => true
      irb(main):004:0> db = 
DBI.connect('dbi:ODBC:aspect','workgroups\dsmith','fakepassword')
      => #<DBI::DatabaseHandle:0xb7c3fb60 
@trace_output=#<IO:0xb7f5b024>, @trace_mode=2, 
@handle=#<DBI::DBD::ODBC::Database:0xb7c3fac0 @attr={}, 
@handle=#<ODBC::Database:0xb7c3fae8>>>
      irb(main):005:0> quit

Ruby can now talk to SQL Server

Testing Rails

    *

      rails deleteme
      cd deleteme
      ruby script/generate model Aspect
      ruby script/generate controller aspect index

vim app/models/aspect.rb

    *

      class Aspect < ActiveRecord::Base
        set_table_name "cnxrpt_CallCenterCallDetail"
        self.primary_key="CallCenterCallDetail_id"
        self.establish_connection(:adapter => "sqlserver",
            :mode => "odbc",
            :dsn => "aspect",
            :username => 'workgroups\dsmith',
            :password => 'fakepassword')

            def self.names
              self.content_columns.collect {|x| x.name}
            end

      end

vim app/controllers/aspect_controller.rb

    *

      class AspectController < ApplicationController
              def index
                      @data = Aspect.find(:first)

              end
      end

vim app/views/aspect/index.rhtml

    *

      <h1>First row </h1>
      <table bgcolor="#000000" cellspacing=1>
      <% Aspect.names.each do |name| -%>
        <tr>
              <th bgcolor="#DDDDDD"><%=name -%>
              <td bgcolor="#FFFFCC"><%=@data.send(name) -%>
      <% end -%>
      </table>

ruby script/server -p 5312 -d

Vist http://server27:5312/aspect

Thanks massively for that!
I guess I know what I'm gonna be doing for the next few days ... Tearing 
my hair out!
Cheers, & I'll post how I get on (although don't hold your breath!).
   Doug.

Hi -- sorry if I'm being really dumb, but I'm rather an OSX & *nix 
newbie.
I've been following your recommendations to the letter, and have got to 
the Test 1 and Test 2 bit.
When I run these tests, I get the following:

<output>
etc 520 $ tsql -S aspect -U 'workgroups\dsmith' -P fakepassword -I 
/etc/freetds.conf
locale is "en_GB.UTF-8"
locale charset is "UTF-8"
There was a problem connecting to the server
etc 521 $ isql aspect 'workgroups/dsmith' 'fakepassword'
dyld: lazy symbol binding failed: Symbol not found: _tds_alloc_context
  Referenced from: /usr/local/lib/libtdsodbc.so
  Expected in: flat namespace

dyld: Symbol not found: _tds_alloc_context
  Referenced from: /usr/local/lib/libtdsodbc.so
  Expected in: flat namespace

Trace/BPT trap

</output>

I've tried understanding this from a number of perspectives, including 
on my head & backwards through a mirror, but I'm afraid it's all Dutch 
to me.
Does that output give you any hint of what I may have done wrong?
& thanks again,
   Doug.

I posted this about a year ago. See if that helps.

http://www.napcsweb.com/blog/2007/03/08/15/



On Fri, Feb 29, 2008 at 2:05 PM, Doug Livesey <

I will do, Brian, thankyou!
If I can, I think I'll work through your article step-by-step (monkey 
see, monkey do!), but that does raise one question -- is it possible my 
earlier attempts to set this up will muck it all up?
Should I worry about cleaning out what I've done before?
Anyway, thanks again for everyone's input, & I'll let you know how I get 
on.
Cheers,
   Doug.

Like I say -- I'll post how I do, but it's hometime, now -- it's 9 
o'clock here in the UK, and Friday night to boot!
Thanks again,
   Doug.

Hi again, Brian -- it's been a while, as life got in the way again.
I'm following your instruction, which is great btw -- thanks for that.
I just have a couple of probably really dumb questions.
1) Where, in your instruction, you have 'MY_SERVER_NAME', I'm guessing 
that should be swapped out for the name or IP address of the server 
holding SQL Server?
2) Similarly, should 'MY_DATABASE_NAME' be swapped out for the actual 
name of the database I'm connecting to?
3) Is 'MY_DSN' meant to be swapped out for something, and if so, what 
would it be?

&, once again, I really appreciate you writing the article & drawing my 
attention to it.

Cheers,
   Doug.

It appears that there's a problem with FreeTDS on OSX Leopard -- details 
here:
http://article.gmane.org/gmane.comp.db.tds.freetds/8992

I'm still on with this problem, but will post when I get it sussed.
Cheers,
   Doug.

That makes sense, the guide I posted above has worked flawlessly 2 
mac's, and 3 unix boxes.

The only system that's a problem is a Leopard system, (not mine).

I'm not to sure what to do about this though.
(I've actually switched to using a global virtual instance, to let all 
of our developers work with, saves a ton of time when it comes to 
installing this, and a dozen other applications we use.

Doug Livesey wrote:
> It appears that there's a problem with FreeTDS on OSX Leopard -- details 
> here:
> http://article.gmane.org/gmane.comp.db.tds.freetds/8992
> 
> I'm still on with this problem, but will post when I get it sussed.
> Cheers,
>    Doug.

I'm on to the FreeTDS maintainer, now, and he is helpfully advising me 
-- I'll post here to let you know how I get on.
Cheers again,
   Doug.

Well that worked -- unfortunately I now have a new problem!
Probably due to my various efforts (including gem installing the SQL 
Server adapter for ActiveRecord & copying the DBD/ado.rb file over), I 
get FreeTDS working, but the following error output from my console in 
Rails when I try to count on a model called Properties:

<CONSOLE>
>> Property.count
DBI::InterfaceError: Could not load driver (no such file to load -- 
/usr/local/lib/ruby/site_ruby/1.8/dbd/ADO)
  from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:368:in `load_driver'
  from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:233:in 
`_get_full_driver'
  from /usr/local/lib/ruby/site_ruby/1.8/dbi.rb:219:in `connect'
  from 
/usr/local/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-1.0.0/lib/active_record/connection_adapters/sqlserver_adapter.rb:46:in 
`sqlserver_connection'
  from 
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:291:in 
`send'
  from 
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:291:in 
`connection='
  from 
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:259:in 
`retrieve_connection'
  from 
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:78:in 
`connection'
  from 
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/base.rb:1080:in 
`columns'
  from 
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/calculations.rb:254:in 
`column_for'
  from 
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/calculations.rb:118:in 
`calculate'
  from 
/usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/calculations.rb:46:in 
`count'
  from (irb):2

</CONSOLE>

Can anyone maybe offer any advice from that? I'm stumped!
Cheers,
   Doug.

Doug, did you ever get that working? I have the same error as you.
I've gotten each step working and can connect to SQL Server through
the FreeTDS client, but can't through DBI.connect().

DBI::InterfaceError: Could not load driver (no such file to load -- /
Library/Ruby/Site/1.8/dbd/ODBC)
  from /Library/Ruby/Site/1.8/dbi.rb:368:in `load_driver'
  from /Library/Ruby/Site/1.8/dbi.rb:233:in `_get_full_driver'
  from /Library/Ruby/Site/1.8/dbi.rb:219:in `connect'
  from (irb):5


On Apr 11, 5:42 am, Doug Livesey <rails-mailing-l...@andreas-s.net>

Andy wrote:
> Doug, did you ever get that working? I have the same error as you.
> I've gotten each step working and can connect to SQL Server through
> the FreeTDS client, but can't through DBI.connect().
> 
> DBI::InterfaceError: Could not load driver (no such file to load -- /
> Library/Ruby/Site/1.8/dbd/ODBC)
>   from /Library/Ruby/Site/1.8/dbi.rb:368:in `load_driver'
>   from /Library/Ruby/Site/1.8/dbi.rb:233:in `_get_full_driver'
>   from /Library/Ruby/Site/1.8/dbi.rb:219:in `connect'
>   from (irb):5
> 
> 
> On Apr 11, 5:42�am, Doug Livesey <rails-mailing-l...@andreas-s.net>


You should have pathto/dbd/ODBC directory and odbc.rb file in it. I 
dont't quite remember where I got it but I think you specify which 
drivers to install when you install dbi package.

You may also look into dbi package and search for odbc.rb file end 
extract it manualy into pathto/dbd/ODBC directory.

by
TheR

Hi!
Sorry, I didn't get it sorted at all -- I ran past my level of 
expertise!
I'm sort of hoping that the next Ruby or Rails update will give me the 
blank slate I need to get it working, and in the meantime I'm employing 
horrible kludges you don't even want to hear about!
Sorry I'm not more help,
   Doug.

I ended up getting ActiveRecord on OS X talking to SQL Server after
lots of work and trial and error.

Here were a couple of my resources in addition to the Rails wiki
pages.

http://toolmantim.com/article/2007/5/7/getting_rails_talking_to_sqlserver_on_osx_via_odbc
http://www.easysoft.com/developer/languages/ruby/rails.html#installing-easysoft-odbc-sql-server-driver

The second is a commercial ODBC package priced per machine at 500
pounds as of this writing.  I'm able to connect without that using
iODBC and FreeTDS.

Note that I also found the activerecord-sqlserver-adapter is no longer
on gems.rubyonrails.com and as of Rails 2 is no longer included with
the activerecord gem.  So I found it on github and cloned that done,
prepared the .gem file and installed it that way.  I didn't find
instructions for that, so I'm assuming it was just because it had
recently been moved to GitHub following Rails.

AndyAtkinson

On Jun 4, 7:24 am, Doug Livesey <rails-mailing-l...@andreas-s.net>