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

Question

I'm trying to manually execute SQL commands so I can access procedures in NuoDB.

I'm using Ruby on Rails and I'm using the following command:

ActiveRecord::Base.connection.execute("SQL query")

The "SQL query" could be any SQL command.

For example, I have a table called "Feedback" and when I execute the command:

ActiveRecord::Base.connection.execute("SELECT `feedbacks`.* FROM `feedbacks`")

This would only return a "true" response instead of sending me all the data requested.

This is the output on the Rails Console is:

SQL (0.4ms)  SELECT `feedbacks`.* FROM `feedbacks`
 => true

I would like to use this to call stored procedures in NuoDB but upon calling the procedures, this would also return a "true" response.

Is there any way I can execute SQL commands and get the data requested instead of getting a "true" response?

score 241 · Accepted Answer · edited Jan 23 '19 at 10:24

241

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.

edited Jan 23 '19 at 10:24

Jan Klimo

4,643
2
36
42

answered May 27 '15 at 05:40

Patrick Angodung

4,625
3
14
18

5

it's better to use exec_query if you are on PSQL because it will leak memory – 23inhouse Jan 05 '17 at 13:51
3

I cannot find the difference between the code in your question and in your answer. They both seem to use `ActiveRecord::Base.connection.execute`. Could you please point out what exactly you changed to get the data instead of just `true`? – RocketR Apr 12 '18 at 13:29
I'm guessing the difference is that `ActiveRecord::Base.connection.execute` is now assigned to `results` – CLSA Jun 02 '23 at 10:02

score 186 · Answer 2 · answered Mar 30 '16 at 17:31

186

For me, I couldn't get this to return a hash.

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

But using the exec_query method worked.

results = ActiveRecord::Base.connection.exec_query(sql)

answered Mar 30 '16 at 17:31

Tim Park

2,446
2
11
13

24

`.exec_query` returns an `ActiveRecord::Result` object which is very handy with easily accessible `.columns` and `.rows` attributes. `.execute` returns an array of hashes which is usually more troublesome to deal with and probably heavier on memory. I had never used `exec_query`, thanks for the tip. – Frâncio Rodrigues Dec 17 '17 at 20:57
19

Just to add to the last comment, you'd typically want to use `.entries` when using `.exec_query` to get the results as an array of hashes. – 8bithero Mar 11 '18 at 20:06
This always gives me nil for the results with ActiveRecord 5 running a DELETE query? – Tom Rossi Jun 05 '19 at 21:38

score 46 · Answer 3 · edited Jul 14 '14 at 15:25

46

Reposting the answer from our forum to help others with a similar issue:

@connection = ActiveRecord::Base.connection
result = @connection.exec_query('select tablename from system.tables')
result.each do |row|
puts row
end

edited Jul 14 '14 at 15:25

nobody

19,814
17
56
77

answered Jul 14 '14 at 15:16

MFredrickson-NuoDB

461
4
3

score 29 · Answer 4 · answered Apr 22 '15 at 22:17

29

res = ActiveRecord::Base.connection_pool.with_connection { |con| con.exec_query( "SELECT 1;" ) }

The above code is an example for

executing arbitrary SQL on your database-connection
returning the connection back to the connection pool afterwards

answered Apr 22 '15 at 22:17

Andreas Rayo Kniep

5,674
2
30
30

3

Why will you use the connection pool instead of the connection itself? Is there any advantage? Would you have a source about it? – bonafernando May 23 '17 at 21:12
5

@bonafernando, Your database might start throwing "Too many connections" [errors](https://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html) if you have code that uses `ActiveRecord::Base.connection` without calling `ActiveRecord::Base.clear_active_connections!`. See http://api.rubyonrails.org/v5.2/classes/ActiveRecord/ConnectionAdapters/ConnectionPool.html – eremite Jun 29 '18 at 18:08
Yeah, before your answer I've changed and noticed I've never had any other "Too many connections" error. Thanks! – bonafernando Jun 29 '18 at 19:48

score 11 · Answer 5 · answered May 06 '21 at 06:10

11

Once you get the MySql::Result object

results = ActiveRecord::Base.connection.execute(query)

You can convert it to array of rows

results.to_a

will make array of this format

[[row1][row2]...]

answered May 06 '21 at 06:10

Harsh Kumar

334
2
12

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

5 Answers5

Linked