How to read & write to a dbf database file from ruby.

A little history. The company I work for has an old system that was written in house for a period of almost 20 years and it’s done in dBase. Once cool and allmighty, it’s not longer, dare we say, a system that is at the forefront of computer technology. It has a numerous problems, i.e. multiple users locking tables, AD based security for each individual file/table, slow, etc. Anyways long story short, we decided to convert our system to a web based one, with a Debian/Apache/Passenger/MySQL/Ruby backend – and whatever at the frontend.

Obviosly we had a problem of converting old data to mysql format, with ruby only supporting reading of dbf file via ruby dbf gem – which is great if you only need to read files. Unfortunatly – we couldn’t just abandon our system – since too much depended on it – we need to have a slow transition – where some stuff being phased out as it’s being replaced by web based app. :. , we needed to be able to write back to dbf files. Let me save you some time – I search long and hard, and there is no way to do it. Or so I and most of the commenters on the Internet thought at the time of this writing (Jan, 09). Until I had an epiphany – ODBC!!! I was actually reading an article about PHP, and thought about one of my first experinces – when I had to pull data from MS Access (back in ‘99) into MySQL using ODBC.

So below is the solution and explanation of the process. Let me tell you right now – I don’t know if this will work on Linux. My dev box is WinXP and apps that will be deployed will be deployed on Win2k3 for vairous other reasons. Please leave a comment if you got it working on Linux.

Setup a DSN for your connection.

1. Go to Control Panel > Asministrative Tools > ODBC
2. Select System DSN  tab and add a new connection
3. Select Driver do Microsfot dBase (*.dbf) – if you are missing that – you might need to install db2k running time, that you should have if you working with dBase files.
4. Select Version 5
5. Give it a name – i.e. rubydbf – somethign unique and descriptive – this analogous to your db name in your MySQL connections
6. Select the directory where your dbf files sit – in my case – C:Projectsdbf
7. Select appropriate indexes for your dbf files – either *.ndx or *.mdx files.

Fire up shell – we are going to test our connection.

1. We need to make sure we have all the gems:
   

   
gem install dbi
gem install dbd-odbc


2. Let’s test if it worked:
   

   
irb
require 'dbi'
require 'odbc'
conn = DBI.connect('DBI:ODBC:rubydbf','','')


   If you didn’t get any error messages – that means that you are fine. You can try to run

   conn.tables

   for a list of tables available to you through this connection.

3. Let’s try to run a couple of select statements. Let me warn you right away – and I think it’s a dBase driver issue ( there are a couple of other driver’s I haven’t tried, that might be better) – some of the standard DBI ODBC commands just don’t work. I’ll give the list of the ones I found working and most useful at the end of the post.
   

   rs = conn.select_all('select * from product')

   That throws back all the data sitting in the product table, rs being your reqular ruby array. So you can do all the nice things such as rs.size or rs[3], etc. Obviously you are limited by your RAM and pc – but most modern pc’s should handle your older dbf files. The reason I’m saying this is that I found most sql statement unexecutable – for example select * from product limit 2 – won’t work. Of course you can use things like select_many() – but I think it’s just easier to do all of your magic in ruby, simply cause ruby is going to be faster and easier in this case, thou I don’t not advocate that in general – DBs are good at manipulating data – leave it to them.

   Also another way to do it is to use DBI’s prepare and execute statements:

   q = conn.prepare('select * from product where partid = 14')
q.execute()
rs = q.fetch_all()

   This approach lets you use band variables – which might be useful – if you are not just converting data, but need to pull dynamic result sets – i.e. dated reports or something.

4. Now that we can read data, let try to insert data – which is the main point of this exercise:
   

   q = conn.prepare("insert into product (description) values('cool widget')")
q.execute()

   If you run a select now you should see a new record in your table. As – I said unfortunatly you can’t do limits and so on, so you can dump all you data in an array rs = conn.select_all('select * from product') and then do rs.last to see your new and shiny row

That is it. Should be enough to get you started, let me know if you run into problems – someone else mgiht think of something or sometimes – I have an epifany – just like this one time
Have fun! or what amounts to it in dBase world

Technorati Tags: ruby, dbf, odbc, dBase