MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Ruby-MySQL Program

with 6 comments

After you install Ruby and build the Rails framework, you need to create the mysql gem. This blog post shows you how to create the mysql gem and how to write a simple Ruby program that queries the MySQL database.

The first step creates the mysql gem for Ruby programming:

gem install mysql

It should show you the following:

Fetching: mysql-2.9.1.gem (100%)
Building native extensions.  This could take a while...
Successfully installed mysql-2.9.1
Parsing documentation for mysql-2.9.1
Installing ri documentation for mysql-2.9.1
Done installing documentation for mysql after 0 seconds
1 gem installed

After you install the mysql Ruby Gem, you can write and test a test.rb Ruby program that tests a MySQL database connection. The simplest complete code looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# Include Ruby Gem libraries.
require 'rubygems'
require 'mysql'
 
begin
  # Create new database connection.
  db = Mysql.new('localhost','student','student','studentdb')
  # Print connected message.
  puts "Connected to the MySQL database server."
rescue Mysql::Error => e
  # Print the error.
  puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}"
  puts "Can't connect to the MySQL database specified."
  # Signal an error.
  exit 1
ensure
  # Close the connection when it is open.
  db.close if db
end

You can run the program with the following syntax:

ruby test.rb

The program prints “Connected to the MySQL database server.” when there’s a student user with a student password that’s authorized to connect to the studentdb database. If any of the values are invalid when creating the connection, the program prints “Can’t connect to the MySQL database specified.”

Having tested the connection, the next query.rb program tests the connection by returning values from a query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# Include Ruby Gem libraries.
require 'rubygems'
require 'mysql'
 
# Begin block.
begin
  # Create a new connection resource.
  db = Mysql.new('localhost','student','student','studentdb1')
  # Create a result set.
  rs = db.query('SELECT item_title FROM item')
  # Read through the result set hash.
  rs.each_hash do | row |
    puts "#{row['item_title']}"
  end
  # Release the result set resources.
  rs.free
rescue Mysql::Error => e
  # Print the error.
  puts "ERROR #{e.errno} (#{e.sqlstate}): #{e.error}"
  puts "Can't connect to MySQL database specified."
  # Signal an error.
  exit 1
ensure
  # Close the connection when it is open.
  db.close if db
end

You can test it with the following command-line syntax:

ruby query.rb

It returns a data set like this from the item table of my video store example:

+---------------------------------------+
| item_title                            |
+---------------------------------------+
| The Hunt for Red October              |
| Star Wars I                           |
| Star Wars II                          |
| Star Wars II                          |
| Star Wars III                         |
| The Chronicles of Narnia              |
| RoboCop                               |
| Pirates of the Caribbean              |
| The Chronicles of Narnia              |
| MarioKart                             |
| Splinter Cell                         |
| Need for Speed                        |
| The DaVinci Code                      |
| Cars                                  |
| Beau Geste                            |
| I Remember Mama                       |
| Tora! Tora! Tora!                     |
| A Man for All Seasons                 |
| Hook                                  |
| Around the World in 80 Days           |
| Harry Potter and the Sorcerer's Stone |
| Camelot                               |
+---------------------------------------+
22 rows in set (0.00 sec)

You need the ruby interpreter to run them. You can make the programs standalone operations by putting the following line as the first line in your Ruby programs.

1

#!/usr/bin/ruby

Then, you can run the program like this if they have read and execute privileges and are located in the present working directory where you issue the following command:

./mysql_query.rb

If you want to work with individual columns, please check this subsequent post that shows how you can access individual columns. As always, I hope this helps those trying to get things working.

After posting this somebody asked for books that could help them learn how to write Ruby programs. While books are nice and listed below, I’d start with the tryruby.org web site.

I’d recommend the following books because …

  • The Ruby Programming Language is 7 years old now and only covers Ruby 1.8 and 1.9, but its written by David Flanagan and the creator of the Ruby Programming Language – Yukihiro Matsumoto.
  • Programming Ruby 1.9 & 2.0: The Pragmatic Programmer’s Guide is more current and a well balanced approach at learning how to write Ruby programs.
  • The Well-Grounded Rubyist is the most current book and teaches you how to think about writing Ruby beyond just the syntax. As a Manning book, you can purchase the physical copy and automatically get a downloadable ebook. It’s certainly the best value for the money option provided you already know how to program in at least one other object-oriented programming language.

Written by maclochlainn

April 11th, 2015 at 2:35 am

Posted in Fedora,LAMP,Linux,MySQL,Ruby,sql

Tagged with ,

«
»