possible to use outside of rails?

[Roy]

Hey All,

I need to do some data-mashing w/non-rails data that lives on mssql & am trying to work out how to use activerecord to do it.  I had thought to use either the .execute or .select methods of ActiveRecord::ConnectionAdapters::SQLServerAdapter, but the former is not acting as I'd hoped (returns just a Fixnum) and the latter throws a NoMethodError b/c .select is protected.  Please see code below.

I'm running ruby 1.8.7 (2011-02-18 patchlevel 334) [i386-mingw32] on winxp, hitting an mssql 2005 db.  Activerecord gem is v3.2.6 and activerecord-sqlserver-adapter is v3.2.9.

Am I just barking up the wrong tree, or should this be possible?  If so--can anybody let me know how to e.g., loop through a result set & execute my own SELECT , DELETE & UPDATE statements?

Many thanks!

-Roy

P.S. Here's my script:

require 'rubygems'

require 'active_record'

ab = ActiveRecord::Base

c = ab.establish_connection(

  :adapter => "sqlserver",

  :mode => "odbc",

  :dsn => "msa"

).connection

# This works w/out error, but x is a Fixnum w/value -1.  How would I get at a resultset w/this?

x = c.execute("select count(*) as n from dbo.recruits")

# This gives the following error

# C:/Documents and Settings/pardre1/Desktop/deleteme.rb:17: 

# protected method `select' called for #<ActiveRecord::ConnectionAdapters::SQLServerAdapter:0x2c1fc30> (NoMethodError)

# x = c.select("select count(*) as n from dbo.recruits")

c.close

puts c.class.name

puts x.class.name

puts x.to_s

# puts c.methods.sort.join("\n")

puts "Finished!"

[Ken Collins]

Hey Roy,

If you are going to use ActiveRecord _AND_ by pass all the model abstraction, then you should use the proper methods on the connection. Many people get these wrong because they confuse them with methods that may look similar on the low level raw_connection. TL;DR, #execute is not what you think. See here:

https://github.com/rails/rails/blob/3-2-stable/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb

That said, I would suggest if your going to by pass all the goodness of ActiveRecord which you are basically doing. Just skip it and use the gems that make raw connections. I see in your case you are using RubyODBC, why not use TinyTDS for the SQL Server connection?

That said, if you do just want to use ActiveRecord, why not just create some basic models that do all the work for you so you dont have to write all the SQL out. IE:

class Recruit < ActiveRecord::Base

end

Recruit.all

 - Ken

[Roy]

Thanks Ken!  That's very helpful.  I'll take a look at ruby-odbc & see if I can make that work, or if not, will try AR-derived classes.

Thanks!

-Roy