Rails 3 execute custom sql query without a model

Question

I need to write a standalone ruby script that is supposed to deal with database. I used code given below in rails 3

@connection = ActiveRecord::Base.establish_connection(
:adapter => "mysql2",
:host => "localhost",
:database => "siteconfig_development",
:username => "root",
:password => "root123"
)

results = @connection.execute("select * from users")
results.each do |row|
puts row[0]
end

but getting error:-

`<main>': undefined method `execute' for #<ActiveRecord::ConnectionAdapters::ConnectionPool:0x00000002867548> (NoMethodError)

what i am missing here?

SOLUTION

After getting solution from denis-bu i used it following way and that worked too.

@connection = ActiveRecord::Base.establish_connection(
            :adapter => "mysql2",
            :host => "localhost",
            :database => "siteconfig_development",
            :username => "root",
            :password => "root123"
)

sql = "SELECT * from users"
@result = @connection.connection.execute(sql);
@result.each(:as => :hash) do |row| 
   puts row["email"] 
end

denis-bu · Accepted Answer · 2013-12-09T08:51:31.180

168

Maybe try this:

ActiveRecord::Base.establish_connection(...)
ActiveRecord::Base.connection.execute(...)

edited Dec 09 '13 at 08:51

answered Mar 14 '13 at 11:48

denis-bu

3,426
1
17
11

14

FWIW, Using empty params is not idiomatic ruby. So do `connection.execute` rather than `connection().execute` – radixhound Nov 14 '13 at 03:38
Works on Rails 4 also. – Leigh McCulloch Jan 06 '15 at 16:07
https://github.com/igorkasyanchuk/execute_sql less code is needed plus you can do it in directly rails console – Igor Kasyanchuk Mar 13 '19 at 21:58

Sachin R · Answer 2 · 2014-07-18T05:37:37.900

100

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

edited Jul 18 '14 at 05:37

answered Mar 14 '13 at 11:52

Sachin R

11,606
10
35
40

8

In this case, connection = ActiveRecord::Base.connection; connection.execute("SQL query") would work. Less typing! – Watusimoto Nov 14 '13 at 16:29
16

Or just `ActiveRecord::Base.connection.execute("SQL query")` – Kasper Grubbe Jul 27 '14 at 01:01
3

This connection may have to be returned to the connection pool afterwards to avoid threading issues: `ActiveRecord::Base.connection_pool.checkin(connection)` http://apidock.com/rails/ActiveRecord/ConnectionAdapters/ConnectionPool – lee Oct 04 '14 at 18:05

score 39 · Answer 3 · edited May 23 '17 at 12:10

I'd recommend using ActiveRecord::Base.connection.exec_query instead of ActiveRecord::Base.connection.execute which returns a ActiveRecord::Result (available in rails 3.1+) which is a bit easier to work with.

Then you can access it in various the result in various ways like .rows, .each, or .to_hash

From the docs:

result = ActiveRecord::Base.connection.exec_query('SELECT id, title, body FROM posts')
result # => #<ActiveRecord::Result:0xdeadbeef>


# Get the column names of the result:
result.columns
# => ["id", "title", "body"]

# Get the record values of the result:
result.rows
# => [[1, "title_1", "body_1"],
      [2, "title_2", "body_2"],
      ...
     ]

# Get an array of hashes representing the result (column => value):
result.to_hash
# => [{"id" => 1, "title" => "title_1", "body" => "body_1"},
      {"id" => 2, "title" => "title_2", "body" => "body_2"},
      ...
     ]

# ActiveRecord::Result also includes Enumerable.
result.each do |row|
  puts row['title'] + " " + row['body']
end

note: copied my answer from here

As stated, exec_query returns the actual results - what I want, while execute just returns status information. — Paul Chernoch, Jun 09 '16 at 17:23
As of Rails-7.0 and Ruby-3.1, `.to_hash` is undefined ([Ver.7.0.4 doc](https://api.rubyonrails.org/v7.0.4/classes/ActiveRecord/Result.html)). Instead, `.to_a` returns an Array of Hash. Make sure the column names are unique in using `.to_a`, e.g., specify `"SELECT 3 a,4 b,5 c;"` as opposed to `"SELECT 3,4,5;"`. — Masa Sakano, Nov 05 '22 at 14:00

montrealmike · Answer 4 · 2014-09-23T16:42:45.077

25

You could also use find_by_sql

# A simple SQL query spanning multiple tables
Post.find_by_sql "SELECT p.title, c.author FROM posts p, comments c WHERE p.id = c.post_id"
> [#<Post:0x36bff9c @attributes={"title"=>"Ruby Meetup", "first_name"=>"Quentin"}>, ...]

edited Sep 23 '14 at 16:42

answered Oct 24 '13 at 12:43

montrealmike

11,433
10
64
86

my script is outside rails application so i don't think find_by_sql would work. – Neeraj Oct 25 '13 at 10:16
4

you're right, i read your question too quickly. This answer might help others who come to this page based on your question title. – montrealmike Oct 26 '13 at 19:50
how to get not an array but relation? – Малъ Скрылевъ Jul 12 '16 at 12:15

ant · Answer 5 · 2013-03-14T11:51:55.803

4

How about this :

@client = TinyTds::Client.new(
      :adapter => 'mysql2',
      :host => 'host',
      :database => 'siteconfig_development',
      :username => 'username',
      :password => 'password'

sql = "SELECT * FROM users"

result = @client.execute(sql)

results.each do |row|
puts row[0]
end

You need to have TinyTds gem installed, since you didn't specify it in your question I didn't use Active Record

edited Mar 14 '13 at 11:51

answered Mar 14 '13 at 11:45

ant

22,634
36
132
182

1

Sure, TinyTds is whole other gem.. It is not ActiveRecord. To use it you should install it separately. – denis-bu Mar 14 '13 at 11:50
1

I don't want to use any additional GEM as long as mysql2 and active-record is available – Neeraj Mar 14 '13 at 11:56
Good, you should mention that in your question (tag/title) – ant Mar 14 '13 at 12:02
2

TinyTDS works with MSSQL and Sybase, it doesn't work with MySQL. ant: *You* should mention that in your answer, and not assume that the OP is using the same niche DBMS as you are. – cliffordheath Aug 14 '15 at 07:31

Rails 3 execute custom sql query without a model

5 Answers5

Linked

Related