Fixing FreeTDS on OS X like a Ruby-wielding ostrich would

I am having massive problems connecting my MacBook Pro with the Microsoft SQL Server 2000 we have at work. I’ve connected to the same SQL Server (more or less) painlessly from Debian a couple of times before, so it bugs me that this is so frigging hard on OS X.

The most aggrevating part of the problem is the fact that I can actually connect. I can even execute queries and get results back. However, when I try to run a select query that returns no results, I get:

DBI::DatabaseError: 24000 (0) [FreeTDS][SQL Server]Invalid cursor state: SELECT id FROM users WHERE id = 0 (ActiveRecord::StatementInvalid)

Alright, at this point I know it’s either FreeTDS, iODBC, the ODBC driver, the Ruby DBI/ODBC libraries, or ActiveRecords SQL Server Adapter that’s messing with me. Gee, with all that many unknowns it should be easy, huh?

Test ODBC connection over FreeTDS

First order of business is to test the lowest level possible, using the iodbctest utility:

$ iodbctest "dsn=biq_alfred_dsn;uid=<user>;pwd=<password>"
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0001.0928
Driver: 0.64 (libtdsodbc.so)
SQL>SELECT id FROM users WHERE id = 0;
id   
-----
result set 1 returned 0 rows.

Excellent, that works, so my issue has to come from higher up the chain, which means the Ruby libraries.

Testing the Ruby DBI library

Checking stuff in Ruby is always easy, we just fire up our trusty irb:

$ irb
irb(main):001:0> require 'dbi'
=> true
irb(main):002:0> dbh = DBI.connect('DBI:odbc:biq_alfred_dsn', '<user>', '<password>')
=> #<DBI::DatabaseHandle:0x49778 @trace_output=#<IO:0x2b7c8>, @trace_mode=2, @handle=#<DBI::DBD::ODBC::Database:0x496c4 @attr={}, @handle=#<ODBC::Database:0x496ec>>>
irb(main):005:0> dbh.select_one('SELECT id FROM users WHERE id=0')
DBI::DatabaseError: 24000 (0) [FreeTDS][SQL Server]Invalid cursor state
        from /opt/local/lib/ruby/vendor_ruby/1.8/DBD/odbc/odbc.rb:239:in `fetch'
        from /opt/local/lib/ruby/vendor_ruby/1.8/dbi.rb:668:in `fetch'
        from /opt/local/lib/ruby/vendor_ruby/1.8/dbi.rb:503:in `select_one'
        from /opt/local/lib/ruby/vendor_ruby/1.8/dbi.rb:485:in `execute'
        from /opt/local/lib/ruby/vendor_ruby/1.8/dbi.rb:502:in `select_one'
        from (irb):5
        from :0

Aha, the issue can be reproduced at this level, which leaves me with the Ruby DBI/ODBC libraries being the likely culprits. Since the file where the exception is raised is DBD/odbc/odbc.rb, I’ll start investigating that.

It turns out that patching the failing fetch method to simply return nil when this exact error occurs actually fixes the problem. At this point I am more than willing to do as the ostriches do when faced with danger; stick my head in the sand and pretend everything is okay. So at the moment my Rails application overrides the Statement#fetch method with

module DBI::DBD::ODBC
  class Statement < DBI::BaseStatement
    def fetch
      convert_row(@handle.fetch)
    rescue ODBCErr => err
      if err.message == "24000 (0) [FreeTDS][SQL Server]Invalid cursor state"
        return nil
      else
        raise DBI::DatabaseError.new(err.message)
      end
    end
  end # class
end # module

and my test suite passes, both on Debian and on OS X.

Comments and Trackbacks

I am relatively new to mac (year and a half) and I am new to ruby and I am trying to connect our MSSQL server thru rails. If you could send me detailed instructions it would be most appreciated. Or if you know how to use Actual ODBC within the rails framework, I’d appreciate it.
Thanks

I have no experience with Actual ODBC. And I am not sure if I can get much more detailed than the above without being asked about specifics, sorry.

Thanks for the info. I put it in and it worked a treat for me! You’ve made me happy.

I have one thing to add, if anyone tries this and it doesn’t work:

It won’t work if the DBI::DBD:ODBC module is not already defined in the system. So you need to ensure that the module exists prior to “require”-ing the fixup code.

I did this by adding:
require “dbi”
require “DBD/ODBC/ODBC”

to the top of the file containing the fix.

It is quite possible that at some points in rails that these modules have been loaded automatically - but when I tried to require the fix from the environment.rb file I found that I had to add the above to make it work.

Cheers!