CODEWITHSUNDEEP
ruby-on-railsoracle-databaseruby-on-rails-4
Bjoernsen
Bjoernsen

Reputation: 2418

Rails and Oracle: execute sql statement and get all data

I have a rather complex sql statement that is created on the fly. But the same problem exists for a simple query, so I use this one as an example.

I have a Feature model. I can call Feature.count --> 4

But if I try to get all Feature ids using ActiveRecord::Base.connection.execute("SELECT ID from features") the result is a OCI8::Cursor object. I do not know how to get the data out of it.

If I try ActiveRecord::Base.connection.execute("SELECT ID from features").fetch I get [1]. With xyz.fetch_hash I get {"ID" => 1}.

I would like to have all IDs. I just switched from PostgreSQL to Oracle.

Using PostgreSQL, I got all data using ActiveRecord::Base.connection.execute("SELECT ID from features").to_a, but to_a does not exist using the activerecord-oracle_enhanced-adapter gem (*** NoMethodError Exception: undefined method 'to_a' for #<OCI8::Cursor:0x00000007028aa8>).

I am using Ruby 2.0 and Rails 4. The connection to the Oracle database is working, except my manually created statement.

Upvotes: 6

Views: 3683

Answers (2)

ruby_object
ruby_object

Reputation: 1266

my solution

  def my_exec_query(sql)
    # because our old framework does not have exec_query
    res = ActiveRecord::Base.connection.execute(sql)
    res_data = []
    res.fetch_hash { |d| res_data << d }
    res.close

    res_data
  end

Upvotes: 1

Bjoernsen
Bjoernsen

Reputation: 2418

I found a solution.

Old: ActiveRecord::Base.connection.execute("SELECT ID from features")

New: ActiveRecord::Base.connection.exec_query("SELECT ID from features").to_a

Maybe someone have the same problem.

Upvotes: 13

Related Questions