Ruby - Connect to MySQL without Rails

This post demonstrates how to connect to MySQL from Ruby, insert, select and delete table rows.

Installation Details

These are the stuff currently installed on my Windows 10 box.

Notepad++
Ruby
- ruby 2.3.1p112 (2016-04-26 revision 54768) [x64-mingw32]
Gem 2.6.7
mysql2 (install gem using the command below)
On Windows command-prompt
MS DOS
gem install mysql2
1
gem install mysql2

For more information on mysql2, visit https://github.com/brianmario/mysql2
eventmachine (install gem using the command below)
On Windows command-line prompt
MS DOS
gem install eventmachine
1
gem install eventmachine

Install the gems

MySQL Server Details

You will be connecting to a MySQL Server and therefore it has to be installed first. Then, create a database schema rubydb.

Database Table

Create a database table persons using the following SQL DDL

CREATE TABLE `rubydb`.`persons` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `rubydb`.`persons` (

`id` INT NOT NULL AUTO_INCREMENT,

`name` VARCHAR(45) NULL,

PRIMARY KEY (`id`));

Our Ruby Codes

require "mysql2/em"   

@db_host  = "localhost"
@db_user  = "root"
@db_pass  = "whatever"
@db_name = "rubydb"

client = Mysql2::Client.new(:host => @db_host , :username => @db_user, :password => @db_pass, :database =>@db_name)

# We insert some data using MySQL multi-row insert
client.query("insert into persons(name) values('Mark'), ('Osman'), ('Conan')")

# Retrieve all rows from the persons table
results = client.query("SELECT * FROM Persons")

# Display all fields
results.each do |row|
  puts row["id"].to_s() + "|" + row["name"]
end

# Clear the table
client.query("delete from persons")