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
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
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
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