I recently found myself in an interesting situation. I needed to extract data from a database. Unfortunately, all I had to work with was an ODBC name.

If this were a MySQL database, I would have used the command-line interface to check out the structure and see what was available.

In this case, I didn’t have a command-line interface. What I did have was Ruby and irb. Here’s how I got the job done.

Required Gems

Three gems are required to make this work. First, we need dbi and a driver (or dbd). The dbd-odbc driver also requires the ruby-odbc gem.

dbi
dbd-odbc
ruby-odbc

In order to install ruby-odbc on Windows, you will need to compile it from source. I used the excellent DevKit provided with the RubyInstaller and had no problems.

Connect

Let’s assume that the ODBC Data Source Name is “TestData”. First, we require the DBI library, then connect to the data source.

require 'DBI'

dbh = DBI.connect("DBI:ODBC:TestData")

You can also pass a username and password after the connection string if required.

List and Describe Tables

Now that we have a connection, let’s see what tables are available.

dbh.tables

My test database only has one table. It’s called “Table1”. Let’s list the columns in this table.

dbh.columns "Table1"

This will tell us the type of data stored in each field in addition to the name.

Query For Data

In order to see the actual data, we can prepare and execute an SQL query. In this case, the separate prepare and execute is not really necessary, but it’s a good habit to get into.

sth = dbh.prepare("SELECT * FROM Table1")
sth.execute

row = sth.fetch

The fetch method will return a row of data at a time until there is not more data. At that point it returns nil. Calling fetch again after it returns nil will result in an exception.

A Complete Example

Here’s a simple example of connecting to the ODBC database and displaying all of the data in a tab separated format.

require 'DBI'

dbh = DBI.connect("DBI:ODBC:TestData")

sth = dbh.prepare("SELECT * FROM Table1")
sth.execute

puts sth.column_names.join("\t")

while row = sth.fetch
  puts row.join("\t")
end

sth.finish
dbh.disconnect

You could also require the CSV class and use it to generate comma-separated output.