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?

Jan Klimo · Accepted Answer · 2019-01-23 10:24:03Z

251

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, 2019 at 10:24

Jan Klimo

4,9702 gold badges39 silver badges45 bronze badges

answered May 27, 2015 at 5:40

Patrick Angodung

4,8513 gold badges16 silver badges18 bronze badges

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

4 Comments

23inhouse Over a year ago

it's better to use exec_query if you are on PSQL because it will leak memory

RocketR Over a year ago

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?

CLSA Over a year ago

I'm guessing the difference is that ActiveRecord::Base.connection.execute is now assigned to results

Victor Pudeyev Over a year ago

This is completely useless - I guess OP is karma farming.

Tim Park · Accepted Answer · 2016-03-30 17:31:19Z

195

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, 2016 at 17:31

Tim Park

2,6462 gold badges14 silver badges13 bronze badges

3 Comments

Frâncio Rodrigues Over a year ago

.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.

8bithero Over a year ago

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.

Tom Rossi Over a year ago

This always gives me nil for the results with ActiveRecord 5 running a DELETE query?

nobody · Accepted Answer · 2014-07-14 15:25:34Z

47

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, 2014 at 15:25

nobody

20.3k17 gold badges59 silver badges80 bronze badges

answered Jul 14, 2014 at 15:16

MFredrickson-NuoDB

4714 silver badges3 bronze badges

Comments

Andreas Rayo Kniep · Accepted Answer · 2015-04-22 22:17:41Z

30

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, 2015 at 22:17

Andreas Rayo Kniep

6,6903 gold badges35 silver badges31 bronze badges

3 Comments

bonafernando Over a year ago

Why will you use the connection pool instead of the connection itself? Is there any advantage? Would you have a source about it?

eremite Over a year ago

@bonafernando, Your database might start throwing "Too many connections" errors if you have code that uses ActiveRecord::Base.connection without calling ActiveRecord::Base.clear_active_connections!. See api.rubyonrails.org/v5.2/classes/ActiveRecord/…

bonafernando Over a year ago

Yeah, before your answer I've changed and noticed I've never had any other "Too many connections" error. Thanks!

Harsh Kumar · Accepted Answer · 2021-05-06 06:10:27Z

13

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 6, 2021 at 6:10

Harsh Kumar

4563 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

4 Comments

3 Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

3 Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related