RoR ActiveRecord find_by_sql Question

Question

I want to preface this question by stating that I am fairly new to Ruby development, however, personally, I've dedicated myself to trying to find the answers myself, as opposed to whimsically asking questions on forums. That being said, this is my first official "post", and I have made every attempt to be as accurate and concise in my posting.

I have a MSSQL 2005 database that I am connecting to, using RoR ActiveRecord & RubyGems ADO.

In my AR Base Class, I am running a find_by_sql statement, which is executing a multi-table SELECT statement. My query is executing as expected, and I have verified that the data being returned is accurate.

My Goal:

I need to query an MSSQL 2005 database, and then dump the data that is returned to a .csv file.

My plan to accomplish this is:

1. Use ruby gems & ADO to connect to the MSSQL DB
2. Use Ruby AR to query the database using find-by-sql (due to the nature of the query)
3. Use Ruby FasterCSV to retrieve the data and dump it to a .csv file.

Current Scenario:

I am able to successfully connect to the database, and query the data using AR.

Problem:

Once the find-by-sql query has run, AR returns the data in an array of hashes. As I stated before, I am new to AR, and have been developing in RoR for several months now. As such, I do not know how to access the array's attributes.

Here's what I have so far:

require 'rubygems'
require 'activerecord'
require "fastercsv"


ActiveRecord::Base.pluralize_table_names = false

ActiveRecord::Base.establish_connection(
    :adapter => 'sqlserver',
    :mode => 'ODBC',
    :dsn => 'xxxx',
    :database => 'xxxx',
    :username => 'blah',
    :password => 'blahblah',
    :host => 'server'
)

class Dp_display < ActiveRecord::Base
    od30 = Dp_display.find_by_sql("SELECT dd.acct_no, dd.cur_bal, dd.od_dt, ra.email_1
    FROM dp_display dd, rm_acct ra
    WHERE dd.rim_no = ra.rim_no and dd.cur_bal < -10 and dd.class_code = 125 and 
    dd.od_dt = convert(varchar, getdate()-30,101)
    ORDER BY dd.od_dt desc");

    #testing od30 returning values successfully
    puts od30

Result:

When I look at od30, all data is being returned correctly. The data looks like this (only listing the first array element):

    od30 = Array (2 elements)
      +[0] = {Dp_display}#<Dp_display:0x7667d74>
          +@attributes = Hash (4 elements)
             +'acct_no'-> 1122334455
             +'email_1'-> [email protected]
             +'cur_bal'-> -333.55
             +'od_dt'-> 2009-09-08 00:00:00.000
      +[1] = {Dp_display}#<Dp_display:0x7667d10>

Using FasterCSV, I perform the following:

    FasterCSV.open("c://file30.csv", "w") do |csv|
      csv << od30

The csv file is successfully created, and when I open it, it only contains:

<Dp_display:0x7667d74>

What I am trying to get is:

1122334455,[email protected],-333.55,2009-09-08 00:00:00.000

For learning / testing purposes, I have tried accessing the array elements using many different array functions including model.attributes, each_index, flatten, and then putting the result. Unfortunately, I cannot figure it out.

In any case, I come up with the same result:

Again, I know that with some time, I could come up with an elaborate solution, but for now, I want to try and keep it simple (as I am new to development, and trying to learn small steps).

Any assistance would be greatly appreciated (even if simply pointing me in the right direction would be great!).

Thank you.

Answer 1

find_by_sql will still attempt map your results to objects, in your case Dp_display objects. As they are ActiveRecord objects you can access the columns it returned like any attribute, but any derived columns that don't exist in the table will be read-only.

It appears you are trying to dump an array of objects instead of each object on its own. Try something like this:

FasterCSV.open("c://file30.csv", "w") do |csv|
  csv << ['here', 'are', 'my', 'headers']
  od30.each |o|
     csv << [o.acct_no, o.email_1, o.cur_bal, o.od_dt]
  end
end

That is off the top of my head, there is probably an easier way to do it by matching header keys to the attributes. Good luck!

Peer

Answer 2

Or just

FasterCSV.open("c://file30.csv", "w") do |csv|
  od30.each |o|
     csv << o
  end
end

http://fastercsv.rubyforge.org/