How To Convert Your Database To SQL

by Scott Ferrett

Published 1999-05-03    Printer-friendly version

This document is based on the talk given by Scott Ferrett at Euro Devcon '99 in Amsterdam on 22 April 99. It is based on the facilities available in Clarion 5a Enterprise Edition. Later versions of Clarion may require different (hopefully less) work. However, the information on file structure changes will probably still apply.

Reprinted in Clarion Magazine with permission.

Throughout this article I will refer to TPS files. However, this article applies equally to any ISAM file format (Clarion, dBase, or Btrieve). I will refer to SQLAnywhere as the SQL driver. However, this can be replaced with Oracle, MSSQL, Scalable, AS400, ODBC or any other SQL file driver.

There are two things to do when creating an SQL database based on an existing set of TPS files: convert the data definitions and convert the data.

Converting the Data Definitions (Creating a new DCT)

The first thing to do is create a new DCT.

You then need to move the existing TPS tables into the new DCT and convert them to SQL tables. This can be done in two ways: Copy the old DCT to the new DCT then change each table one at a time; or Export the old DCT to TXD, edit the TXD and import the TXD into the new DCT.

I will use the second technique as it allows for bulk changes. However, this system is much more dangerous as you have no tools to assist you in making certain that everything you do is consistent and correct.

Driver Name

This is the most obvious change. You need to change 'TopSpeed' to 'SQLAnywhere'. Do not do this yet.

OWNER

All SQL tables require an OWNER attribute that indicates how to connect to the database where the tables live. THIS SHOULD ALWAYS BE A VARIABLE.

If you have any tables that already have an OWNER attribute, remove these.

Once you have no tables with OWNER attributes you can replace "'TopSpeed') " with "'SQLAnywhere'),OWNER(GLO:Owner)"

Key Component STRING => CSTRING

In most SQL databases trailing spaces are important when testing if a field is equal. So 'Smith' ~= 'Smith '. This is normally only important on key components as they are used in relational links and filters. If you use CSTRINGs instead of STRINGs, then Clarion treats trailing spaces in the same manner as the SQL system.

MEMO => STRING

SQL drivers do not support MEMO fields. These can be represented as very large strings. As you will be developing 32bit applications you do not have a 64K record limit to worry about.

LONG => DATE and TIME (sometimes)

In older systems date and time data is stored as a LONG. These should be converted to DATE and TIME fields so your data will be easily accessible from non-Clarion programs such as third party report writers.

No RECLAIM attribute

SQL drivers do not support this attribute.

Remove NOCASE

Every SQL system (except P.SQL) only supports the concept of case sensitive or case insensitive keys across the entire database. You cannot specify one key as case sensitive and another one as case insensitive.

Because of this global setting, SQL systems do not require the NOCASE attribute. In fact, specifying it can SIGNIFICANTLY impact on performance.

Change Referential Integrity to Server based

This is something you get for free. You do not have to learn any SQL to get the advantage of having the server do your referential integrity. To do this you need to change all your relational constraints to the equivalent server based relational constraints. If you are editing the TXD you do this by changing;

CASCADE to CASCADE_SERVER;

RESTRICT to RESTRICT_SERVER;

and CLEAR to CLEAR_SERVER

You have now done all the quick TXD based changes. You can do the following changes in the TXD or after importing the TXD. I recommend the later.

Make Sure All Files Have A Unique Key

There is no hidden record number in SQL. So for the driver to be able to update a record you must tell the driver how to uniquely identify a record. To do this you must have at least one unique key defined for a file. Even one record control files. The driver does not know that there is only one record.

Do Not Use GROUP IDs In Keys

It is fine to use fields that are within a group as components of a key. But you cannot use the GROUP field in a key. If you have keys that use a GROUP field, you will need to change the key to use each field within the key.

Converting The Data Definitions (Create an SQL Script)

To create an SQL Script you will need two dictionaries. One is the one you created in the earlier steps. The other is an SQL dictionary. You get an SQL dictionary by either creating a new SQL database or using an existing one.

Run The Dictionary Synchronizer To Create An SQL Script

Run the synchroniser. Select your other dictionary to be the SQL database you want to create the SQL tables in.

Set the Source DCT as the Clarion DCT you created in the previous section and the destination DCT as your SQL database.

Once you get to the synchroniser screen you need to copy all the files to your SQL database. The easiest way to do this is to highlight the top line. Press the right mouse button and select add.

Run the script

To run the script you need to run your SQL's SQL executor. Load the script and run it.

Converting The Data

Create the Conversion Program

To create a conversion program you select Create Conversion Program from the File menu whilst you are in the dictionary editor.

You then end up back in the synchroniser. Don't panic. This is where you are meant to be. The synchroniser has many faces. One of these is to allow conversion programs to be created.

Choosing your dictionaries

To create a conversion program you need two DCTs. The original DCT that contains all the TPS files and the new DCT that contains the SQL table definitions.

The most confusing part of creating a conversion program is the screen that asks you which DCT is the source and which is the destination.

The Source DCT is the one with your SQL tables in it

Your Destination DCT is the one with the TPS tables in it

Once you get to the synchroniser screen you need to copy all the files. The easiest way to do this is to highlight the top line. Press the right mouse button and select copy.

Press OK.

You now get the next confusing part of the conversion program creator. You find yourself back in the dictionary editor.

The system has actually done what you wanted. It just didn't tell you.

Edit the Conversion Program

A project convert.prj was created in the previous step.

Load this as the current project.

The first thing to do is change the properties of the project to create a 32bit program.

You will then need to edit the conversion program. The program is an OO program designed to convert an existing set of ISAM tables to a new version of those tables. As such, it does not handle converting ISAM to SQL without a few modifications. Being an OO program it allows us to make these modifications without having to hack the base code.

The file that needs to be edited is C5CVT__1.CLW.

Standard SQL Code Additions To Conversion Program

There are a few standard settings that need to be overwritten for all SQL tables. So the easiest way to do this is to create an SQLDestTable class derived from DestTable. You then change all your DestTable derived classes to be SQLDestTable derived classes.

SQLDestTable   CLASS(DestTable)
CreateTable   PROCEDURE (),RCCODE,DERIVED
AskName       PROCEDURE (BOOL _MustExist, <string FileLabel>)
                    ,BYTE,PROC,DERIVED
BuildKeys     PROCEDURE (),RCCODE,DERIVED
        END
SQLDestTable.CreateTable PROCEDURE()
  CODE
  RETURN RC:Ok

SQLDestTable.AskName  PROCEDURE (BOOL _MustExist, *lt;string FileLabel>)
  CODE
  SELF.FileName = SELF.Label
  RETURN RC:OK

SQLDestTable.BuildKeys PROCEDURE ()
  CODE
  RETURN RC:Ok

Setting the OWNER

The conversion program creates an owner variable for every table. This is a pain. You need to change every OWNER() attribute to refer to just one string. You can then either add code to get a user ID and password from the user, or hard code it.

Set Tasks

The conversion program generator does not set the right tasks to be performed when doing this conversion. So you need to edit the "SELF.Task = " line to be:

SELF.Task = TASK:DefaultSQL + TASK:OpenSrc - |
  TASK:Backup + TASK:UpdateDest

There is one of these lines for each file being converted.

Convert Does Not Handle MEMO=>STRING

There is a bug in the conversion generator where it does not generate the necessary code to convert memos to strings. So in each table that had a memo you will need to edit the Assign procedure and add the line

NewFile.StrField = OldFile.MemoField

Modifying Your Applications

The only thing you will need to do is add a logon screen to the start of your program. You will need to ask the user for a User ID and Password.

You can hide this from the user by having a hard coded User ID and Password. Even in this case you will want to add a dialog indicating the program is connecting to the server as this can take some time (up to 20 seconds is not unusual).

The connection to the server is done when you first open a file. So you need to either open a file in your Logon screen, or on the frame. You can close the file immediately. The connection will be kept open until the application terminates or PROP:Disconnect is called.

Doing This For Client Data

This article has been written from the viewpoint of the developer having access to the database. If you want to convert an existing customer's data to an SQL system you will need to:

• Get the SQL system installed.
• Either copy over an empty database that already contains the table definitions, or create a new DCT and run the creation script.
• Run the conversion program

Printer-friendly version

Copyright © 1999-2008 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942