Ruby and MySQL Tutorial

1. Introduction

Mysql is a powerful, fast and popular open source relational database management systems and Ruby is a very powerful open source programming language. I want to show you how to use these two powerful tools together to create database powered applications. I will show you how to use the mysql-ruby module. At the end I will also show you how to use Ruby/DBI together with MySQL.

All the examples have been testet with the following sytem:

• Red Hat Linux 7.3


• Ruby 1.7.3


• Mysql-Max 4.0.x (rpm version)


• mysql-ruby-2.4.x

Some things might be different with MySQL versions smaller than 4.0.

I also have not tested the stuff on a Windows machine, but it should there too. If you have any comments on how to improve this document, please let me know. My native tongue is German and not English, so I am sure that there are some errors in these documents like grammer errors and wrong usage of words. If your english is better than mine and you find some errors, please tell me.

Notation:

I use the same Notation for class methods and instance methods as used in the
Book "Programming Ruby" by David Thomas and Andrew Hunt (For more informations about books
see the end of this tutorial). Example:

Mysql.new means that new is a class method of class Mysql and

Mysql#close means that close is an instance method of a Mysql object.

2. The sample database

First of all, we have to create our sample database. This database will be very, very simple (this document is not about database design). It will be called sampledb and will have one table called words. This table will have three columns holding the translation of some words for the languages German, English and French. First I will show you how to create the sample database using just SQL, and then I will show you how you can do this with Ruby.

Fire up your favourite editor and type (or copy and paste) the following code in the editor:

---

CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE words
(
    german varchar(30),
    english varchar(30),
    french varchar(30)
);

INSERT INTO words VALUES('Adler', 'eagle', 'aigle');
INSERT INTO words VALUES('Haus', 'house', 'maison');
INSERT INTO words VALUES('Name', 'name', 'nom');
INSERT INTO words VALUES('Wal', 'whale', 'baleine');

GRANT ALL ON sampledb.* TO rubyuser@localhost IDENTIFIED by 'ruby';

---

I assume that you have MySQL installed and running. In order to create the sample database save this SQL code to a file called create.sql and then type at your command prompt
mysql -u root -p < create.sql

Then enter the root password for MySQL. If you don't know how to install MySQL and set up the password for the root user (this is not the root user of your Linux or Unix system. Give him a different password than the linux root user. This is for security reasons) then checkout the official MySQL documentations available at http://www.mysql.com or check out a good book on MySQL. (For some book recommendations check at the section recommended books.

The SQL code I showed you here will create the sample database, the table words and will insert four rows of data. And it will grant all privileges on this database for the user rubyuser and set the password for this user to rub. I just wanted to show you the SQL code in case you already know MySQL, so you can see what the sample database will loke like. Now I will show you how to use Ruby for this.

3. Using mysql-ruby

3.1 Installation

First, you have to download the MySQL Ruby Module. You can find it at http://www.tmtm.org/en/mysql/
The installation is very simple. Just type

$ ruby extconf.rb

$ make

# make install #(as root or with sudo)

Often you have to specify where the MySQL header files and the MySQL shared libraries are located on your system. On my system I had to enter this command

$ ruby extconf.rb --with-mysql-include=/usr/include/mysql/ --with-mysql-lib=/usr/lib/mysql/

and then you can run make and make install.

3.2 Classes and Methods

The method names for mysql-ruby are generally the same as the underlying C functions but without the "mysql" prefix. And they are part of classes. So the mysql_qurey C functions becomes Mysql#query, that is a method named query in the Mysql class. There are four classes in mysql-ruby:

• Mysql


• MysqlRes


• MysqlField


• MysqlError

The first three are derived from class Object and the last is derived from StandardError.

Mysql is the most important one. You create an instanze of it when you want to connect to MySQL.

MysqlRes is for the results for query that return rows like the SELECT statement. According to the MySQL Reference Manual we will call the results returned by a query "result set".

MysqlField allows you to access information about a field like it's name or size.

MysqlError is a class you need when something goes wrong.

In the next paragraphs in this tutorial I will show you how to use these classes.

3.3 creating the sample database

Now we will see mysql-ruby in action. First, please delete the sampledb in your MySQL installation because we will create it again with Ruby. If you don't delete it, you will get an error message during the execution of the following example.

Here is the code for out first example:

---

#!/usr/local/bin/ruby -w 
# create.rb

require "mysql"

dbname = "sampledb"

m = Mysql.new("localhost", "root", "passwd")
r = m.query("CREATE DATABASE #{dbname}")
m.select_db(dbname)
m.query("CREATE TABLE words
         (
          german varchar(30),
          english varchar(30),
          french varchar(30)
         )"
        )

m.query("INSERT INTO words VALUES('Adler', 'eagle', 'aigle')")
m.query("INSERT INTO words VALUES('Haus', 'house', 'maison')")
m.query("INSERT INTO words VALUES('Name', 'name', 'nom')")
m.query("INSERT INTO words VALUES('Wal', 'whale', 'baleine')")
m.query("GRANT ALL ON sampledb.* TO rubyuser@localhost IDENTIFIED by 'ruby'")

m.close

---

The code is easy to understand. First we load the mysql module.

Then we create a Mysql Object. Mysql.new is an alias to the Method Mysql.real_connect. We pass the host where the MySQL server is running and the username and the password of the root user to the constructor. Then we create the database with the Mysql#query Method. We pass this method the SQL code to create the database. Then we have to call Mysql#select_db. You always have to select a database before you can work on it like inserting data or creating tables as we do here. (This is like "use sampledb" within an interactive mysql session.

After selecting the database we create the tables and then we insert the data. We have to call Mysql#query for every row we want to insert. If you would have a lot of more rows you would do this in a loop of course.

At the end we call Mysql#close in order to close the connection to the MySQL server.

In MySQL versions small than MySQL 4 there was a function called Mysql#create_db("dbname"). But because the underlying C function is deprecated this method is no longer available. The recommended way to create a database according to the MySQL manual is to connect to the MySQL server and to use the mysql_query function and the SQL command CREATE DATABASE dbname, as we did in our example.

If you still want to use the Mysql#create_db function with Mysql 4.X you can write it on your own like this:

require "mysql"

class Mysql
    def create_db(db)
        query("CREATE DATABASE #{db}")
    end
end

Here we add the method Mysql#create_db to the Mysql class. In Ruby you can always add methods to classes like this. You do not have to change the original source code of the class.

In our method we just call Mysql.createdb which is accessible inside the Mysql class because. Here createdbis called on self which is always the current object. For more on this look in one of the Ruby books recommended at the end of this tutorial.

(Another way to add this method is to change the source code of the mysql-ruby module and recompile it.)

Here I show you how to use this newly created method. First save the above code in a file called mymysql.rb, so you can load it with require.

Put it either in the same directory as the following example or in a directory where Ruby looks for modules, for example /usr/local/lib/ruby/site_ruby/1.6 on many Linux systems.

---

#!/usr/local/bin/ruby -w

require "mymysql"

m = Mysql.new("localhost", "root", "passwd")
m.create_db("sampledb")

3.4 Querying a database

In this part I will show you various methods on how to get information about the sample database and on how to retrieve date from it.

Getting field informations

In the next listing you see how to get some informations about the fields returned by a query.

---

#!/usr/local/bin/ruby -w

# example for MysqlRes#fetch_fields and MysqlRes#fetch_rows

require "mysql"

dbname = "sampledb"

m = Mysql.new("localhost", "rubyuser", "ruby")
m.select_db("sampledb")
result = m.query("SELECT * FROM words")
fields = result.fetch_fields
fields.each do |field|
    puts field.name
    puts field.table
    puts field.def
    puts field.type
    puts field.length
    puts field.max_length
    puts field.flags
    puts field.decimals
end

m.close

After creating the Mysql Object and selecting the sample database we use a simple SELECT statement to get data out of the database. In this example we are not interested in the data itself but on informations about the fields of the result set. The underlying C structure for MysqlField is called MYSQL_FIELD and has the following members:

• name: name of the field


• table: name of the table containing the field


• def: default value of the field


• type: type of the field


• length: width of the field


• max_length: maximum length of the field


• flags: various bit flags for the field


• decimals: for numeric fields this contains the number of decimals

For a more detailed explanation of this values please see the MySQL Reference Manual.

The Ruby class MysqlField has methods with the same name as the members of the underlying C structure as you can see in the example above. For many applications you will not be interestedin this values and will not use the MysqlField classes. But for example when you write a management tool for MySQL this values can be very helpful.

Getting data

There are several ways to get data out of a MySQL database. The Mysql#query method returns a MysqlRes object which is a class for the result set returned by a SQL querey. Two very easy to use methods of MysqlRes are MysqlRes#each and MysqlRes#each_hash. First I will show you how to use MysqlRes#each.

#!/usr/local/bin/ruby -w

# example for MysqlRes#each

require "mysql"

dbname = "sampledb"

m = Mysql.new("localhost", "rubyuser", "ruby")
m.select_db("sampledb")
result = m.query("SELECT * FROM words")

result.each do |array|
    array.each do |value|
        puts value
    end
    puts
end

m.close

MysqlRes#each is a iterator method that returns an array of field values for every row of the result set. In our example we just iterate over the array with Array#each and print the value to standart output.

The next example demonstrates MysqlRes#each_hash.

#!/usr/local/bin/ruby -w

# example for MysqlRes#each_hash

require "mysql"

dbname = "sampledb"

m = Mysql.new("localhost", "rubyuser", "ruby")
m.select_db("sampledb")
result = m.query("SELECT * FROM words")
result.each_hash do |h|
    printf("%-12s %-12s %-12s\n", h['english'], h['german'], h['french'])
end
m.close

MysqlRes#each_hash is an iterator method that returns a Hash for each row of the result set where the fieldname is the key and the fieldvalue is the value of the Hash. The next example is very similar. It again uses MysqlRes#each_hash but now we print the names of the fields and the values for each row of the result set using Hash#each.

#!/usr/local/bin/ruby -w

# example for MysqlRes#each_hash

require "mysql"

dbname = "sampledb"

m = Mysql.new("localhost", "rubyuser", "ruby")
m.select_db("sampledb")
result = m.query("SELECT * FROM words")
result.each_hash do |h|
  h.each do |key, value|
    printf("%-12s %-12s\n", key, value)
  end
  puts
end
m.close

Recommended Books

Here a several useful books for Ruby and MySQL users:

Ruby

• Title: Programming Ruby - The Pragmatic Programmer's Guide
  
  Authors: David Thomas, Andrew Hunt
  
  Publisher: Addison-Wesley
  
  (The Ruby bible - a must have)


• Title:The Ruby Way
  
  Author: Hal Fulton
  
  Publisher: Samspublishing

(great cookbook, with a small chapter an using Ruby with MySQL)

• Title: The Ruby Developer's Guide
  
  Authors: Robert Feldt, Lyle Johnson, Michael Neumann
  
  Publisher: Syngress
  
  (This book has a great section about using Ruby/DBI)

MySQL

• Title: MySQL Reference Manual
  
  Authors: Michael Widenius, David Axmark, MySQL AB
  
  Publisher: O'reilly
  
  (Print edition of the MySQL manual written by the creators of MySQL themselves. Here is the Online Version):

This tutorial is published under the GNU Free Documentation License.