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

  • Recipe 13.15, "Preventing SQL Injection Attacks," for more on SQL injection
  • "Using the Ruby MySQL Module" (http://www.kitebird.com/articles/ruby-mysql.html)
  • MySQL bindings (http://www.tmtm.org/en/mysql/ruby/)


Strings

Numbers

Date and Time

Arrays

Hashes

Files and Directories

Code Blocks and Iteration

Objects and Classes8

Modules and Namespaces

Reflection and Metaprogramming

XML and HTML

Graphics and Other File Formats

Databases and Persistence

Internet Services

Web Development Ruby on Rails

Web Services and Distributed Programming

Testing, Debugging, Optimizing, and Documenting

Packaging and Distributing Software

Automating Tasks with Rake

Multitasking and Multithreading

User Interface

Extending Ruby with Other Languages

System Administration



Ruby Cookbook
Ruby Cookbook (Cookbooks (OReilly))
ISBN: 0596523696
EAN: 2147483647
Year: N/A
Pages: 399

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net