Using Indices in Reports

by Steve Parker

Published 1998-06-01    Printer-friendly version

Download the code here

Publisher's Note

At several points in this article, the template code is much too long to fit in a normal width document. We've used continuation characters ( the rule character | ) to show where the code is continued. This code will not work as shown in this article. You should use the enclosed templates instead. We hope this message helps eliminate the confusion that could be caused.

Clarion provides two, and only two, ways to access data files: physical record order and indexed access.

Since record order is rarely either useful or meaningful as a display order, we will not consider it further here. Note that by "display," I intend to include anything an end user might see and which might contain more than one record. So, "display" includes both browses and reports.

Types of Indices

An index allows us to display data in a file in a different order than the order in which the records are physically stored on the disk. So, while records are stored in the order in which they were created, unless the file is physically sorted, an index allows display in another order. For example, we can display records ordered by surname, invoice number or customer ID. Using indices, we can also access a single record or subset of records directly using Access:file.Fetch(index) or Get(file,index).

Typically, sequential access is used in file browsing procedures, reports and batch processes. These provide end users (hopefully -- though sometimes there is no telling with end users) with meaningful displays of their data. Direct access is typically used for data validation and lookups.

TopSpeed provides three types of indices: static, dynamic and automatic. The attributes used on file declarations for each are: Index, Dynamic (index) and Key, respectively.

An Index is known to the application but is not maintained by it. The developer must update an Index as and when needed with the BUILD() statement. A Key, on the other hand, is automatically maintained by the application. That is, whenever the file is touched (a record added, deleted or changed), all of its Keys are updated. So, a Key is just a special sort of Index; one that is automatically maintained (and that is very special indeed).

A Dynamic Index is in many ways even more special. Like a standard Index, a Dynamic Index requires a BUILD() to update but, unlike an Index or Key, contains no components. That is, at design time, you do not specify which fields comprise the index. The absence of component fields, of course, implies that some time before actually using a dynamic index, its components (and whether they are ascending or descending) must be specified. This allows one Dynamic Index to be used for multiple purposes and, even, for you to allow the user to specify those components at runtime. You can even specify a record filter when you BUILD() the file, should you need or wish to.

Dynamic Indices exhibit two very desirable features. First, they do not require exclusive access to the file before a BUILD(). An Index (or Key) does require exclusive access to the file in order to BUILD() it and that means that the file must be locked or opened with write denied to all others. Since a Dynamic Index does not require exclusive access, it can be built while another procedure is using the file. Second, because it uses temporary files, two users can BUILD() different components on the same index simultaneously. The downside is that because these temp files are removed after use, building a Dynamic Index cannot be incremental (though using filters may positively impact the speed of this process).

The automatic maintenance of Keys introduces some very important design considerations. First, any Key declared on a file must be opened when that file is opened. This means that for file systems that store Keys in external files, an additional file handle per Key will be used. A file with many Keys in a procedure that also has several lookups, along with their files and Keys, can easily swamp a network's Files parameter. Secondly, including file systems like TopSpeed and Btrieve that store Keys internally, memory is used to load each key. Finally, since Keys are updated whenever the data file is touched (if you display a record on a form, even though you do not actually change anything, you have "touched" the file), you incur processing overhead for each Key declared. Each of those Keys will be updated.

For these reasons, Keys have tended to be reserved for browsing and batch procedures. Reports that require sorted access, but which cannot be based on an existing Key, have traditionally used an Index. It takes only minimal consideration to realize that you simply waste resources, bandwidth, memory and disk space, if you create Keys for reports that are not run daily.

Using Indices in Reports

If an Index is not built immediately before use, the report may fail to contain accurate data. If the Index has never been built or if, during disk maintenance, it was removed, the report will contain no data at all. You may even get an error, one that an end user might¼ question.

To verify this for yourself, compile and run the attached sample app. Enter some records in the Name file. Then run the report that does not build the index. Then run the report that does. Enter some more records and run the two reports, in the same order, again. You see what I mean? Now, just how many support calls do you want?

There are template symbols that can be used to determine whether you have requested Key or Index access. However, the Report template does not use them to generate code to BUILD() an Index at runtime when a report is Index-based. There really are too many permutations for the templates to be reliable in discerning our desires.

Since the code is not generated for us, we have little choice but to take control of the process ourselves (part of the point, I expect). Now, consider what might be required in even a small application: We may need to "take control" 15 or 20 times. Great ¼ just great.

However¼ we can pass entities to procedures and functions, hmm.

The ability to pass entity parameters means that we could pass the file and key/index labels to a function and get back a result telling us whether the index was built or not. (A function is better suited to this purpose than a procedure because we can use its return value to determine whether the Index was or was not built and respond accordingly.)

If we can make such a function, we need only one procedure for the whole application instead of placing code into each report. That is great. Going a step further, this function could just as easily be made into a template or compiled into a LIB, DLL or object and that means we only need to write this code once for as many applications and reports we may (ever) have. And, if the code is placed in a LIB or DLL, it only has to be compiled once for as many applications and reports we may (ever) have. Yeah, that really is great.

The Basic Functionality

So, what exactly do we need to do? It really isn't as simple as BUILD(IndexLabel).

The first thing we should to do is advise the user that an Index is being built. Because building larger files (or even moderately sized files on slower networks or computers) can take several minutes, displaying a message keeps the user from thinking that the system has crashed or hung. And, any kind of window is certainly nicer to look at than an hourglass. Moreover, the user doesn't need to wonder why the hourglass is displayed. (N.B.: the BUILD() statement in C4 has properties which return the percent of the process completed, but I haven't figured out how to use them yet.)

Next, we need to actually display the window (newbie note: opening does not make a window visible; it only reserves and initializes the necessary memory for the structure). Then, because BUILD() requires exclusive access to the file, we need to LOCK() the file. If the LOCK() is successful, we can actually BUILD() the Index. If you do not LOCK() the file, you must open the file in deny write mode (i.e., 2h or 22h). Because OPEN() or TRYOPEN() make just one attempt, I prefer LOCK(). LOCK() will try continuously for the period of time specified in its second parameter.

The report's Beginning of Procedure, After Opening Files embed (Clarion templates) or the WindowManager Method Executable Code Section¼ Init¼ (),Byte with Priority "Last" (ABC templates) is the appropriate place for these steps:

  OPEN(MessageWindow)
  DISPLAY
  LOCK(FileLabel,.1)
  IF ERRORCODE()
    CLOSE(MessageWindow)
    MESSAGE('This file is in use. Index could ' &|
            'not be built.','Warning',Icon:Hand)
    RETURN(Level:Fatal)
    !DO ProcedureReturn
  END
  BUILD(PRE:KeyLabel)
  UNLOCK(FileLabel)
  CLOSE(MessageWindow)

First, the message window is opened and displayed. Then, because BUILD() requires exclusive access to the file, we attempt to LOCK() the file.

If you are using a Dynamic Index, locking is not necessary. Though when using a Dynamic Index, you do need to specify the component fields. This is easily handled in the second parameter of the BUILD() statement, either using a variable or a string literal.

The LOCK() attempt lasts for 1/10 second. If it fails, another process must be using the file. So, we display a message and terminate the report. (If 1/10 is too short for you, adjust upward to a value that seems more reasonable in your circumstance. You could even re-write the function to take this as a parameter.)

If users state that they frequently cannot run reports because they get the file-in-use message (i.e., other users are working with the file) or if you have reason to believe this may be a problem, seriously consider using a Dynamic Index instead.

If the LOCK() succeeds, the Index is built and the file unlocked. Note that I do not issue an UNLOCK() unless the Index is actually built. This is because the file was not locked unless the Index was built. So, it is necessary to UNLOCK() it only if the Index was built.

The message window is closed and the report procedure continues.

From this code, we can also infer what our prototype would look like:

BuildNDX(File,Key)

So Far, So Good

So far, so good. However, the code outlined creates a procedure, not a function, and we have not provided for Dynamic Indices.

Again, we want to be able to place this code out in an external file, a LIB, DLL or object. In addition, we want a function. That means that our call in the report should look something like:

IF NOT BuildNDX(FileLabel,Pre:IndexLabel)
  RETURN(Level:Fatal)
  !DO ProcedureReturn
END

That means that our prototype should be:

BuildNDX(FILE,KEY),BYTE

So, we need a datum in our function to store the return value. Further, it is possible that the index we want to build is a Dynamic Index. To build a Dynamic Index, we will want to pass a string or string variable containing the components and we will not need to lock the file. For example:

IF NOT BuildNDX(Customers,CUS:DynaNDX,'+CUS:Date')

or

IF ~BuildNDX(Students,STU:FlexKey,LOC:SortString)

Our final prototype becomes:

BuildNDX(FILE,KEY,<STRING>),BYTE

I usually pre-pend a "p" in my parameter list, so my parameter labels will be: p_File, p_Key and p_String. If p_String is not blank, it follows that we are trying to build a Dynamic Index. If p_String is omitted or blank, we are dealing with a Static Index. The final code for the function looks like:

BuildNDX PROCEDURE(p_File,p_Key,p_String)
Ret_Val Byte
messagewindow WINDOW,AT(,,76,49),CENTER,GRAY,DOUBLE
                BOX,AT(0,0,77,49),USE(?Box1),COLOR(00H),FILL(0FFH)
                STRING('Building Index'),AT(15,14),|
                       FONT(,,,FONT:bold),USE(?String1),TRN
                STRING('Please Wait ...'),AT(15,25),|
                       FONT(,,,FONT:bold),USE(?String2),TRN
              END
  CODE
  OPEN(MessageWindow)
  DISPLAY !keep user up to date
  IF p_String !If Dynamic Index
    BUILD(p_Key,p_String) !build it
  ELSE !Else Static Index
    LOCK(p_File,.1) !Try to lock it
    IF ERRORCODE() !If unable to lock
      CLOSE(MessageWindow)!notify user
      MESSAGE('This file is in use. Index could ' &|
              'not be built.','Warning',Icon:Hand)
      Ret_Val = 0
      DO ProcedureReturn
    END
    BUILD(p_Key) !otherwise, build index
    IF ERRORCODE() !check for errors
      STOP('Problem after attempting to build index.' &|
           <13,10>' & ERRORCODE() & '/' & ERROR())
      Ret_Val = 0
      DO ProcedureReturn
    END
    UNLOCK(p_File) !be nice, unlock file
  END
  Ret_Val = 1 !Successful
  DO ProcedureReturn
ProcedureReturn ROUTINE
  CLOSE(MessageWindow)
  RETURN(Ret_Val) !Advise caller

Notice that I use my own ProcedureReturn routine. There is nothing special in this label. I am just accustomed to using it; it could just as easily have been called Martha, Ted or Zebra. What is important is that it provides a single exit point and ensures that the success/failure value in Ret_Val is sent back to the calling procedure. (Mostly it means that I only have to type "Close(MessageWindow)" once instead of three times.)

The beauty part? This function works with every version of Clarion (since CW1.0) and both template chains in C4. Change the window to a screen, and it works in CFD.

The libraries attached to my January and February articles contain functioning versions of BuildNDX().

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