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.
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
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.
My test database only has one table. It’s called “Table1”. Let’s list the columns in this table.
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.