Sw4   >   Sql   >   Converting Data

Converting Data

Numerous attempts have been made by myself and other StudioWorks developers to create a generic data converter. The reality that we have all discovered is that it is virtually impossible to write a generic data converter. There are an infinite number of possible structures for the source data and it seems none of it is ready to be ported straight into the new and improved SQL database which you have designed for your StudioWorks app.

There are however some techniques which you can use to simplify the process of converting your old data and moving it into your new StudioWorks friendly database. This sections covers various tips and tricks to help you with the conversion process.

For the majority of applications I have written, I have spent more time on the data conversion than writing the app.

Data Converter Demo

StartNewApp

includes a myDataConverterDemo library which is located inside the APP/libraries/db2sess/ folder.

You can open the library in StartNewApp by selecting My App menu > Programmer My App menu > Data Converter...

All going well this will open the myDataConverterDemo library and then open the wDataConverter window located inside the library.

The myDataConverterDemo library contains several objects which you can use for writing your own data converter.

  1. oDataConverter - This object class contains demo code that you can study, copy and modify for your own data converter code. The $importOldData method is called by the Import Old Data button on the wDataConverter window.
  2. oSQLLists_db2sess - The $retDefinedList method of this object is called by the oDataConverter object to return SQL defined lists based on schema or query classes located inside the myDataConverterDemo library.
  3. tBase_db2sess - This table class is used by the $retDefinedList method of oSQLLists_db2sess. The table class has $getAllRecords, $getWhere, $selectWhere methods which can be used to select and fetch records from the old database.
  4. oDataConverterTools - This object class has some handy methods which are called by the oDataConverter object. You can call these methods from your own data converter code in the oDataConverter object.

Data Converter Instructions

If you have an Omnis classic application and data file that you wish to convert to Omnis Studio and StudioWorks the following steps can be followed for setting up your StudioWorks app for converting the old Omnis data file.

  1. Create a data_OldClassicApp folder inside your APP folder. If the folder already exists, delete any contents inside the folder.
  2. Drag your old app's data file into the folder.
  3. Create a db2sess folder inside your APP/libraries/ folder if it doesn't already exist.
  4. If it doesn't already exist, drag the myDataConverterDemo.lbs library from StartNewApp into the db2sess folder.
  5. Rename myDataConverterDemo.lbs to an appropriate name for your APP. e.g. ClassicDataConverter.lbs
  6. From your StudioWorks app's menu, open the Data Converter window. Main Menu > Programmer AppName > Data Converter...

    All going well your data converter library will be opened and the wDataConverter window inside the library will be opened.
  7. Click the Open db2sess Session button in the window.
  8. F2 Browser > SQL Browser > OLDDATAFILE session > click the Tables node.
  9. There should be a list of all the tables in your old Omnis data file.
  10. Select all of the old tables and drag them into the Schema Classes folder of your Data Converter library.
  11. This creates one schema class for each table in the old data file.
  12. You are now read to start writing code in the oDataConverter object of your Data Converter library.
  13. You will want to get all the records from the old data file into a list, then fix and parse the data as needed to get it ready to insert/update tables in the new data base.

    Often times the table structure in the new data base will be structured differently than the data in the old data file so you will need to do import the old data into a list, merge the columns you want into separate lists and insert records starting with the parent table, then insert and link child records to the appropriate parent records.

Getting you new database structure right and converting your old data can take as much or more time that writing the application. Time spent getting the new database designed right is time well spent.

If you are unfamiliar with SQL and database design you may want to consider hiring myself or another StudioWorks developer who has experience to assist you. A week or two of consulting and assistance could easily save you months of work trying to do the data conversion.

Data Conversion Advice

I've done a few data conversions from various sources. The following are some general recommendations:

  1. If you are not familiar with SQL and designing RDBMS databases, take the time to read Database Design for Mere Mortals by Michael J. Hernandez.
  2. Design your new database. If your application's database is large and complicated, start with desiging a few simple lookup tables. e.g. Country, Stateprov, Towncity.
  3. Create the schema classes for a few of the simple tables.
  4. Add the SQL-Meta Data. For lookup tables set the class meta-data fetchall checkbox to checked.
  5. Create the tables in your new database.
  6. Write the data import routine for the first table, following the code in the oDataConverter object class.
  7. Step through the code importing the data from old data base and inserting it into the new database.
  8. Add the list view window for the table to your navigation list
  9. Layer by layer add the data for additonal tables

StudioWorks Documentation - Copyright 2005 Vencor Software