2

I need to pull data from a legacy SQL Server database and turn it into JSON. I have SQL queries already written (for legacy versions of reports, that I'm replacing) that extracts exactly what I need.

It is of course easy to turn ActiveRecord objects to JSON (and CSV), but I don't see any good way to do what I need to do, since all the ActiveRecord methods involving raw SQL seem to be aimed at returning ActiveRecord objects, and this data doesn't necessarily logically correspond to a class, and I don't need to persist it anywhere additional. I just want to perform the SQL query and return JSON and CSV and be done with it.

iconoclast
  • 21,213
  • 15
  • 102
  • 138
  • 1
    there is a railscast for this http://railscasts.com/episodes/362-exporting-csv-and-excel – bjhaid Jan 15 '14 at 19:27
  • If you are not doing rails, ruby has a mature `csv` library as well as a mature `json` library, they are pretty straightforward – bjhaid Jan 16 '14 at 06:00
  • if your query returns a list/array of activerecord objects (which I assume it should be doing), then all you need to is call `attributes` method on each elements in the list to convert it to a hash that you can now easily convert to JSON or CSV alternatively you can call `to_json` directly on the activerecord object and it becomes a json object – bjhaid Jan 16 '14 at 16:08
  • I've moved [this discussion to chat](http://chat.stackoverflow.com/rooms/45394/discussion-between-iconoclast-and-bjhaid). Let's delete the comments here. – iconoclast Jan 16 '14 at 18:38

2 Answers2

6

in your controller, you could add:

render json: @result

or

render :json => @result

and that would render something like this: {id: 1, name: "user name", color: "red"}

Samira Khorshidi
  • 963
  • 1
  • 9
  • 29
accelBurst
  • 61
  • 3
4

I'd recommend sequel.

From Querying with Sequel:

require 'sequel'

DB = Sequel.connect # database information goes here

class Thing < Sequel::Model
end

result_objects = DB["SELECT * FROM things"].all

Then simply parse the result_objects into JSON:

require 'json'

hashes = result_objects.collect { |ro|
  ro.to_hash
}

JSON.generate(hashes)
Shawn Bush
  • 644
  • 3
  • 6
  • I looked into using Sequel, but I couldn't find anything on making it work with MS SQL. Can you point me in the right direction? – iconoclast Jan 16 '14 at 04:15
  • [This page](http://sequel.jeremyevans.net/rdoc/files/doc/opening_databases_rdoc.html) has instructions for using various adapters to connect to your database of choice. Jump to the 'tinytds' section; that seems to be an adapter that should work with SQL Server. – Shawn Bush Jan 16 '14 at 14:49