Rails ADODB.Connection Error

[gregarican]

I know this isn't exclusively a Rails list, so I apologize in advance.
Since this is the main list I work with I hope someone might have some
insight into a particularly nagging error message I have been getting.

The scenario is I have to move a working Rails setup to a new server.
So I installed Ruby/Rails/Apache/FastCGI on the new server as well as
a SQL Server instance. Then I copied over the Rails application
directory to the new box. Figuring that pointing Rails to the new SQL
Server instance would just involve modifying the database.yml file, I
changed the host name to the new SQL Server instance.

I can get the default Rails welcome page fine on the new server. But
when I try to launch my Rails app I get the error below. Any
suggestions about what specifically is the issue? Using IRB I can open
up the new SQL Server instance and query its Employee table fine on
the new server (which is what my Rails app is doing below).

DBI:atabaseError in Employee#signIn

failed to create WIN32OLE object from `ADODB.Connection'
HRESULT error code:0x8007007e
The specified module could not be found.
app/controllers/employee_controller.rb:111:in `signIn'

Show framework trace

C:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO/ADO.rb:57:in `connect'
C:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:584:in `connect'
C:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:384:in `connect'
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.10.0/lib/active_record/
connection_adapters/sqlserver_adapter.rb:49:in `sqlserver_connection'
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.10.0/lib/active_record/
connection_adapters/abstract_adapter.rb:136:in `send'
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.10.0/lib/active_record/
connection_adapters/abstract_adapter.rb:136:in `connection='
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.10.0/lib/active_record/
connection_adapters/abstract_adapter.rb:100:in `retrieve_connection'
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.10.0/lib/active_record/
base.rb:235:in `connection'
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.10.0/lib/active_record/
base.rb:366:in `find_by_sql'
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.10.0/lib/active_record/
base.rb:334:in `find'
C:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.10.0/lib/active_record/
deprecated_finders.rb:37:in `find_all'
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.8.0/lib/action_controller/
base.rb:708:in `send'
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.8.0/lib/action_controller/
base.rb:708:in `perform_action_without_filters'
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.8.0/lib/action_controller/
filters.rb:294:in `perform_action_without_benchmark'
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.8.0/lib/action_controller/
benchmarking.rb:34:in `perform_action_without_rescue'
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.8.0/lib/action_controller/
benchmarking.rb:34:in `measure'
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.8.0/lib/action_controller/
benchmarking.rb:34:in `perform_action_without_rescue'
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.8.0/lib/action_controller/
rescue.rb:79:in `perform_action'
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.8.0/lib/action_controller/
base.rb:330:in `send'
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.8.0/lib/action_controller/
base.rb:330:in `process'
C:/ruby/lib/ruby/gems/1.8/gems/actionpack-1.8.0/lib/action_controller/
base.rb:273:in `process'
C:/ruby/lib/ruby/gems/1.8/gems/rails-0.12.0/lib/dispatcher.rb:32:in
`dispatch'
C:/Program Files/Apache Group/Apache2/htdocs/po/public/dispatch.fcgi:
20
C:/Program Files/Apache Group/Apache2/htdocs/po/public/dispatch.fcgi:
18:in `each_cgi'
C:/ruby/lib/ruby/site_ruby/1.8/fcgi.rb:597:in `each'
C:/ruby/lib/ruby/site_ruby/1.8/fcgi.rb:597:in `each_cgi'
C:/Program Files/Apache Group/Apache2/htdocs/po/public/dispatch.fcgi:
18

 

[Luis Lavena]

I know this isn't exclusively a Rails list, so I apologize in advance.
Since this is the main list I work with I hope someone might have some
insight into a particularly nagging error message I have been getting.

The scenario is I have to move a working Rails setup to a new server.
So I installed Ruby/Rails/Apache/FastCGI on the new server as well as
a SQL Server instance. Then I copied over the Rails application
directory to the new box. Figuring that pointing Rails to the new SQL
Server instance would just involve modifying the database.yml file, I
changed the host name to the new SQL Server instance.

I can get the default Rails welcome page fine on the new server. But
when I try to launch my Rails app I get the error below. Any
suggestions about what specifically is the issue? Using IRB I can open
up the new SQL Server instance and query its Employee table fine on
the new server (which is what my Rails app is doing below).

DBI:atabaseError in Employee#signIn

failed to create WIN32OLE object from `ADODB.Connection'
    HRESULT error code:0x8007007e
      The specified module could not be found.
app/controllers/employee_controller.rb:111:in `signIn'

Show framework trace

Basically Win32OLE, the backend used to connect using SQLServer, is
telling you that cannot find ADODB.Connection module

Most likely, you don't have ADODB installed in your computer.

Searching google for HRESULT 0x8007007e indicates that maybe a missing
DLL or a DLL used by it.

Please ensure SQLServer and required libraries installed properly.

 

[gregarican]

Basically Win32OLE, the backend used to connect using SQLServer, is
telling you that cannot find ADODB.Connection module

Most likely, you don't have ADODB installed in your computer.

Searching google for HRESULT 0x8007007e indicates that maybe a missing
DLL or a DLL used by it.

Please ensure SQLServer and required libraries installed properly.

AFAIK that shouldn't be a problem. I have SQL Server 2008 installed on
the same box and from an IRB command line I was able to create a new
ADODB.Connection object and query this database I need for Rails to
talk to. It worked fine in that regard.

Wonder if it's something about the new box being 64-bit Windows Server
2008? This is something I didn't thoroughly research in advance due to
being forced to pull the trigger quicker than I'd have liked. The
Rails version is _ancient_ too. From 2005 I believe. Version 0.12.0
IIRC :-/

 

[Phillip Gawlowski]

Wonder if it's something about the new box being 64-bit Windows Server
2008? This is something I didn't thoroughly research in advance due to
being forced to pull the trigger quicker than I'd have liked. The
Rails version is _ancient_ too. From 2005 I believe. Version 0.12.0
IIRC :-/

Yeah, the 64 bit issue seems the big one. And if you have a SQL Server
2008 instance, I doubt the ActiveRecord connectors from 2005 can talk to
it, so you have two problems for the price of one.

The Ruby issues, you could, possibly, fix by using a 64 bit build of
Ruby (no clue if such a thing is available, however). A more recent
Rails should come with SQL Server 2008 support if you need that,
otherwise you have to somehow proxy the DB requests through an SQL
Server instance that your ActiveRecord can connect to.

 

[gregarican]

Yeah, the 64 bit issue seems the big one. And if you have a SQL Server
2008 instance, I doubt the ActiveRecord connectors from 2005 can talk to
it, so you have two problems for the price of one.

The Ruby issues, you could, possibly, fix by using a 64 bit build of
Ruby (no clue if such a thing is available, however). A more recent
Rails should come with SQL Server 2008 support if you need that,
otherwise you have to somehow proxy the DB requests through an SQL
Server instance that your ActiveRecord can connect to.

Yeah, this is my first forway into the world of 64-bit Windows 2008
Server. I also had to port over roughly two dozen ODBC System DSN's
and how some were only accessible through the 64-bit ODBC Manager and
others through the 32-ODBC Manager was throwing me for a moment or
two.

From what I can tell the Rails SQLServer connector appears to be using
SQLOLEDB, which Lord knows if it's looking for the wrong version or
something. When I was able to use IRB on connect to the SQL 2008
Server database fine on the box I thought I had hope. But if behind
the scenes Rails is using something different that's more hard-coded I
might seek an alternative method of getting this migration done.

Appreciate the insight!

 

[Luis Lavena]

AFAIK that shouldn't be a problem. I have SQL Server 2008 installed on
the same box and from an IRB command line I was able to create a new
ADODB.Connection object and query this database I need for Rails to
talk to. It worked fine in that regard.

Wonder if it's something about the new box being 64-bit Windows Server
2008? This is something I didn't thoroughly research in advance due to
being forced to pull the trigger quicker than I'd have liked. The
Rails version is _ancient_ too. From 2005 I believe. Version 0.12.0
IIRC :-/

"on the same box" means?

Can you perform the exact same Win32OLE request to create a
ADODB.Connection on your 64bits server?

I haven't encountered issues on this topic about mxing 32bits
executables and connect them using OLE with 64bits. Basically, Windows
handle those.

The only thing Windows do not handle or allow is you loading a 64bits
DLL in a 32bits process. AFAIK is not the issue.

Let just eliminate Rails of the equation for a second.

Also, a full version of Ruby (ruby -v) and the ADO library being used
will be helpful.

 

[gregarican]

"on the same box" means?

Can you perform the exact same Win32OLE request to create a
ADODB.Connection on your 64bits server?

I haven't encountered issues on this topic about mxing 32bits
executables and connect them using OLE with 64bits. Basically, Windows
handle those.

The only thing Windows do not handle or allow is you loading a 64bits
DLL in a 32bits process. AFAIK is not the issue.

Let just eliminate Rails of the equation for a second.

Also, a full version of Ruby (ruby -v) and the ADO library being used
will be helpful.

On the same box means that I can start up IRB on the 64-bit server,
create a new ADODB.Connection object and query the SQL Server 2008
database. It works fine, although that Rails cannot accomplish the
same thing. Looking at what's going on it has to be a Rails issue as
far as I can tell. When I changed the database.yml back to point to
the old, 32-bit SQL Server 2000 box I get the same error traceback. So
it's not like the new SQL Server 2008 instance it throwing things off.

The Ruby version is i386-mswin32 1.8.2. The ADO version is 1.6...

 

[Luis Lavena]

On the same box means that I can start up IRB on the 64-bit server,
create a new ADODB.Connection object and query the SQL Server 2008
database. It works fine, although that Rails cannot accomplish the
same thing. Looking at what's going on it has to be a Rails issue as
far as I can tell. When I changed the database.yml back to point to
the old, 32-bit SQL Server 2000 box I get the same error traceback. So
it's not like the new SQL Server 2008 instance it throwing things off.

The Ruby version is i386-mswin32 1.8.2. The ADO version is 1.6...

Excellent!

I mean, not the error, but that the issue can be reproduced and is not
related to 32/64 bits.

In your local machine, under which version of Ruby, ADODB (of Ruby)
and version of Rails are you running?

Ruby 1.8.2 seems VERY old to me, that is what "ruby -v" reports?

If is a newer version of Rails on top of an old version of Ruby and
ADODB ruby adapter, then the problem could be there.

We need details of the working environment versus the broken one. It
seems rails 0.12.0 with ActionPack 1.8.0 and ActiveRecord 1.10.0

Now we need to know which version of Ruby and your local/working
environment.

 

[gregarican]

Excellent!

I mean, not the error, but that the issue can be reproduced and is not
related to 32/64 bits.

In your local machine, under which version of Ruby, ADODB (of Ruby)
and version of Rails are you running?

Ruby 1.8.2 seems VERY old to me, that is what "ruby -v" reports?

If is a newer version of Rails on top of an old version of Ruby and
ADODB ruby adapter, then the problem could be there.

We need details of the working environment versus the broken one. It
seems rails 0.12.0 with ActionPack 1.8.0 and ActiveRecord 1.10.0

Now we need to know which version of Ruby and your local/working
environment.

That's it. Ruby 1.8.2 and Rails 0.12.0 with those supporting versions
you have mentioned. I'm trying a local install on my testing laptop
now. I have the same Ruby and Rails versions on my laptop, along with
SQL Server 2008 Developer Edition and 64-bit Windows 7 Pro. We'll
see...

 

[gregarican]

That's it. Ruby 1.8.2 and Rails 0.12.0 with those supporting versions
you have mentioned. I'm trying a local install on my testing laptop
now. I have the same Ruby and Rails versions on my laptop, along with
SQL Server 2008 Developer Edition and 64-bit Windows 7 Pro. We'll
see...- Hide quoted text -

- Show quoted text -

I restored the DB to my 64-bit Windows 7 laptop's SQL Server 2008
Developer install. Then I copied over the whole Rails directory
structure from the non-working server to my laptop. All I did was
change the config/database.yml file so that the host was my laptop.
When I fired up WEBrick locally I was able to navigate the various
Rails pages fine on my laptop. This was with the same versions of
Ruby, Rails, the Rails supporting files, and SQL Server. The only
minor tweak I had to do was rename the columns in the DB since for
some reason the case-sensitivity wasn't maintained when restoring it
to my laptop.

Something is definitely odd on that 64-bit Windows 2008 Server I'm
thinking. Because my laptop is running things fine...

 

[gregarican]

I restored the DB to my 64-bit Windows 7 laptop's SQL Server 2008
Developer install. Then I copied over the whole Rails directory
structure from the non-working server to my laptop. All I did was
change the config/database.yml file so that the host was my laptop.
When I fired up WEBrick locally I was able to navigate the various
Rails pages fine on my laptop. This was with the same versions of
Ruby, Rails, the Rails supporting files, and SQL Server. The only
minor tweak I had to do was rename the columns in the DB since for
some reason the case-sensitivity wasn't maintained when restoring it
to my laptop.

Something is definitely odd on that 64-bit Windows 2008 Server I'm
thinking. Because my laptop is running things fine...- Hide quoted text -

- Show quoted text -

Here's an exact list of the installed gems on both the "good" server
and the "bad" server. Appear to be identical :-/


*** BAD SERVER'S GEMS ***

actionmailer (0.9.0)
Service layer for easy email delivery and testing.

actionpack (1.8.0)
Web-flow and rendering framework putting the VC in MVC.

actionwebservice (0.7.0)
Web service support for Action Pack.

activerecord (1.10.0)
Implements the ActiveRecord pattern for ORM.

activesupport (1.0.4)
Support and utility classes used by the Rails framework.

builder (2.1.2)
Builders for MarkUp.

rails (0.12.0)
Web-application framework with template engine, control-flow
layer,
and ORM.

rake (0.5.3)
Ruby based make-like utility.

sources (0.0.1)
This package provides download sources for remote gem installation



*** GOOD SERVER'S GEMS ***

actionmailer (0.9.0)
Service layer for easy email delivery and testing.

actionpack (1.8.0)
Web-flow and rendering framework putting the VC in MVC.

actionwebservice (0.7.0)
Web service support for Action Pack.

activerecord (1.10.0)
Implements the ActiveRecord pattern for ORM.

activesupport (1.0.4)
Support and utility classes used by the Rails framework.

builder (2.1.2)
Builders for MarkUp.

rails (0.12.0)
Web-application framework with template engine, control-flow
layer,
and ORM.

rake (0.5.3)
Ruby based make-like utility.

sources (0.0.1)
This package provides download sources for remote gem installation

 

[gregarican]

Here's an exact list of the installed gems on both the "good" server
and the "bad" server. Appear to be identical :-/

*** BAD SERVER'S GEMS ***

actionmailer (0.9.0)
    Service layer for easy email delivery and testing.

actionpack (1.8.0)
    Web-flow and rendering framework putting the VC in MVC.

actionwebservice (0.7.0)
    Web service support for Action Pack.

activerecord (1.10.0)
    Implements the ActiveRecord pattern for ORM.

activesupport (1.0.4)
    Support and utility classes used by the Rails framework.

builder (2.1.2)
    Builders for MarkUp.

rails (0.12.0)
    Web-application framework with template engine, control-flow
layer,
    and ORM.

rake (0.5.3)
    Ruby based make-like utility.

sources (0.0.1)
    This package provides download sources for remote gem installation

*** GOOD SERVER'S GEMS ***

actionmailer (0.9.0)
    Service layer for easy email delivery and testing.

actionpack (1.8.0)
    Web-flow and rendering framework putting the VC in MVC.

actionwebservice (0.7.0)
    Web service support for Action Pack.

activerecord (1.10.0)
    Implements the ActiveRecord pattern for ORM.

activesupport (1.0.4)
    Support and utility classes used by the Rails framework.

builder (2.1.2)
    Builders for MarkUp.

rails (0.12.0)
    Web-application framework with template engine, control-flow
layer,
    and ORM.

rake (0.5.3)
    Ruby based make-like utility.

sources (0.0.1)
    This package provides download sources for remote gem installation- Hide quoted text -

- Show quoted text -

I whipped up a quick Ruby test script (see below) and was able to
query the same SQL Server 2008 database table on the "bad" server, so
apparently Ruby DBI/ADO is working in this regard. But Rails isn't!

SqlServer.rb
------------------------

require 'win32ole'

class SqlServer
# This class manages database connection and queries
attr_accessor :connection, :data, :fields

def initialize
@connection = nil
@data = nil
end

def open
# Open ADO connection to the SQL Server database
connection_string = "Provider=SQLOLEDB;"
connection_string << "Persist Security Info=False;"
connection_string << "User ID=sa;"
connection_string << "password=XXXXXX;"
connection_string << "Initial Catalog=po;"
connection_string << "Data Source=diamond-sql\\mssql;"
connection_string << "Network Library=dbmssocn"

@connection = WIN32OLE.new('ADODB.Connection')
@connection.Open(connection_string)
end

def query(sql)
# Create an instance of an ADO Recordset
recordset = WIN32OLE.new('ADODB.Recordset')

# Open the recordset, using an SQL statement and the
# existing ADO connection
recordset.Open(sql, @connection)

# Create and populate an array of field names
@fields = []

recordset.Fields.each do |field|
@fields << field.Name
end

begin
# Move to the first record/row, if any exist
recordset.MoveFirst

# Grab all records
@data = recordset.GetRows

rescue
@data = []
end

recordset.Close

# An ADO Recordset's GetRows method returns an array
# of columns, so we'll use the transpose method to
# convert it to an array of rows
@data = @data.transpose
end

def close
@connection.Close
end
end

sqlQuery = "SELECT * from Employees;"
db = SqlServer.new
db.open
db.query(sqlQuery)
field_names = db.fields
results = db.data
db.close

puts results # This worked like a champ...