Re: Rails ADODB.Connection Error

On Jan 22, 11:02=A0am, gregarican <[e-mail]> wrote:
> On Jan 22, 10:47=A0am, gregarican <[e-mail]> wrote:
>
>
>
>
>
> > On Jan 21, 9:07=A0pm, gregarican <[e-mail]> wrote:
>
> > > On Jan 21, 8:45=A0pm, Luis Lavena <[e-mail]> wrote:
>
> > > > On 21 ene, 22:18, gregarican <[e-mail]> wrote:
>
> > > > > On Jan 21, 5:55=A0pm, Luis Lavena <[e-mail]> wrote:
>
> > > > > > On Jan 21, 5:59=A0pm, gregarican <[e-mail]> wrote:
>
> > > > > > > On Jan 21, 3:24=A0pm, Luis Lavena <[e-mail]> wrot=
e:
>
> > > > > > > > On Jan 21, 2:03=A0pm, gregarican <[e-mail]> wr=
ote:
>
> > > > > > > > > I know this isn't exclusively a Rails list, so I apologiz=
e in advance.
> > > > > > > > > Since this is the main list I work with I hope someone mi=
ght 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 serve=
r as well as
> > > > > > > > > a SQL Server instance. Then I copied over the Rails appli=
cation
> > > > > > > > > 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 I=
RB I can open
> > > > > > > > > up the new SQL Server instance and query its Employee tab=
le fine on
> > > > > > > > > the new server (which is what my Rails app is doing below=
).
>
> > > > > > > > > DBI::DatabaseError in Employee#signIn
>
> > > > > > > > > failed to create WIN32OLE object from `ADODB.Connection'
> > > > > > > > > =A0 =A0 HRESULT error code:0x8007007e
> > > > > > > > > =A0 =A0 =A0 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 SQLSe=
rver, is
> > > > > > > > telling you that cannot find ADODB.Connection module
>
> > > > > > > > Most likely, you don't have ADODB installed in your compute=
r.
>
> > > > > > > > Searching google for HRESULT 0x8007007e indicates that mayb=
e a missing
> > > > > > > > DLL or a DLL used by it.
>
> > > > > > > > Please ensure SQLServer and required libraries installed pr=
operly.
>
> > > > > > > > --
> > > > > > > > Luis Lavena- Hide quoted text -
>
> > > > > > > > - Show quoted text -
>
> > > > > > > AFAIK that shouldn't be a problem. I have SQL Server 2008 ins=
talled on
> > > > > > > the same box and from an IRB command line I was able to creat=
e a new
> > > > > > > ADODB.Connection object and query this database I need for Ra=
ils to
> > > > > > > talk to. It worked fine in that regard.
>
> > > > > > > Wonder if it's something about the new box being 64-bit Windo=
ws Server
> > > > > > > 2008? This is something I didn't thoroughly research in advan=
ce 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 bein=
g used
> > > > > > will be helpful.
> > > > > > --
> > > > > > Luis Lavena- Hide quoted text -
>
> > > > > > - Show quoted text -
>
> > > > > On the same box means that I can start up IRB on the 64-bit serve=
r,
> > > > > create a new ADODB.Connection object and query the SQL Server 200=
8
> > > > > database. It works fine, although that Rails cannot accomplish th=
e
> > > > > 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 tracebac=
k. 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. I=
t
> > > > 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.
>
> > > > --
> > > > Luis Lavena- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > 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...- 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)
> =A0 =A0 Service layer for easy email delivery and testing.
>
> actionpack (1.8.0)
> =A0 =A0 Web-flow and rendering framework putting the VC in MVC.
>
> actionwebservice (0.7.0)
> =A0 =A0 Web service support for Action Pack.
>
> activerecord (1.10.0)
> =A0 =A0 Implements the ActiveRecord pattern for ORM.
>
> activesupport (1.0.4)
> =A0 =A0 Support and utility classes used by the Rails framework.
>
> builder (2.1.2)
> =A0 =A0 Builders for MarkUp.
>
> rails (0.12.0)
> =A0 =A0 Web-application framework with template engine, control-flow
> layer,
> =A0 =A0 and ORM.
>
> rake (0.5.3)
> =A0 =A0 Ruby based make-like utility.
>
> sources (0.0.1)
> =A0 =A0 This package provides download sources for remote gem installatio=
n
>
> *** GOOD SERVER'S GEMS ***
>
> actionmailer (0.9.0)
> =A0 =A0 Service layer for easy email delivery and testing.
>
> actionpack (1.8.0)
> =A0 =A0 Web-flow and rendering framework putting the VC in MVC.
>
> actionwebservice (0.7.0)
> =A0 =A0 Web service support for Action Pack.
>
> activerecord (1.10.0)
> =A0 =A0 Implements the ActiveRecord pattern for ORM.
>
> activesupport (1.0.4)
> =A0 =A0 Support and utility classes used by the Rails framework.
>
> builder (2.1.2)
> =A0 =A0 Builders for MarkUp.
>
> rails (0.12.0)
> =A0 =A0 Web-application framework with template engine, control-flow
> layer,
> =A0 =A0 and ORM.
>
> rake (0.5.3)
> =A0 =A0 Ruby based make-like utility.
>
> sources (0.0.1)
> =A0 =A0 This package provides download sources for remote gem installatio=
n- 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 =3D nil
		@data =3D nil
	end

	def open
	# Open ADO connection to the SQL Server database
		connection_string =3D  "Provider=3DSQLOLEDB;"
		connection_string << "Persist Security Info=3DFalse;"
		connection_string << "User ID=3Dsa;"
		connection_string << "password=3DXXXXXX;"
		connection_string << "Initial Catalog=3Dpo;"
		connection_string << "Data Source=3Ddiamond-sql\\mssql;"
		connection_string << "Network Library=3Ddbmssocn"

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

	def query(sql)
		# Create an instance of an ADO Recordset
		recordset =3D 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 =3D []

		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 =3D recordset.GetRows

		rescue
			@data =3D []
		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 =3D @data.transpose
	end

	def close
		@connection.Close
	end
end

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

puts results # This worked like a champ...