FlashFiler (NexusDB) to Oracle Migration

The project I'm currently working on has required me to migrate data contained within Flash Filer2 database files (*.FF2). Although this is an old and obscure file format, others out there may well need to migrate data stored in such files to a newer, shiner DB, like Oracle.

We were supplied with a CD that contained a catalogue of data but unfortunately it was in this obscure FF2 file format. There was a front end to the data on the CD but this was pretty much useless as we needed to exploit the data in the application we were developing. After identifying the file format, the question was how to actually get at the data and make use of it in the new application. The client owned the data so copyright etc wasn't an issue, but it may be for you.

Like most databases, the flash filer database files take the form of binary file. My novel approach was to build a database instance round the files and accessing the data through a DB driver or through ODBC.

This meant there were 2 problems.

• Getting hold of and installing the Database engine cheaply
• Finding an ODBC driver for the database engine

I sniffed failure when I discovered the company who had produced the database engine had ceased trading. After much googling, I found that the Flash Filer DB engine had been kindly placed on source forge at http://sourceforge.net/projects/tpflashfiler and some instructions at http://www.atari-source.com/docs/turbopower/FlashFilerBasics.pdf on how to get the DB to point to the flat files so they could be accessed. If you don't read the instructions, the answer lies in creating an Alias to the directory containing the FF2 files.

Unfortunately the ffexplorer.exe supplied with the flashfiler download does not include an export function but does allow you to nicely see the the table structure. This is useful when you come to extract your data.

Again after some more goggling, I eventually found an ODBC driver at http://www.nexusdb.com/downloads/FlashFilerODBC213_r001.ZIP , which be used for free. Although unsupported, the driver sufficed and did the job nicely (tho not very quickly).

Once installed you need to set up a data source which points to your flash filer database. On NT systems go to the windows control panel (in classic view) then Administrative tools- Sources(ODBC). Click on add and select 'Nexus DB FlashFiler Driver'. Enter your connection details and give you data source a name.

The next problem I encountered was how to actually connect to the database over ODBC and grab the data. Without wanting to installing or write masses of Java, C# or Perl. I found the simplest way was to create some JScript that connected to the database and dumped out flat tab-separated text files. JScript is a Microsoft scripting language that has javascript like syntax. I have uploaded a generic version of the file which is located here and can be adapted by yourself. To run, simply type the fully qualified path and filename in a command window.

To get the data into Oracle you could use sqlldr or externalised tables, both use similar syntax. I used externalised tables and created a new table by doing create table as (select * from external_table).

When a you use an externalised table, the data is actually read from the text file and and cannot take advantage of the performance features Oracle uses, hence my reason for creating a table in this way. Granted, I could have bypassed this and used sqlldr or some other scripting language. The only thing to note is that you need to create a data directory mapping in the Database which will map to the location of your csv/tsv file file.

The files I have supplied may need to be adapted for your own use though. The JScript file was my quick and dirty solution to get a job done quickly and has no error checking. If it fails you'll get a javascript type alert saying so. All code supplied comes without warranty or guarantee.

Hope this saves someone a cool couple of hours or can find a use for this.