Invoking a Large Set of SQL from a Rails 4 Application

How can execute multiple statements in one query with Rails?

It should work out of the box with PostgreSQL, checked with pg gem and rails 3.2:

class Multitest < ActiveRecord::Migration
  def up
    execute <<-SQL
      create table x(id serial primary key);
      create table y(id serial primary key, i integer);
    SQL
  end

  def down
  end
end

On a side note, manipulating schema_migrations directly looks strange.

How can I import a SQL file into a Rails database?

The easiest way:

bundle exec rails db < $SQL_FILE

example:

bundle exec rails db < my_db.sql

Rails raw SQL example

You can do this:

sql = "Select * from ... your sql query here"
records_array = ActiveRecord::Base.connection.execute(sql)

records_array would then be the result of your sql query in an array which you can iterate through.

How do you manually execute SQL commands in Ruby On Rails using NuoDB

The working command I'm using to execute custom SQL statements is:

results = ActiveRecord::Base.connection.execute("foo")

with "foo" being the sql statement( i.e. "SELECT * FROM table").

This command will return a set of values as a hash and put them into the results variable.

So on my rails application_controller.rb I added this:

def execute_statement(sql)
  results = ActiveRecord::Base.connection.execute(sql)

  if results.present?
    return results
  else
    return nil
  end
end

Using execute_statement will return the records found and if there is none, it will return nil.

This way I can just call it anywhere on the rails application like for example:

records = execute_statement("select * from table")

"execute_statement" can also call NuoDB procedures, functions, and also Database Views.

Rails 4: Show SQL in console in production

ActiveRecord::Base.logger = Logger.new(STDOUT)

Execute it in rails console on your server, and then all the ActiveRecord generated SQL queries will be shown.

Processing large recordsets in Rails

You want to use ActiveRecord's find_each for this.

Dataset.find_each do |data|
  ...
end

Are you in favour of in-memory filtering or using SQL queries on large number of records in a ruby on rails app?

Letting the database do its job is always best. Just write the query with a where clause/join/etc. and use a sort by to return only the records you need. By returning more records and then filtering the data in rails wastes the amount of the data across the "wire" (between DB and app server) and also memory. Databases are optimized for these sorts of things. Use an index on the table as necessary.

Another similar post: http://www.mail-archive.com/rubyonrails-talk@googlegroups.com/msg13484.html

---

Related Topics