Read / write to DBF files from Ruby

A little history.

I worked on a project, where a system was written in house for a period of almost 20 years and it’s done in dBase. Once cool and almighty, it’s not longer, dare we say, a system that is at the forefront of computer technology. It has numerous problems: multiple users locking tables, AD based security for each individual file/table, slow, etc.

Long story short, we decided to convert our system to a web based one, with a Debian / Nginx / Passenger / MariaDB / Ruby back-end – and whatever at the front-end. Obviously 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.

Unfortunately, we couldn’t just abandon our system, since too much depended on it – we needed to have a slow transition – where some sections are 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 comments 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 experiences – 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 haven’t tried this will work on Linux. This was deployed on Win2k3 (and tested on WinXP). Please leave a comment if you got it working on Linux.

Setup a DSN for your connection.

  1. Go to Control Panel > Administrative Tools > ODBC
  2. Select System DSN tab and add a new connection
  3. Select Driver do Microsoft 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 – something 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:\Projects\dbf
  7. Select appropriate indexes for your dbf files – either *.ndx or *.mdx files.

Lets try to access our data

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

We need to make sure we have all the gems:

  gem install dbi gem install dbd-odbc

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 for a list of tables available to you through this connection.

conn.tables

Read data

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

This spits 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 because 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.

Another way to query your data 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.

Insert Records

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

Have fun! or what amounts to it in dBase world 🙂