How do you manually execute SQL commands in Ruby On Rails using NuoDB
SqlRuby on-Rails-3ActiverecordNuodbSql Problem Overview
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.
Like 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 anyway I can execute SQL commands and get the data requested instead of getting a "true" response?
Sql Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
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)
Solution 3 - Sql
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
Solution 4 - Sql
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
Solution 5 - Sql
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]...]