I recently received some data in the Advantage Database Server file format (.adt).  In the past I have worked with DBF, Access, and .xls.  I was able to find some way to retrieve data from these formats using open source software.  The open source solutions were much easier to work with, usually not requiring any sort of driver installation, etc.

I searched thoroughly for an easy way to retrieve information from this set of .ADT files I was dealing with.  Sybase provides tools to work with the data, but all of them require using their software or drivers developed by them.  I would have settled for this if it wasn’t such a hassle to figure out how to use them.  I asked the question on Stackoverflow.com and got an answer from one of the employee’s of Sybase.  While helpful, the suggested Perl driver was one of the things I was avoiding.  I did attempt it and after a little hassle, moved on to creating a simpler solution.

One solution was to simply use Sybase’s provided “Advantage Data Architect” software to open the .ADT files and export them all to SQL.  This is possible with their software, but it would not work for my situation.  The developers of the program outputting these .adt files used a separate embedded database for each new report that was being worked on.  This meant there was hundreds of folders, each containing 30-40 .ADT files making up their own embedded database.  I needed something that could automate the extraction of this data.

Data Format

To cut this short, I decided to open up the .ADT files with a hex editor and see if I could get the information I needed out.  I only needed the column names, their types, and the actual rows of data.  To aid anybody else doing something similar to this, I’ll include some of my notes about the format here:

• The data is stored at the very end of the file.
• The byte offset when the data starts is located at byte 32 and is 32-bits
• Byte 24 contains a 32-bit integer specifying the number of rows
• Byte 36 contains the a 32-bit number representing the number of bytes each record consumes
• The header is 400 bytes
• Column information is after the header, before the data.  Each column entry is 200 bytes.
• The number of columns can be calculated (data_offset-400)/200
• Column names consume the first 128 of each column entry.
• Byte 130 contains a 16-bit integer representing the type of data.
• Byte 136 contains a 16-bit integer, the length of the field in bytes
• Character = 04, Double = 0A, Integer = 0B, Shortint = 0C, CICharacter = 14, Date = 03, Time = 0D, Timestamp = 0E, Autoinc = 0F

This was enough information to get what I wanted.

Ruby-ADT Gem

I took the existing DBF gem by Keith Morrison.  Most of the credit for this gem goes to him as I borrowed the structure heavily from his gem.

I’ll provide an example of what is now possible with my new ADT gem.  No drivers are required and it’s as simple as installing the gem.  I’ll include a snippet of the readme from my Ruby-ADT gem on github, borrowed heavily again from the DBF gem.

Installation

gem install ruby-adt

Basic Usage

Load an ADT file:

require 'rubygems'require 'adt'

table = ADT::Table.new("test.adt")

Enumerate all records

table.each do |record|  puts record.name  puts record.emailend

Load a single record using record or find

table.record(6)table.find(6)

Attributes can also be accessed through the attributes hash in original or underscored form or as an accessor method using the underscored name.

table.record(4).attributes["PhoneBook"]table.record(4).attributes["phone_book"]table.record(4).phone_book

Search for records using a simple hash format. Multiple search criteria are ANDed. Use the block form of find if the resulting recordset could be large otherwise all records will be loaded into memory.

# find all records with first_name equal to Keithtable.find(:all, :first_name => 'Keith') do |record|  puts record.last_nameend

# find all records with first_name equal to Keith and last_name equal# to Morrisontable.find(:all, :first_name => 'Keith', :last_name => 'Morrison') do |record|  puts record.last_nameend

# find the first record with first_name equal to Keithtable.find :first, :first_name => 'Keith'

# find record number 10

table.find(10)

Migrating to ActiveRecord

An example of migrating a DBF book table to ActiveRecord using a migration:

require 'adt'

class CreateBooks < ActiveRecord::Migration  def self.up    table = ADT::Table.new('db/adt/books.adt')    eval(table.schema)

    table.each do |record|      Book.create(record.attributes)    end  end

  def self.down    drop_table :books  endend

Limitations and known bugs

• ADT is read-only
• External index files are not used
• Dates are not currently handled correctly.

Future Directions

I am going to continue working on this library.  I have already heard from a few others that found this useful immediately after I posted it online.  I was surprised people could find it so quickly.  I’m sure there are bugs.  I would appreciate any problems to be reported to the Ruby-ADT Github page.  Emails are also welcome, but any issues should be directed to the github page and I’ll get to them when I can.

Hope this helps somebody out there dealing with this format.

- Chase Gray