Navigation:  Port to SQL > Step THREE ( Transferring Data)

Data

 

One of the major concerns with a project like converting an application to SQL is how to manage the conversion and transfer of the data.  Don't worry, DMC has that covered.  DMC will not only Create the new SQL tables and copy the data from TPS to SQL, but also give you an enormous set of tools to manipulate the data in the process.  During the Port to SQL process with the "Data" option selected, DMC creates the new SQL scripts and basic data transfer Profiles into one big Project.  This starts as a simple one to one transfer.  Here's a simple example:

 

In this simple example, a new table is created with the name SQL_Customer so it won't be confused with the TPS Customer table.  A new GUID is created so SQL will have a unique record identifier (SQL requirement).  The column Cus:State is given the prefix Sql_State so the column name won't be a reserved word.  Note that the TPS Cus:State is not changed.  If you run the Data Transfer Project (or individual Profile for this table), DMC will create the table in SQL, copy the data from TPS to SQL and create the new GUID for each record.  For many conversions that may be sufficient.  For others, you may take this opportunity to do some data clean-up or manipulation.

 

 

Data Manipulation

 

If you would like to make more changes during the data transfer then DMC is ready, willing and able to assist.  Here are a couple of quick example of what can be done.  I won't go through every step - you'll find much more information here.

 

Let's say your users had some trouble with their data entry and they have some data in the States column (2 char string) in lowercase, some uppercase and some mixed and you want them all to be in caps.  Just edit the Customer Profile, double click on the Sql_Cus:Sql_State column and add the Function UPPER with the source of State and save the changes.  Now, all your states will be converted to all capital letters during the transfer.

 

What if we only want to transfer customers from a single state - Florida?  No problem, create a query for Cus:State equal to FL (2 character string).

 

Now for my favorite.  Let's say you want to create a new customer list that contains some merged new information.  The current Customer file has a 2 character state and the customer wants the full state name instead of the abbreviation, and they want to include some new information as well.  In your Clarion app you can a new field for RegionCode and a new table that contains the StateAbbreviation, StateName and RegionCode fields.  Re-Port the Dictionary using DMC.  In the Customer table profile you can add a Lookup of the old Cus:State in the new table to insert both the full StateName and RegionCodes for that state into the new SQL_Customer table.  No hand coding required.

 

There's no need to transfer your data then create a program to manipulate the data.  DMC can handle complex layers of dozens of Conditions, Functions and Replacements wizards as well as the option to hand build even more complex conditions.

 

To explore all the possibilities, check out the options in Projects & Profiles and the options for Query, Conditions, Functions, Replace and Concatenate.

 

The best part is that DMC Enterprise users can Export these Projects & Profiles to use with the DMC Runtime Engine which they can deploy to their users' computers to make those same changes directly on their systems without the need for their users to buy their own license of DMC.  Be sure to check out the Runtime Engine!

 

Show me the next step.