The WarpDoctor Clinic

Review of Approach for OS/2 Warp 4, Revision 1.5

By: Walter Metcalf
Date: 04/14/00

I have been fascinated by databases since my first computer, which was a Commodore 64. Dissatisfied with the accounting program I was using, I tried to write my own using a simple database program available for the C-64. I did quite well too--until I exceeded the 4K memory available for user programs! (Note: that's 4 kilobytes.) Since then I have been "on the hunt" for the perfect database, and have tried many in the process.

The latest database I have checked out is Approach, part of the Lotus SmartSuite for OS/2 Warp 4 package. Before I get into specifics of my examination, I'd like to briefly survey some of the more notable features that aren't covered later.

Approach, as well as the other members of SmartSuite, features a Team Approach which allows different people in the same office, or (using the Internet feature), possibly around the world, to create and work on a database, simultaneously or at different times.
Using its unique Internet system, Approach can access tables and databases on the internet as if they were on your local drive. This allows your Approach session to reach literally around the world.
Approach allows you to create database tables either by selecting one of several pre-existing templates, called SmartMasters, or by starting from scratch and building your own. I personally have found many of the SmartMasters too complex for me to understand well enough to modify them confidently, so I have always built my own. But for a one-time job where time is at a premium, using a SmartMaster could be a real time-saver. (For example see the article entitled SmartSuite SmartMasters.
Approach comes with a very versatile and generally easy-to-use interface. As soon as it starts, Approach presents you with a dialogue giving you 3 options: select a SmartMaster, select an existing database with a Browse button, or select "blank database," to create a new database; the last one is the default. If you choose to select the default, Approach first presents you with another dialogue where you define which fields you want, their lengths, and their types. More complex options can be added now or later. After requesting a path and filename for the new database, Approach creates a simple form and table, or worksheet, using the fields you defined earlier. Now, using the menus, you can enter your data, create another table, create forms, reports, etc. Once a database is initially created most of your manipulation of the database will be done via these worksheets. There is no stated limit to the number of worksheets you can have, and it's important to realize that each worksheet may display data from any combination of fields and tables. This makes the worksheets extremely powerful. Approach also allows you to create other forms as well as reports. These are done using a simple point and click method; drag and drop can be used to modify them once they have been created.
In the review of Approach in SmartSuite 1.0 I pointed out the following bugs:
1. Every time I try to create a query of more than 3 conditions using the Query by Box feature, Approach crashes. This seems to be fixed in version 1.5.
2. I found when I closed Approach, even with all databases closed, using the 'X' icon next to the title bar, it more often than not either crashed or hung. In either case, Approach remained "stuck" in the system so that it could not be closed or restarted without first rebooting the system. Using File|Exit Approach seems to be somewhat more successful. This has also largely been fixed. Even by trying to force the problem, I was only able to get Approach to crash or hang once.
3. Second, I have a bit of a problem with any program that occupies 11 MB of RAM with no data loaded. Lotus (IBM) needs to understand not every workstation, even in enterprises, has a high-end Pentium IIe or Pentium III! This is unchanged.
4. Finally, I may be picking at nits, but I wish Approach had the ability to save the main window size and position. Little things like this can add a lot to an application's ease of use. Also unchanged.
  In v1.5, Approach has picked up a new problem to replace the ones it lost.
  1. Under certain conditions, Approach causes the processor usage to go to 100% and remain there, even though Approach is apparently idle. These conditions include:
    1. Approach has a password-protected (encrypted) database in memory;
    2. Under some circumstances when Approach has a query displayed on the screen.
    This phenomenon has been observed on a very high-end Pentium class machine with 256 MB RAM!

During my hunt for the perfect database, I came up with the following list of requirements or standards I looked for:

Relationality
Easy-to-use interface
Powerful built-in programming, or scripting, language
Data Integrity
Referential integrity
Decent speed.
Flexible query engine, accessible through the UI as well by script.
Compatibility with a wide variety of data formats.
Complete, easy-to-understand documentation.
Tight security.

Understand that these are my standards. Clearly each manufacturer has its own set of standards and emphases, that explains why there is so much variety among the DBMS's available today. These standards are what I look for in a database before I fork out the cash. They will also give us a basis on which to examine Approach and compare it with some other database systems. Since no database has all of them, I look for a product with as many as possible. Let's look at Approach now and see how it fares against these standards.

1. Relationality

Essentially a database is relational if it can be viewed by the user as a set of tables that can manipulated according to a strict set of rules. Virtually all modern databases today are relational, and Approach is no different. A typical example of a simple relational database is a business that keeps a record of each sales invoice in a table indexed using an invoice number. Naturally, each invoice contains the customer's name, or customer no., as one of its elements, or fields. To make billing possible, the business must also keep a record of each customer, his or her address, phone number, and other information in another table. Since each customer may have multiple invoices, these tables are in what is called a one to many relationship. Using the join feature (by selecting Create|Join), you set this up by joining the customer number on the invoice to the customer number on the customer invoice using drag and drop. Close the Join dialogue and you're done! Thereafter when you open the invoice table, Approach automatically accesses the customer table, allowing you to select fields from either table in any query, form, or report you wish to build. The ability to access multiple tables and thereby avoid duplicating data is what makes relational databases so useful.

Approach is clearly relational, and so receives top marks in this category.

2. Easy-to-use Interface

Approach's GUI interface is superb. Much of it is drag and drop as we have already seen. Reports, views, tables, and forms can all be constructed and modified using drag and drop. On top of this the action bars and toolbars make frequently repeated procedures a snap. In addition, by clicking on the extreme left of the toolbar row, you can select a different toolbar set from the ones already provided, or you can make your own.

My only complaint is that sometimes the location of menu items is somewhat confusing: for example, too many functions are lumped under the Create menu. As a result, to delete a field, a join, etc., you have to select the Create menu. Those items should appear on the Edit menu.

One feature of Approach I especially like is its unobtrusive monitoring of each of your keystrokes and mouse operations. Once you get used to it, it's really quite helpful. In most dialogues, there is an OK or Close button that is initially greyed out. As soon as you type a valid expression this button turns black, and stays black as long as what you enter remains valid. As soon as an expression is erroneous the button turns grey again. This can be a big help in correcting typos and other errors.

Approach scores very high in this area.

3. Powerful, built-in programming or scripting languages

This requirement comes out of my programming background. I have written several applications using database systems, and no matter how good the UI is, you can't really harness the power of a modern database if you don't have the ability to program it. In addition, programming is almost essential for automating routine database operations.

I should first mention that Approach contains several levels of macros and script facilities with which you can create scripts and macros directly from the keyboard. One method involves a sophisticated keystroke recording techniques simple enough for even a beginner to Approach to use.

The big gun in Approach's programming arsenal is the proprietary language called LotusScript. The manuals, which come in Acrobat form on CD, describe LotusScript as a version of Basic that in addition offers standard structured programming facilities and a set of language extensions that enable object-oriented application development. Moreover, applications written in LotusScript will run with the other products in Smartsuite for OS/2 Warp 4, including WordPro and Freelance Graphics. If that isn't enough, LotusScript applications can also call functions written in Rexx as well as in C! Although I haven't used LotusScript yet, I am very much anticipating doing so.

I would rate Approach as excellent when it comes to programming ability.

4. Data Integrity

Data Integrity refers to the safety of your data once it's committed to the DBMS. Security and Referential Integrity (see below) are aspects of that, but Data Integrity looks at the subject more generally. Approach has a serious problem here that I did not discover until after the review of v1.0 because symptoms begin to appear only with the passage of time. Whether due to computer problems that are inevitable or due to internal Approach bugs I cannot say, but after a database has been used for a time, Approach will start generating "Internal errors" usually when attempting to add a record to a table. There is no way around and no explanation of this problem. Worse, there is no way to close either the database or Approach. The only way to shut down Approach is to have OS/2 force it to terminate, which is likely to cause further corruption of the database. Indeed once this process has begun, more and more of these errors appear until the entire database is virtually unusable.

Many databases are shipped with utilities designed to repair or isolate damaged records, but Approach has no such tools. Furthermore, only "enterprise" level customers have access to any support from Lotus, so SOHO users have little recourse but to rebuild the database!

Approach rates a poor when it comes to Data Integrity.

5. Referential Integrity

For a long while I used Paradox for DOS to create my applications. Paradox was and is a fine DBMS, and I became quite enamoured with it. However it has one fatal flaw. Its designers failed to give it referential integrity. As a result my clients and I wound up with corrupted data on more than one occasion, which cost me heavily in terms of embarrassment, time, and money. Having been burned so badly, I now will not consider a database product without this feature.

What is this strange-sounding piece of jargon I'm so insistent on, anyway? Simply put, a DBMS has referential integrity if it has the ability to ensure that changes to one table that affect other tables are transmitted to those other tables. Consider again the example of the business with customer and invoice tables. What would happen if the owner accidentally deleted a customer who still had outstanding invoices from the customer table? He'd have a number of what I call "orphaned" records: invoices for customers you had no record of. Paradox for DOS would and did allow that to happen under certain circumstances.

Approach does have referential integrity. Unfortunately I had to do considerable research of my own, including digging through some third party documentation of Approach to determine this. (In particular, see Approach 97 for Windows for Dummies, IDG:1997, Ray & Ray, p.106.) Thanks to Approach's poor documentation (which I'll go into more later), I was unable to determine this from any information provided by Lotus. Given the importance of this feature, I find it odd that Lotus doesn't advertise the fact that Approach possesses relational integrity. To the best of my knowledge the only other OS/2 database possessing this feature is DB2. None of the xBase style databases, including OnCmd, have it. ¹

When building a database, Approach enables referential integrity by default, but the feature can be modified or completely disabled, although this is not advisable under normal circumstances. The normal way to set or modify this feature is through the join function. After joining the tables, select the Options button. You'll see a set of four check boxes, with the two Insert boxes checked. Left alone referential integrity is enabled. Modifying these settings weakens referential integrity or disables it altogether. (I consider it almost "criminal" that Lotus doesn't do a better job in the online help of explaining the potentially dangerous consequences of changing these settings, especially considering that once set, they cannot be changed.) As I suggested, once the options are set and the Join dialogue completed, the options cannot be changed without first destroying the join relationships. This provides an added level of security.

Approach receives top marks in this category.

6. Decent Speed

I have a straight Pentium 200 with 64 MB ram plus fast wide SCSI hard drives. I ran a series of tests on a database consisting of a single table containing 10 integer fields and 20,000 records using three DBMS's including Approach. The table exists in the form of an ASCII delimited text file. Here are the results:

**DBMS Speed Comparisons**
Operation	Approach for OS/2	R:Base for OS/2 v4.5++	Paradox for DOS 4.5
Load & Convert ASCII Table	164 sec	22 sec	5 sec
Reverse Sort on Single Field	29 sec	4 sec	1.5 sec

You can see Approach's speed leaves a lot to be desired! As if that weren't bad enough, Approach locks out the WPS while it is processing! Since Approach for OS/2 is a port from Windows, there is some hope its speed will improve as it continues to develop. ²

Approach receives a rating of poor in this category.

7. Flexible query engine, accessible through the UI, as well by a script

Approach has the best query engine I have ever seen. For those of you a bit rusty on your database terminology, the query facility allows you to search the database for those records matching a set of what may be very complex conditions. Using our example above, a simple query could be, "which customers bought more than $10,000 worth of goods last year?" The query engine is what gives databases much of their power and usefulness, because it provides the ability to ask your database questions. Approach gives you no fewer than four ways to create queries from the user interface! These methods include Lotus' own Query by Box (a graphical method of actually laying out your query using the mouse), the standard SQL, and the more usual method of specifying one directly from a record template. When using LotusScript, query operations are embedded in the logic of the script using such statements as Select Case and Do While.

Approach receives top marks in this category.

8. Compatibility with a variety of data formats

Again, Approach shines here. Approach allows importing from 11 different file formats! In addition, like many modern databases, you can actually use files from Paradox, dBase, Foxpro, and several other applications without converting or importing them. While Approach defaults to opening in dBase IV format, you can change the file format simply by selecting a different option on the combo box. Of course, Approach is completely compatible with its "colleague" Lotus 1-2-3, and as we have seen applications developed with LotusScript can be used with other products in Lotus SmartSuite.

Approach receives top marks here.

9. Complete, easy-to-understand documentation

This is without a doubt Approach's worst area. The printed documentation is a manual of about 50 pages. It is intended only as a brief introduction to using Approach, and as far as it goes it's not too bad. Two LotusScript documents in PDF format can optionally be copied to your hard drive at installation time. Together they contain about 600 pages, and they are quite good. These manuals are easily the best documentation accompanying Approach.

However, for the vast amount of your work on Approach, you have only the online help to turn to. Even an online manual in book format would probably be a big help. The online help is not only inadequate, it is outright obtuse. One error message declares, "This operation is not revertible." I had to search through two dictionaries and an encyclopedia to discover the meaning of "revertible"! The emphasis seems to be to help you understand what to do in certain situations, but gives you little information on why you should do it or what Approach does when you select that option. Rather than clarify how Approach works, most of the time it obfuscates it. Without some documentation other than this kind of online help, the user is left feeling lost and frustrated a good deal of the time.

Approach gets a rating of poor in this category.

10. Tight security

Any serious database must take steps to ensure unauthorized individuals can't view or alter sensitive data, such as payroll records. Approach does this in two ways, both depending upon passwords. First, if a password is assigned to a table, then all the data in the table is encrypted before it is stored on disk. Second, the much-advertised Team feature of Approach allows the administrator to assign many levels of security, each with its own set of accessible fields. Each field available to a user can be set to read-only or read-write.

Approach receives top marks in this category.

As you can see, Approach has many stengths, but besides the few bugs I mentioned, falls down when it comes to speed, documentation, support, and data integrity. However, this last one is an application-killer.

It's hard to give an overall rating to Approach because it has so many outstanding points and yet several serious weaknesses. I decided to settle on an average (C). However, you'll have to decide if it's suited for your needs.

—Walter Metcalf

Notes

¹ DBExpert from Sundial Systems has what might be called conditional relational integrity. If the DBExpert database is built using an imported database, then the DBExpert database will share the characteristic of the parent database. If the database is built using DBExpert, then the database will or will not have referential integrity depending on the options selected at build time.