Recipe 13.9. Talking Directly to a MySQL Database
Problem
You want to send SQL queries and commands directly to a MySQL database.
Solution
Do you really need to do this? Almost all the time, it's better to use the generic DBI library. The biggest exception is when you're writing a a Rails application, and you need to run a SQL command that you can't express with ActiveRecord.
If you really want to communicate directly with MySQL, use the Ruby bindings to the MySQL client library (found in the mysql gem). It provides an interface that's pretty similar to DBI's.
Here's a MySQL-specific version of the method with_db, defined in this chapter's introduction. It returns a Mysql object, which you can use to run queries or get server information.
require 'rubygems'
require 'mysql'
def with_db
dbh = Mysql.real_connect('localhost', 'cookbook_user', 'password',
'cookbook')
begin
yield dbh
ensure
dbh.close
end
end
The
Mysql#query method runs any SQL statement, whether it's a SELECT query or something else. When it runs a query, the return value is a result-set object (a
MysqlRes); otherwise, it's nil. Here it is running some SQL commands:
with_db do |db|
db.query('drop table if exists secrets')
db.query('create table secrets( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
secret LONGTEXT )')
db.query(%{insert into secrets(secret) values
("Oh, MySQL, you're the only one who really understands me.")})
end
And here's a query:
with_db do |db|
res = db.query('select * from secrets')
res.each { |row| puts "#{row[0]}: #{row[1]}" }
res.free
end
# 1: Oh, MySQL, you're the only one who really understands me.
Discussion
Like the database connection itself, the result set you get from query wants to be closed when you're done with it. This calls for yet another instance of the pattern seen in with_db, in which setup and cleanup are delegated to a method that takes a code block. Here's some code that alters query to take a code block:
class Mysql
alias :query_no_block :query
def query(sql)
res = query_no_block(sql)
return res unless block_given?
begin
yield res
ensure
res.free if res
end
end
end
Now we can write more concise query code, and not have to worry about freeing the result set:
with_db do |db|
db.query('select * from secrets') do |res|
res.each { |row| puts "#{row[0]}: #{row[1]}" }
end
end
# 1: Oh, MySQL, you're the only one who really understands me.
The method MysqlRes#each yields you the rows of a result set as arrays. MysqlRes#each_hash also gives you one row at a time, but in hash form: you can access a row's fields by name instead of position.
MysqlRes#num_rows gives you the number of rows matched by a query.
with_db do |db|
db.query('select * from secrets') do |res|
puts "#{res.num_rows} row(s) matched:"
res.each_hash do |hash|
hash.each { |k,v| puts " #{k} = #{v}" }
end
end
end
# 1 row(s) matched:
# id = 1
# secret = Oh,
MySQL, you're the only one who really understands me.
The MySQL interface provides no protection against SQL injection attacks. If you're sending SQL containing the values of possibly tainted variables, you'll need to quote those values yourself.
See Also
 | 
