Establishing Mysql DB Connectivity from Ruby

If you are a rails fan you will probably recommend using active record instead, but if you just want to hack away at your mysql db from within your Ruby code, you may find this handy…

I’m currently using a wamp box to serve up content for performance metrics. What I needed was a Ruby script which could interrogate data on this installation.

You can download the binary required for this from here. After you install the gem (I do this off-line as my command prompt doesn’t allow me internet access here at work) you may find that you get an error similar to the following:
"NameError: uninitialized constant Mysql"

See what’s happening?

To find out what’s happening, you can jump into an irb terminal and type the following:
irb(main):002:0> require 'mysql'

When you do you will probably get a popup dialog like this:

Basically if you read the README that comes with the gem you will notice:

Dependencies …
For this to work, you must have libmysql.dll in your PATH environmental variable. If you have MySQL installed locally, just make sure that \bin is in your path. If you don’t have MySQL installed locally, you can install one or more of the MySQL tools, find the libmysql.dll included in their bin directory, and copy it to the %SystemRoot%\System32 folder.

So all you need to do is find out where your libmysql.dll lives:
D:\wamp>dir /b /s LIBMYSQL.dll
D:\wamp\bin\apache\apache2.2.8\bin\libmysql.dll
D:\wamp\bin\mysql\mysql5.0.51a\bin\libmySQL.dll
D:\wamp\bin\php\php5.2.5\libmysql.dll

And then add that bin directory to your environment path:
D:\wamp\bin\mysql\mysql5.0.51a\bin

Hope that saves some of you some time in getting connectivity on your windows boxes!
Attached below is some sample code to test your config.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

require 'rubygems'
require 'mysql'
 
def with_db
    dbh = Mysql.real_connect('localhost', 'myuser', 'mypass', 'mydb', 3306)
    begin
        yield dbh
    ensure
        dbh.close
    end
end
 
with_db do |db|
    res = db.query('select * from tablename')
    res.each do |row|
        puts row[1]
    end
end