How to Manually Execute SQL Commands in Ruby on Rails Using Nuodb

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.

Use rails console sql query strings to learn sql

If you want just one field from one realtion, you have to specify it in the select clause in SQL. Something like this

SELECT styles.retail_price FROM styles WHERE styles.id = 1

In this case, you obtain just the retail_price but in an array if you use rails's execute method. If you want just the single value use select_value method, not execute. Like

ActiveRecord::Base.connection.select_value("SELECT styles.retail_price FROM styles WHERE styles.id = 1")

In the sqlite3 terminal you obtain a syntax error because of this [["id", 1]]. That's a rails/applicative-level stuff used for prepared statements but is not valid SQL. You should just substitute the id like I did.

Rails ActiveRecord .where method doesn't execute SQL query

Every function/method returns the last evaluated value.
In your case it's
where("lower(#{field_name}) like ?", "%#{param}%") if rigor == 'soft'

which returns nil if rigor is not 'soft'.
So adding a return should doing what you want:

 def self.matches(field_name, param, rigor = 'exact')
    return where("lower(#{field_name}) like ?", "#{param}") if rigor == 'exact'
    where("lower(#{field_name}) like ?", "%#{param}%") if rigor == 'soft'
  end

Calling Database Procedure From Ruby on Rails

So I totally forgot about this but I solved this issue a while back and here's a sample of what I did:

SAMPLE_PROCEDURE:

CREATE PROCEDURE sample_procedure ( IN input_1 INTEGER ) 
    RETURNS return_msg ( col_1 STRING , col_2 INTEGER ) AS 
            VAR value_string STRING; 
            VAR value_integer INTEGER;
            value_string = input_1;
            value_integer = input_1+10;
        INSERT INTO return_msg VALUES ( value_string , value_integer);
    RETURN; 
END_PROCEDURE

And here is how I call it:

ActiveRecord::Base.connection.execute("call sample_procedure(1)")

Rails would return the following:

[{"col_1"=>"1", "col_2"=>11}]

I hope this helps.

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.

How to understand when a activerecord query is executed?

None of the above acutally hit's the database unless you do something with them.

users = User.where(org_id: 15) only creates an object that represents the query, if you run that line on a console, the interpreter will hit the database because it will try to render the collection, but if you run that inside a script it WON'T run the actual query.

Same for the second line, if you run users.where(....) on the console, the interpreter will try to render the collection so it will hit the database, but inside a script it won't run the query until you actually do something with that.

You can check the server log to understand it better. If you do those two lines in your rails app, you won't see any query unless you do something like users.each do |user| .... or users.first or user.to_a or something similar that actually requires the query to be run.

---

Related Topics