CODEWITHSUNDEEP
mysqlruby-on-railsruby
user2770152
user2770152

Reputation: 91

How to run simple MYSQL query using rails

I want run a simple MYSQL query using rails

Select movie-title, movie-director from moving order by rating desc limit 5;

I don't want all the overhead creating models. I just want to run the query.

What is the best way to do this?
I cannot even connect

Here is my the code from my controller

ActiveRecord::Base.establish_connection ({
:adapter => "mysql2",
:host => "some-rds.amazon.com",
:username => "root",
:password => "root",
:database => "blah"})

This will generate this error ActiveRecord::ConnectionNotEstablished

thanks

Upvotes: 9

Views: 15239

Answers (4)

Parthasarathy S
Parthasarathy S

Reputation: 317

Config the database connection in

/config/database.yml

In Controller, 

def connect_and_fetch
    result_obj =  ActiveRecord::Base.connection.execute("select column from table")
    @rows = []
    index = 0
    result_obj.each do |row|
      @rows[index] = row
      index += 1
    end
end

In html view,

Result : <%= @rows%>

Upvotes: 1

Jesus Gonzalez
Jesus Gonzalez

Reputation: 127

Hey for the fist steep make sure you have the eight gem and install support for Mysql:

Configuring Rails Applications

In this section, we will modify the Rails application servers so that they start working with the database server we have just set up.

Installing Database Server Libraries

The first thing to do is installing the necessary database libraries. In our case, it is MySQL's development package.

Run the following to install MySQL development package mysql-devel:

yum install -y mysql-devel

Configuring database.yml For Rails

Database settings for Rails applications are kept inside the database.yml file in /config directory.

Run the following command to edit the database.yml file using the nano text editor:

# Make sure to enter your application deployment directory
# Example:
# cd /var/www/my_app

nano config/database.yml

Once you open up this file, you will see database settings, divided by environment names. Since an application needs to run using the production environment, let's edit the configuration for that.

Replace the production: YML code block with the following, changing the necessary bits to suit your own set-up configuration, e.g. the IP address etc.

# Example:
# production:
#   adapter: mysql
#   encoding: utf8
#   database: [database name]
#   username: [user name]
#   password: [password]
#   host: [server IP address]
#   port: [port number]
#   protocol: [protocol]
#   pool: [connection pool]

production:
  adapter: mysql
  encoding: utf8
  database: rails_myapp
  username: rails_myapp_user
  password: pwd
  host: 128.199.233.36
  port: 3306
  pool: 10

Note: As provided in the example above, you might need to specify the protocol.

Note: The pool argument contains the number of maximum simultaneous database connection slots (i.e. pool) available. You need to assess your needs and set a number accordingly.

Save and exit by pressing CTRL+X and confirming with Y.

Getting The mysql Gem

Start editing the Gemfile using nano using the following:

nano Gemfile

Add the following line to the file:

gem 'mysql'

Save and exit by pressing CTRL+X and confirming with Y.

Install the new gem using bundle:

bundle install

And that's it! From now on, your Rails application servers will be using your brand new database server for all operations.

you can find more info in: https://www.digitalocean.com/community/tutorials/scaling-ruby-on-rails-setting-up-a-dedicated-mysql-server-part-2

Upvotes: 0

Rajesh Omanakuttan
Rajesh Omanakuttan

Reputation: 6918

Just use ModelName.find_by_sql("your sql query") in your console.

Upvotes: 4

Zabba
Zabba

Reputation: 65467

movie_title = 'Planet of the Apes'
sql = "SELECT * FROM movies WHERE title = #{ActiveRecord::Base.sanitize(movie_title)}"
ActiveRecord::Base.connection.execute(sql)

Upvotes: 16

Related Questions