Back with more Ruby, I bring you some database connectivity because that’s always an important thing to know about a language.
Prerequisites
This tutorial requires that you have the mysql and dbi gems installed. If you have RubyGems installed, it should be as simple as
#gem install mysql
#gem install dbi
However, if you’re having difficulty, surely Google understands.
Also, this requires a MySql database running on your local machine with a database containing a customer table as follows
CREATE TABLE `customer` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(30) NOT NULL,
`telephone` char(10) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `telephone` (`telephone`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Accessing the Database
require 'rubygems'
gem 'dbi'
require 'dbi'
begin
con = DBI.connect("DBI:Mysql:DATABASE_NAME:localhost","USER","PASSWORD")
result = con.execute "SELECT * FROM customer"
result.fetch_hash do |row|
printf "ID:%d NAME:%s PHONE:%s\n", row["id"], row["name"], row["telephone"]
end
result.finish
rescue DBI::DatabaseError => e
puts "Error, #{e.err}, #{e.errstr}"
ensure
con.disconnect if con
end
Breaking this down into some blocks, the program starts by requiring rubygems and dbi (gem ‘dbi’ allows the dbi libraries to be accessed). DBI is ruby’s interface to use several different databases. In this case we’ll be using MySQL.
The real work begins when we use DBI.connect to create a connection to a database. This connection is what we’ll use to send queries to the database. Be sure to replace DATABASE_NAME, USER, and PASSWORD with the appropriate information for your own database.
Next up, con.execute will return a result set that can be iterated over using fetch_hash. In this case we’ll simply print each result to the console. Notice that rows are hashes that allow you to access information based on column name. Finally, result.finish means we’re done using the result set given to us.
The rescue section simply makes sure to notify of an error if something goes wrong. Also, ensure does just what it says, ensures that the connection is closed if it exists.
Going Further
Inserting Data
Surely the output right now is rather boring because you don’t have any data in your table. Lets put some in, and lets do it with a prepared statement. That way when you build a Ruby app with user input, you’re less susceptible to SQL injection.
cmd = con.prepare "INSERT INTO customer(name,telephone) VALUES(?,?)"
cmd.execute "Bobby","2229998888"
Receiving Entire Datasets and Single Results
Remember working with result.fetch_hash? What about if you want the entire dataset? What about just the first row? You’re in luck, Ruby makes it easy.
#returns an array of DBI::Row objects
result.select_all "SELECT * FROM customer"
#returns the first row
result.select_one "SELECT * FROM customer"
Database access with Ruby is an important skill to know if you’re dealing with storing any sort of information. With this brief overview, grasping the idea should be straight forward.
