Ruby MySQL Tutorial

This brief Ruby MySQL Tutorial shows you how you can connect to MySQL in Ruby. MySQL support in Ruby was made possible by Tomita Masahiro. He has developed a pure Ruby binding called Ruby/MySQL. We need to install the same on our PC and the installation (you need to be connected to the internet and it takes some time) is as shown below:

C:\>gem install mysql
Bulk updating Gem source index for: http://gems.rubyforge.org
Select which gem to install for your platform (i386-mswin32)

 1. mysql 2.7.1 (mswin32)

 2. mysql 2.7 (ruby)

 3. mysql 2.6 (ruby)

 4. mysql 2.5.1 (ruby)

 5. Cancel installation

> 1
Successfully installed mysql-2.7.1-mswin32
Installing ri documentation for mysql-2.7.1-mswin32...
Installing RDoc documentation for mysql-2.7.1-mswin32...
C:\>

This installs mysql-2.7.1-mswin32 driver which is faster and supports MySQL 4.1 and later. The documentation for this driver is here.

I will assume that you’ve already installed mySQL 4.1 or above on your PC and that you have it running and are familiar with the basics.

Now, run the mysql client program from the command line, as:

C:\>mysql

You should get the mysql prompt. Next, create a database ruby as:

mysql> create database ruby;
Query OK, 1 row affected (0.02 sec)

Next, create a table student in the database ruby as:

mysql> use ruby;
create table student (id VARCHAR(2), name VARCHAR(20), rank VARCHAR(2));

As a first exercise we try to connect to the MySQL server and print all the names in the table student. Program p078rubymysql.rb

require 'mysql'

#my = Mysql.new(hostname, username, password, databasename)
con = Mysql.new('localhost', '', '', 'student')
rs = con.query('select * from student')
rs.each_hash { |h| puts h['name']}
con.close

It is as simple as that. You can explore this api further.

Technorati Tags: , ,

Posted by Satish Talim

   Posted in Code Snippets, Ruby    

Subscribe RSS 2.0 feed. Leave a response, or Trackback from your own site.





5 Responses to 'Ruby MySQL Tutorial'

  1. Dick Monahan - May 16th, 2007 at 5:57 pm

    As soon as I type require ‘mysql’, it crashes. Any ideas? I have tried both 2.7.1 and 2.7.3. I have MySql 5.0.41.

    D:\rem>irb
    irb(main):001:0> require ‘mysql’
    LoadError: 998: Invalid access to memory location. - c:/Program Files/ruby/lib/ruby/gems/1.8/gems/mysql-2.7.1-mswin32/ext/mysql.so
    from c:/Program Files/ruby/lib/ruby/gems/1.8/gems/mysql-2.7.1-mswin32/ext/mysql.so
    from c:/Program Files/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:32:in `require’
    from (irb):1
    irb(main):002:0>

  2. satish - May 16th, 2007 at 7:00 pm

    Some .dll problem? See this thread -
    http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/29579

  3. Walker - May 24th, 2007 at 1:53 am

    I’m new to ruby and mysql. A Mysql::result object erases itself after running each_hash on it once. By erasing itself I mean that you won’t get any results if you run the each_hash method on the same object again. Can you help me understand why this is? I’ve noticed this behavior on some of the other functions, and can kind of guess why it is happening, but I want to gather more information from anyone who knows more. Thanks!

  4. Paul - May 29th, 2007 at 4:43 am

    I am a newbie to rails and I found I was having the same problem. I think that it is a problem with the libmysql.dll. See this mysql bug. http://bugs.mysql.com/bug.php?id=28358

    Fortunately I pulled off the libmysql.dll from another machine (sorry I didn’t check which exact version of mysql is running on my other computer but the libmysql.dll has a creation date of Oct. 22, 2006. Once I replaced the libmysql.dll in the mysql/bin directory on my machine, I was able to run irb and issue the following commands:

    require ‘mysql’
    puts Mysql::VERSION

    Here’s where I got the low down on those mysql gems in case you need it.
    http://www.vandomburg.net/pages/mysql-ruby-windows

  5. Tim - January 5th, 2008 at 9:30 am

    Regarding the observation that each_hash seems to erase the result set: I just ran into this myself, and it took a while to figure it out. You need to call data_seek(0) on the Result object after invoking each_hash, because each_hash advances the record pointer to the end of the result set. If you call each_hash again without seeking back to record 0, each_hash will immediately return because the record pointer is already at the end.

    Although this question is 7 months old, I am recording this answer for posterity, since my web searches for an answer lead me here first.


Leave a Reply

RUBYGALORE.COM