mishmashmoo

Often, when I begin a new gig, I have a few little tools I install to make my job that little bit more efficient.
One of these would be a wamp server and the ruby installer, along with the usual gems I often utilise. The wamp server is great to store data for scripts I’m writing, and a little ruby here and there never hurt anybody!
It occurred to me that I’d never actually blogged how to get ruby and mysql connectivity. As with anything ruby related, its easy but it does require a few key steps..

You’ll obviously need ruby installed, and the wamp server. Once that’s done, install the mysql gem:

c:>gem install mysql --no-ri --no-rdoc

This will install without ri and rdocs. Some definition errors will occur if you install the gem with ri and rdoc, this is due to the location of mysql, however it’s fine, it’ll still work!

Once that’s done, locate your copy of libmysql.dll. It’s usually found in c:wampbinmysqlmysqVERSIONlbin
Take a copy of it and dump it in your location of ruby. Generally C:Rubybin.

Ok so that’s that part done. Now for the serious stuff…
Here’s the code. Its pretty self explanatory.

query = "select columnA, ColumnB from table"
begin
     dbh = Mysql.real_connect("localhost", "login", "password", "databaseName")
     # get server version string and display it
     puts "Server version: " + dbh.get_server_info
   rescue Mysql::Error => e
     puts "Error code: #{e.errno}"
     puts "Error message: #{e.error}"
     puts "Error SQLSTATE: #{e.sqlstate}" if e.respond_to?("sqlstate")
   ensure
      res = dbh.query(query)
   while row = res.fetch_row do
      puts row[0]
      puts row[1]
   end
   puts "Number of rows returned: #{res.num_rows}"
   res.free
     # disconnect from server
     dbh.close if dbh
end

And thats it!
Enjoy!