Upgrading to Client/Server - using Oracle Accelerator

by John Sarver

Published 1998-10-01    Printer-friendly version

We first deployed our Clarion Professional Developer 2.1 with LPM application in early 1992. In those days with only one location and about 200 employees, we were able to manage our order entry and production systems with simple Clarion files. As we grew, we converted our applications to run under Clarion for Windows. These DOS files were still fine, even with two locations and several thousand records. However, as we continued to grow, we made our factories bigger, added manufacturing locations, and saw our data needs grow. With some files having over 1 million records, we were greatly concerned about the occasional need to re-key. Adding a field would take 18-20 hours - if we didn't run out of disk space. We knew there had to be a better way.

In early 1996, Oracle Accelerator (then Oracle Connect) was a new product for TopSpeed. Oracle had lots of promise to meet our needs for easier file maintenance, better performance and greater reliability. We therefore started the steep learning curve towards converting our major DOS files to run under Oracle. We committed one of our developers to be our Database Administrator (DBA); we purchased Oracle and sent him to school - several schools. We hired consulting help to help us understand TopSpeed's Oracle Connect and just how to use it. We fumbled and stumbled until we figured it out. Within about 7 months we deployed our application. Today, we would be struggling to survive without it.

This presentation relates some of the things we have learned in the process of converting and enhancing our software to run on Oracle using TopSpeed's Oracle Accelerator.

While Client/Server certainly isn't for everyone, if you're in an environment with over 20 users hitting the same files, or have a large number of records, or have mission critical data that you cannot afford to risk, then Oracle may be just what you need. Oracle is a very powerful database. It allows you to unload much of your data processing to a dedicated, powerful server. It provides powerful maintenance and query tools. It greatly reduces network traffic using VIEWs and FILTERs. Its reliability and performance in a large multi-user environment is exceptional. It makes your life easier.

However, this power comes at a cost. Oracle is not an inexpensive product, nor is it something you deploy overnight. You will probably need a dedicated Database Administrator to set up your database and maintain it. It takes time to really understand what Oracle Accelerator does for you, but we feel it is a small investment for the return we see.

Oracle Accelerator converts standard Clarion for Windows code into SQL. While it helps to learn some basic SQL, in theory, you can do almost all you need without learning it.

You can convert the existing files in your Clarion dictionary to Oracle. Besides changing the driver, you'll need to change some of the data types. Most of this is outline in the Oracle Accelerator documentation, but some examples are:

DECIMAL needs converted to PDECIMAL

Dates using LONG need to be converted to DATE

You can store DATEs and TIMEs together using a GROUP

The dictionary will identify the invalid types for you, and prompt you to change them once you change the driver type to Oracle. Also, be sure to set the flag to enable field binding, so that CW will set the external names for you.

As an alternative to changing an existing dictionary, you can create the table in Oracle, then import the data structure into your dictionary using the Clarion Import Wizard. In this case, the external names are automatically set for you.

VIEWs and FILTERs allow you to restrict the data you retrieve from a database. Using Clarion or TopSpeed files, you always get all of the fields in a record. Under Oracle, you only get the columns defined in the VIEW. Filtering is also done by the server, so you only get the rows (records) that you desire. However, you need to be aware that even though you may only retrieve some fields in a record using a VIEW, all of the fields are still available to your Clarion program. But -- the unretrieved fields will have invalid data in them, so be careful that you only reference those fields that you are SURE are loaded.

Oracle maintains all of your keys for you -- Oracle calls them indexes. You will still set up keys in your dictionary, but all they do is determine the order for reports, browses, etc. They have no real reference to an external file or set of data. You can, however, set up corresponding indexes in Oracle to match your keys, and this will greatly improve performance. If an index doesn't exist, though, Oracle will do the sorting on the fly. Beware -- this can take a long time for large tables.

If you take the time to learn SQL (and I recommend that you do), you will find a wealth of power in it. Couple it with PROP:SQL, and you can pull all sorts of summarized, totaled, linked (or whatever) data you want from the server. PROP:SQL is a very powerful addition to the Clarion environment.

As you learn SQL and work with Oracle Connect, make liberal use of the PROP:LogFile property syntax. It creates a log file of all the SQL statements generated by the Oracle driver. You can examine it and learn a lot about how the driver works, as well as increase your SQL knowledge.

Remember, if Oracle is right for you it can be an investment with a tremendous payback, but it takes time, energy and patience. Take the steps slowly, be sure you understand where you're going, and you'll find yourself some day wondering how you ever managed your database without it.

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