Ruby database recipes with MySql

First of all I am using Windows as OS (yes, I have to use it here).

Rails has a very nice ORM: ActiveRecord. However ActiveRecord is quite complex and a simple library to manipulate data in a script can be more than enough. In this post I will use MySql as DBMS. First of all you need the MySql adapter and the MySql gem:

gem install mysql

If you have troubles when installing the gem and you have the MySql 5.1 client, download and install the 5.0 instead, there are, at the moment of writing some issues in Windows. Now you can check if you can connect to the database. The following example is the code to manipulate data in a MySql table named users. As you can see it is not so different from the previous example, but it can be use with other DBMSs, this is the beauty of a database abstracion layer, using the adapter design pattern.

require 'dbi'

# Connect to a database, old style
dbh = DBI.connect('DBI:Mysql:test:localhost', 'root', 'asdf')

# Insert some rows, use placeholders
1.upto(13) do |i|
sql = "insert into users (username) VALUES (?)"
dbh.do(sql, "user#{i}")
end

# Select all rows from users
sth = dbh.prepare('select * from users')
sth.execute

# Print out each row
while row=sth.fetch do
p row
end

# Close the statement handle when done
sth.finish

# Don't prepare, just do it!
dbh.do('delete from users where id > 10')

# And finally, disconnect
dbh.disconnect

Some useful links:

• Install and test the Ruby API
• RDoc for DBI
• Ruby DBI home page