Rails and Oracle: execute sql statement and get all data

Question

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.

Bjoernsen · Accepted Answer · 2013-09-19 11:34:58Z

13

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.

answered Sep 19, 2013 at 11:34

Bjoernsen

2,4281 gold badge38 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

royvandermeij Over a year ago

Why don't you use the pluck method for this? ie: Feature.pluck(:id)

Bjoernsen Over a year ago

Because this was an example. I had the same problem with a complex sql statement. But I thought it is easier to explain and to understand if I use Feature as an example.

Lievcin Over a year ago

@Bjoernsen absolutely right. I'm working on a project where the sql queries are quite complex and ActiveRecord make more problems than it solves! Thanks a lot!

ruby_object · Accepted Answer · 2024-11-08 12:06:53Z

1

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

answered Nov 8, 2024 at 12:06

ruby_object

1,2661 gold badge13 silver badges34 bronze badges

Collectives™ on Stack Overflow

Rails and Oracle: execute sql statement and get all data

2 Answers 2

3 Comments

my solution

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

my solution

Comments

Your Answer

Sign up or log in

Post as a guest

Related