Connecting to SQL Server (from Ubuntu): [RubyODBC] Cannot allocate SQLHENV

Question

I'm trying to connect my Ruby on Rails application to a legacy Database that is in a Microsoft SQL Server 2008. I have looked around different tutorials, pages, help pages, and question pages and still haven't been able to make it work.

I'm using:

• Ubuntu karmic
• Rails 2.3.5
• Ruby 1.8.7
• activerecord-odbc-adapter 2.0
• activerecord-sqlserver-adapter 2.3
• dbd-odbc 0.2.5
• dbi 0.4.3

I also have installed:

• freeTDS 0.82-6
• tdsodbc 0.82-6
• sqsh 2.1-8build1
• libodbc-ruby1.8 0.9997-2 (have also tried with 0.9995 and 0.9998)
• unixodbc 2.2.11-16ubuntu1

I have managed to connect to the database with tsql, sqsh and isql. But can't connect it through irb nor through rails. Whenever I try I get the following error:

DBI::DatabaseError: **INTERN (0) [RubyODBC]Cannot allocate SQLHENV**
 from /var/lib/gems/1.8/gems/dbd-odbc-0.2.5/lib/dbd/odbc/driver.rb:36:in `connect'
 from /usr/lib/ruby/1.8/dbi/handles/driver.rb:33:in `connect'
 from /usr/lib/ruby/1.8/dbi.rb:142:in `connect'
 from (irb):4

It doesn't really matter if I use the odbc-adapter or the sql-adapter, because it always fails in the dbi driver. Any ideas on what can be wrong?

I have the odbc.ini, odbcinst.ini and freetds.conf files properly defined. And they are working with all the other commands to connect (isql, tsql, sqsh).

Please help me or point me somewhere that might help me! =)

PS: I have already followed the following pages with not much success (among others):

• wiki.rubyonrails.org/database-support/ms-sql?s[]=sql&s[]=server
• piao-tech.blogspot.com/2008/02/using-activerecord-with-microsoft-sql.html
• lambie.org/2008/02/28/connecting-to-an-mssql-database-from-ruby-on-ubuntu/
• stackoverflow.com/questions/1419397/rubyodbc-cannot-allocate-sqlhenv

Answer 1

If you can switch to JRuby ( jruby.org ) you can try using the active-record-jdbc gem with the MS SQL server JDBC driver. I use them to connect to a MS SQL Server 2005 database from Ruby and it works like a charm.

Hope it helps!

Answer 2

This page seems to suggest that the problem may be related to the inability to find a shared library. You should ensure that the system can find any libraries it may need by checking the output of ldconfig -v and ensuring that the folders containing libodbc, libodbcinst and libtdsodbc are included in the library search path. If you are not sure how to do this then this link may help.

If this fails then build the gem manually using the following line

ruby extconf.rb --with-odbc-dir=/folder/to/unixodbc --disable-dlopen

I can't swear that this is the correct answer but it may help. I do have freetds and SQL Server working quite happily with rails on Centos

Answer 3

So, It is a problem of the ubuntu karmic packages? I have it and also the same problem, is there a form of downgrade the packages?

Answer 4

I only have this error on my x64 machine. With a 32bit Ubuntu 9.10 it's working fine (using Christian's rubyodbc). I will keep looking for a solution and a downgrade is none.

Answer 5

I am getting same error and nothing from above is working on my Ubunt 9.10 x64. Is there any progress? I really want to use ubuntu 9.10 and rails 2.3.5.