Best practice for opening and closing tables

The use of the 'clalev' function to avoid to declare database tables too frequently must now be banned. Let's explain why and how you can do now in Versions 7 and above.

Use of 'clalev' in V6 version

You probably use this type of code so often in V6 version:

If clalev([ITM])=0 : Local File ITMMASTER [ITM]  : # Variant 1 
If ! clalev([BPC]) : Local File BPCUSTOMER [BPC] : # Variant 2

The question is: why did we write this type of code in V6 version?

The answer is simple: Local File is a costly instruction due to the fact that the table description stored in a ".fde" file, is opened and analyzed prior to the creation of the class in memory.
So, doing this every time when a sub-program is potentially able to access to database tables is a waste of time. As clalev enables us to test if the class already exists, it is considered as a better option.

Yet this conception suffer from many flaws and generate the following side effects:

If the table has been previously opened with a 'where' clause, the filter is still there if the File declaration is not executed.
The table could have been opened on another folder than the default one.
If a Filter had been applied on the table and was not cancelled, the filter is still active on the table.

When you develop in service mode, you need to take into account the fact that several calls of different contexts can be done in a given process. So, using clalev is no more safe and must be banned from any Versions 7 and above code.

Another good reason to forget 'clalev' is that Local File has been optimized and is no more costly to execute.

What are the good practices in Versions 7 and above ?

In a Subprogram (called by Call), a Function (called by Gosub) or a Method (invoked by Fmet), declaring a table does not cause a problem even if it has been opened in the calling sequence. As most of the code is called that way, you can freely declare the tables you need in the routines you will write. In fact, you have to declare them if you want to be sure that the table is opened in the right context.

Obviously, if you declare the same table in a sequence called by Gosub, an error will happen. This is the case here:

Local file BPCUSTOMER [BPC]
Gosub DECLARE_TABLE
Call ANOTHER_SUBROUTINE
...
End
$DECLARE_TABLE
Local File BPCUSTOMER [BPC] : # Here, an error will occur (table already opened)
...
Return

If for any reason you want to re-open a table, you have to close it first by using a new instruction called LogicClose. The syntax is the following:

# Let's close the table BPCUSTOMER
LogicClose File [BPC]

What is the difference with the Close instruction?

Logically the table is no more online, and LogicClose doesn't destroy the memory resources used. So the execution of the next File instruction will be faster.

Index Home