connect to mysql with ruby

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!

3 Comments

  • February 17, 2010 - 6:43 pm | Permalink

    I am using a combination of PortableApps & XAMPP on WinXP to wrangle many millions of rows of data. Combined with Talend, Strawberry Perl & Ruby I have a complete working environment available on USB devices.

    In fact, I can copy the environment on a HDD, use it & synchronise between a client’s PC and my own.

  • admin
    February 17, 2010 - 6:57 pm | Permalink

    @Adrian
    Would you care to “share” this little jig of yours? hehe…

  • March 29, 2010 - 7:33 pm | Permalink

    More info on my environment is in http://arewemimetic.wordpress.com/2010/03/30/a-portable-data-analysts-environment/. I’m planning a series of howto posts as well. Ad.

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>