Exploring ODBC with Ruby DBI

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.

To Hex and Back (With Ruby)

I am a big fan of plain text. It is easy to view and easy to edit. Unfortunately, it is sometimes necessary to work with binary files and data.

Ruby’s inspect method does a decent job of showing the contents of a binary string, but sometimes I need something a little more powerful.

Back in the day I would use a hex editor to open binary files and decipher their contents. I don’t have a need for a hex editor anymore, but I would like to occasionally view binary data in the same format.

After some intense Googling and Ruby doc reading, I came up with a few methods to convert a binary string to hex, and convert a string of hex back to the original binary.

Bin to Hex

To convert a string to it hex representation, first take each byte, convert it to hex, then join all of the hex digits back together.

def bin_to_hex(s)
  s.each_byte.map { |b| b.to_s(16) }.join
end

If you like spaces between the hex digits, change join to join(‘ ‘)

Hex to Bin

Converting the string of hex digits back to binary is just as easy. Take the hex digits two at a time (since each byte can range from 00 to FF), convert the digits to a character, and join them back together.

def hex_to_bin(s)
 s.scan(/../).map { |x| x.hex.chr }.join
end

If you find yourself using these frequently in a project, you could add the methods to the String class.

TIMTOWTDI

Of course, there is more than one way to do this. Ruby also provides the handy pack and unpack methods for Arrays and Strings respectively. These are a little more cryptic since you need to know the meaning of the format string to understand what’s going on.

def bin_to_hex(s)
  s.unpack('H*').first
end

def hex_to_bin(s)
  s.scan(/../).map { |x| x.hex }.pack('c*')
end

Check the Ruby documentation for Array and String for a complete explanation of pack and unpack.

Examples

Here’s the output of a quick IRB session to demonstrate how this works.

irb(main):001:0> s = "Hello, World!"
=> "Hello, World!"

irb(main):002:0> s = s.each_byte.map { |b| b.to\_s(16) }.join
=> "48656c6c6f2c20576f726c6421"

irb(main):003:0> s = s.scan(/../).map { |x| x.hex.chr }.join
=> "Hello, World!"

These methods are no replacement for a hex editor, but if you need to check an encryption key or some other short string of binary, they can be just the thing.

Ruby Generators

In my continuing quest to expand my knowledge of all things Computer Science, I stumbled across the Generator topic on Wikipedia. I ended up here after reading an excerpt from Python Cookbook.

The Wikipedia article describes a generator as something that looks like a function but behaves like an iterator. The interesting thing to me is that they can be used to build endless lists.

For example, the Python Cookbook gives several generators that will list all prime numbers (given enough time). The Wikipedia article gives another way of generating prime numbers.

I was surprised that Ruby wasn’t mentioned in the Wikipedia article so I set off on a search for information about Ruby generators. Unfortunately, most of what I found was dealing with code generators for Rails which is something else entirely.

The Ruby Documentation contains a description of the Generator class with a brief example. I was able to use this to convert Wikipedia’s simple prime number generator to Ruby.

require 'generator'

g = Generator.new do |g|
  n = 2
  p = []

  while true
    if p.all? { |f| n % f != 0 }
      g.yield n
      p << n
    end
    n += 1
  end
end

Using this code, each time you call g.next it will return the next prime number starting with 2. For example, this will print the first 25 prime numbers:

for i in 1..25
  puts g.next
end

If you examine the source code in both Python and Ruby you’ll find that they are remarkably similar. The only change is basically replacing Python’s any function with a call to Ruby’s Enumerable#all?.

The more I learn about Python and Ruby, the more similar I realize they are. I have yet to find anything in Python that can’t be done just as easily in Ruby or vice-versa.