Talking Directly to a MySQL Database

Table of contents:

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, its better to use the generic DBI library. The biggest exception is when you e writing a a Rails application, and you need to run a SQL command that you can express with ActiveRecord.^[8]

^[8] You could use DBI with ActiveRecord, but most Rails programmers go straight to the database.

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 thats pretty similar to DBIs.

Heres a MySQL-specific version of the method with_db, defined in this chapters introduction. It returns a Mysql object, which you can use to run queries or get server information.

	require 
ubygems
	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 its a SELECT query or something else. When it runs a query, the return value is a result-set object (a MysqlRes); otherwise, its 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
e the only one who really understands me.")})
	end

And heres 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
e 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 e 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. Heres 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
e 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 rows 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
e the only one who really understands me.

The MySQL interface provides no protection against SQL injection attacks. If you e sending SQL containing the values of possibly tainted variables, youll need to quote those values yourself.

Problem

Solution

Discussion

See Also