• Registered: 2007-01-07
  • Posts: 10

Topic: Help with ActiveRecord::Base.execute(sql)

I have a query that I need to run as a raw sql query against a "legacy" Oracle database. I have not been able to find a complete example of doing such and being very new to this I'm lost. I'm already set up to talk to the Oracle database as other parts of my app are working fine.

Can I just create a new file, my_query.rb, under the models folder like below?

[code = ruby]

sql = "my complex sql statement"
ActiveRecord::Base.execute(sql)

[/code]

Then what? I feel like I'm missing something. How do I access the rows returned by my query?

Sorry if this is a simple task, my background is in writing SQL not programming code. I am making some progress though.
Thanks.

  • ryanb
  • Yardmaster
  • Offline
  • Registered: 2006-06-14
  • Posts: 6,324

Re: Help with ActiveRecord::Base.execute(sql)

I think this returns a MySQL::Result object. See the docs on how to use that. Also, you may want ActiveRecord::Base.connection.execute(sql) instead.

Railscasts - Free Ruby on Rails Screencasts

ryanb's Website

  • Registered: 2007-01-07
  • Posts: 10

Re: Help with ActiveRecord::Base.execute(sql)

Ok, I have a model named my_query.rb that looks like this...
[code = ruby]
sql = "my_complex_sql_query"
ActiveRecord::Base.connection.execute(sql)
[/code]
I have a controller my_query.rb 

class MyQueryController < ApplicationController
def index
    render :action => 'my_query'
  end
end

and a view my_query.rhtml
[code = ruby]
<table>
  <tr>
  <% for column in my_query.content_columns %>
    <th><%= column.human_name %></th>
  <% end %>
  </tr>
 
<% for data in my_query %>
  <tr>
  <% for column in data.content_columns %>
    <td><%=h my_query.send(column.name) %></td>
  <% end %>
  </tr>
<% end %>
</table>
[/code]

This doesn't work as I'm not sure how to reference the result object returned by the model. How do I tie this all together?
Does anyone have a simple working example they would be willing to share?
Thanks.

  • ryanb
  • Yardmaster
  • Offline
  • Registered: 2006-06-14
  • Posts: 6,324

Re: Help with ActiveRecord::Base.execute(sql)

What is the name of the table you are performing the query on? The name of the model should be the singular version of that. You mentioned it's a legacy database, but if you can set up models for it that would help.

Once you get a model set up you can use find_by_sql which is easier to use than executing the query directly on the connection.

BTW, if this is your first Rails project I recommend working on something where you create the database from scratch. Working with a legacy database is tricky with Rails and isn't the best way to learn.

Railscasts - Free Ruby on Rails Screencasts

ryanb's Website

  • Registered: 2007-05-17
  • Posts: 924

Re: Help with ActiveRecord::Base.execute(sql)

Hi ryan,

It seems that using ActiveRecord::Base.connection.execute provides the result to be used only once in a block.

The problem is that I am trying to make a calculation on it using a each(), then I want to list its elements in the view again using each. The problem is that it works only once.

Any solution around that?

Johnson's Website

  • Registered: 2007-04-09
  • Posts: 3

Re: Help with ActiveRecord::Base.execute(sql)

try something like this

some_objects = []
mysql_res = connection.execute(some_query)
mysql_res.each_hash{ |res| some_objects << res }
return some_objects

you could even map this to a particular model object like this

some_model_objects = []
mysql_res = connection.execute(some_query)
mysql_res.each_hash{ |res| some_model_objects << SomeModelObject.new(res) }
return some_model_objects

  • Registered: 2008-07-14
  • Posts: 1

Re: Help with ActiveRecord::Base.execute(sql)

try this
sql = "my_complex_sql_query"
SomeModelObject.find_by_sql(sql)

works for me

  • From: Hikone, Japan
  • Registered: 2006-06-29
  • Posts: 53

Re: Help with ActiveRecord::Base.execute(sql)

@all, why no one ever answered this simple question?

@rx7haze


class MyQuery < ActiveRecord::Base
  def self.sql
     "my_complex_sql_query"
  end
end
class MyQueryController < ApplicationController
  def index
    @result = MyQuery.find(MyQuery.sql)
    render :action => 'my_query'
  end
end

and a view my_query.rhtml

<table>
  <tr>
  <% for column in @result.content_columns %>
    <th><%= column.human_name %></th>
  <% end %>
  </tr>
  
<% for data in my_query %>
  <tr>
  <% for column in @result.content_columns %>
    <td><%=h @result.send(column.name) %></td>
  <% end %> 
  </tr>
<% end %>
</table>



rx7haze wrote:

Ok, I have a model named my_query.rb that looks like this...


sql = "my_complex_sql_query"
ActiveRecord::Base.connection.execute(sql)

I have a controller my_query.rb 
class MyQueryController < ApplicationController
def index
    render :action => 'my_query'
  end
end

and a view my_query.rhtml

<table>
  <tr>
  <% for column in my_query.content_columns %>
    <th><%= column.human_name %></th>
  <% end %>
  </tr>
  
<% for data in my_query %>
  <tr>
  <% for column in data.content_columns %>
    <td><%=h my_query.send(column.name) %></td>
  <% end %> 
  </tr>
<% end %>
</table>

This doesn't work as I'm not sure how to reference the result object returned by the model. How do I tie this all together?
Does anyone have a simple working example they would be willing to share?
Thanks.

somekool's Website