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, 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.

See Also

Related

Категории