Using NFILE prevents complications

Someone sent me some code written by a third party that inserts data into the table LABELS, naturally for printing labels. The person who sent me the code wanted the sort order of the labels to be changed so that it would be according the part numbers sorted alphabetically. As the original programmer used the INSERT INTO/SELECT FROM syntax, it wasn't possible to simply add 'ORDER BY PART.PARTNAME' at the end of the query. 

The solution was provided by what seemed to be a somewhat pointless subroutine earlier in the program.

SUB 20;
INSERT INTO STACK4 (KEY)
SELECT PART
FROM   PART
WHERE  PART <> 0;
RETURN;

Saving the parts in this table gave me the opportunity to add a sort order to STACK4 as per the following.

:SORDER = 0;
DECLARE CSUB50 CURSOR FOR
SELECT PART, PARTNAME
FROM PART
WHERE PART > 0
ORDER BY PARTNAME;
OPEN CSUB50;
GOTO 530 WHERE :RETVAL <= 0;
LABEL 510;
FETCH CSUB50 INTO :PART, :PNAME;
GOTO 520 WHERE :RETVAL  <= 0;
:SORDER = :SORDER + 1;
UPDATE STACK4
SET INTDATA = :SORDER
WHERE KEY = :PART;
LOOP 510;
LABEL 520;
CLOSE CSUB50;
LABEL 530;

Then I could make use of STACK4.INTDATA in the statement that inserts the data into LABELS, ensuring that the labels would indeed be sorted by partname. Later on I noticed that the programmer had not used the field LABELS.SORT - STACK4.INTDATA can be inserted into this field in order to ensure the correct sort order [see below *].

Reviewing the code. I saw that the programmer had declared a subroutine 30 that on first glance appeared to be the same as subroutine 20; the only difference was that instead of FROM PART, the second subroutine had FROM PART ORIG. In other words, using the unlinked PART table instead of the linked table. How was the programmer checking whether PART was linked? By the following code

:E_COUNT = 0;
SELECT COUNT(*)
INTO   :E_COUNT
FROM   PART
WHERE  PARTNAME NOT IN ('', :E_CHVAL)
;
:E_ORIGCOUNT = 0;
SELECT COUNT(*)
INTO   :E_ORIGCOUNT
FROM   PART ORIGPART
WHERE  PARTNAME NOT IN ('', :E_CHVAL)
;
:E_LINKPART =
( :E_COUNT <> :E_ORIGCOUNT AND :E_COUNT <> 0 ? 'Y' : '\0' )

Either SUB 20 or SUB 30 would be invoked, depending on the value of the variable :E_LINKPART . Although I haven't seen the definitions of the various parameters,I am sure that the parameter PAR (implying that this code is called from another procedure) is defined as FILE. If it were defined as NFILE, then there is no need for :E_LINKPART and only one subroutine would be needed.

LINK PART TO :$.PAR;
ERRMSG 500 WHERE :RETVAL <= 0;
GOTO 1 FROM PART WHERE PART > 0;
UNLINK PART;
LABEL 1;
...
GOSUB 20;

I realise that I've made a mountain out of a molehill, but it seems that the original programmer had to build a baroque solution to solve a problem that has a very simple solution. This is not the first time I've seen this, and I think that it stems from an incomplete understanding of how to program - not necessarily how to program in Priority.

Even without using NFILE, the programmer still could have saved the SUB 20/30 duplication:

:E_COUNT = :E_ORIGCOUNT = 0;
SELECT COUNT (*) INTO :E_COUNT FROM PART;
SELECT COUNT (*) INTO :E_ORIGCOUNT FROM PART ORIG;
GOTO 1 WHERE :E_COUNT <> :E_ORIGCOUNT;
UNLINK PART;
LABEL 1;

After all, there's no point in the clause WHERE PARTNAME NOT IN ('', :E_CHVAL) if the same clause is used in both queries; at worst, both values might be one too high (e.g. 37 instead of 36) but as equality is being checked, it makes no difference. Incidentally, :E_CHVAL appears not to be defined anywhere in the procedure; another rookie mistake (it turns out that :E_CHVAL was defined in a previous stage of the procedure).

* To show that I too am not immune to writing sub-optimal code, the code that enters data into STACK4 could be rewritten as below, without the need of a prior INSERT INTO statement. There's no real need to use a subroutine at all as this code is called only once, although I understand why the programmer did this: using subroutines gives the procedure a sense of structure.

SUB 20; /* Insert data into stack4 in partname order */
DECLARE CSUB20 CURSOR FOR
SELECT PART, PARTNAME, SQL.LINE
FROM PART
WHERE PART > 0
ORDER BY PARTNAME;
OPEN CSUB20;
GOTO 230 WHERE :RETVAL <= 0;
LABEL 210;
FETCH CSUB20 INTO :PART, :PNAME, :LINE;
GOTO 220 WHERE :RETVAL  <= 0;
INSERT INTO STACK4 (KEY, INTDATA)
VALUES (:PART, :LINE);
LOOP 210;
LABEL 220;
CLOSE CSUB20;
LABEL 230;
RETURN;

Using the COUNTRIES table as a parameter

One of my clients likes to use the COUNTRIES table as a parameter to reports. Unfortunately, the COUNTRY field in the CUSTOMERS table is not mandatory, so there can be customers without a defined country.

Running a procedure with this parameter set to a specific country works correctly - customers with that country are displayed. Running the procedure with country set to * returns all the customers that have a defined country. What about those customers without a defined country? Leaving the parameter empty apparently causes the error "String or binary data would be truncated (Error 8152)". I don't understand why this error message should appear.

In the past, I might have suggested 'solutions' such as setting the parameter to be 'not equal to Guadeloupe', in the hope that no existing customer is situated in Guadeloupe (where is Guadeloupe? It's "an overseas department and region of France in the Caribbean").

Yesterday my brain must have been working correctly as I saw a much better solution: define the parameter as type NFILE instead of FILE. Add to the code  this section

LINK COUNTRIES TO :$.COU;
ERRMSG 1 WHERE :RETVAL <= 0;
GOTO 1 FROM COUNTRIES WHERE COUNTRY > 0;
UNLINK COUNTRIES;
LABEL 1;

This means that if the parameter is left empty, the COUNTRIES table will be unlinked and the following query will use the original COUNTRIES table, in which the empty value exists. Indeed, after changing the parameter's type, running the procedure with the COUNTRIES parameter left empty worked perfectly.

NFILE: advantages and disadvantages

I am sure that I have written about NFILE at some time but I can't find the reference and so I'll start from the beginning. [Edit: I found the entry which now has the NFILE label]

Let's say that one has a procedure that accepts a list of customers as its input - let's even say that it's the HTML document that I worked on yesterday. When one enters a list of customers, the linked customers table will include only those customers, obviously. When one writes * as the input, all the customers from the unlinked customer table have to be copied into the linked customers table; this takes time! 

Enter NFILE: this was introduced in version 17 IIRC. The documentation says: Select NFILE if the linked file comprises a group of records and you want the link table to remain empty when the user enters * or leaves the field empty. How does one use this? By writing code as follows

LINK CUSTOMERS TO :$.CST;  /* NFILE */
ERRMSG 1 WHERE :RETVAL <= 0;
GOTO 1 FROM CUSTOMERS WHERE CUST > 0;
UNLINK CUSTOMERS;
LABEL 1;

This saves time in copying records from the unlinked table to the linked table. Tables that benefit from this are PART, DOCUMENTS, INVOICES and CUSTOMERS. Maybe a few others.

Now back to the HTML document that I described yesterday. I realised that I could filter unwanted customers using a cursor and a series of expressions such as

GOTO 999 WHERE NOT EXISTS (SELECT 1 FROM BRANCHES
WHERE BRANCH > 0);
LINK CUSTOMERS TO :$.CST;
LINK BRANCHES TO :$.BRN;
DECLARE C15 CURSOR FOR
SELECT CUST FROM CUSTOMERS
WHERE CUST > 0;
OPEN C15;
GOTO 999 WHERE :RETVAL <= 0;
LABEL 100;
FETCH C15 INTO :CST;
GOTO 800 WHERE :RETVAL <= 0;
GOTO 200 WHERE :$.A3 <> 'Y'; /* No need to check price quotes if not wanted */
:HASDATA = 0;
SELECT 1 INTO :HASDATA
FROM CPROF, CPROFA, BRANCHES
WHERE CPROF.PROF = CPROFA.PROF
AND CPROFA.BRANCH = BRANCHES.BRANCH
AND CPROF.CUST = :CST
AND CPROF.PDATE >= :$.DAT;
LOOP 100 WHERE :HASDATA = 1;
....
LABEL 700;
/* If we are here, then the customer has failed all the tests
so delete it from the linked table */
DELETE FROM CUSTOMERS WHERE CUST = :CST;
LOOP 100;
LABEL 800;
...

This code is repeated, once for each type of record - sales opportunities, price quotes, orders and projects. The first two lines check whether any branches were passed as a parameter: there's no point in checking anything if there are no branches. I added all the necessary code, ran the program using one specific branch without designating customers: the procedure took a few minutes to sort itself out then showed the output: as designed, only customers with data from the chosen branch were shown.

I wondered how I could improve the procedure's speed - the obvious step would be to define CUSTOMERS as type NFILE instead of FILE. There would be no need to copy customers to the linked table which would save time. So I added this change, ran the program ... and then realised with a shudder that the DELETION WAS NOT FROM THE LINKED TABLE BUT FROM THE REAL TABLE!!! The first code snippet that I showed unlinks CUSTOMERS; thus the DELETE statement does not work on the linked table but rather the real table.

I reached for the power switch (figuratively) as soon as possible but it was too late. A few minutes later, someone called to ask whether customers had disappeared. My code has managed to delete about 16,000 customers out of 24,000 in a few minutes. Straightaway I phoned the network manager who took Priority off line, and then we turned to our database administrator who started restoring the damaged table.

Bottom line: be wary of NFILE! Normally it can help but sometimes it is dangerous.

More about transferring data from screen triggers to procedures

I wrote two months ago about calling a procedure from a screen trigger, showing this code

LINK CUSTOMERS TO :FILE;
INSERT INTO CUSTOMERS
SELECT * FROM CUSTOMERS ORIG
WHERE CUSTOMER = :$$.NSCUST;
EXECUTE BACKGROUND ACTIVATE '-P', 'TEST_COPYCUSTOMER', 
'CUSTOMERS', :FILE;
UNLINK AND REMOVE CUSTOMERS;

I didn't write about the procedure that this code calls, namely TEST_COPYCUSTOMER. I want that the procedure can be called from a menu, where the user enters a list of customers, as well as this background call, where there is no user interface. In order to accomplish this, the procedure has two stages: the first is an INPUT stage, which has one parameter named PAR which is linked to the customers table, and the second stage is an SQLI stage that does the actual work.

An improvement that appears in later Priority versions (possibly from 17, definitely from 18) is that there is a new file type, NFILE. In previous versions, if someone entered * (i.e. all records), then all the records in the given table would be copied into the linked table. This can take time, so the new file type, NFILE, was created: the documentation says "Select NFILE if the linked file comprises a group of records and you want the link table to remain empty when the user enters * or leaves the field empty".

What the documentation doesn't show is when and how to use this new file type. I use it for parts, customers, suppliers and sometimes customer orders and inventory documents, when there is a strong possibility that the user will leave the parameter empty, meaning 'choose all records'. There's no point in using an NFILE if the user is always going to choose a list of values.

How is the NFILE used in practice?

LINK CUSTOMERS TO :FILE;  /* NFILE */
ERRMSG 1 WHERE :RETVAL <= 0;
GOTO 1 FROM CUSTOMERS WHERE CUST > 0;
UNLINK CUSTOMERS;
LABEL 1;

If the user left the parameter empty (or *), then there will be no records in CUSTOMERS, and so the statement GOTO 1 FROM CUSTOMERS WHERE CUST > 0 will not execute, thus unlinking the CUSTOMERS table, meaning that future references to this table will mean the original table. But should the user enter even one customer number, the branch to label 1 will be taken and future references will mean the linked table.

Now back to the procedure TEST_COPYCUSTOMER: for reasons which at the moment escape me, the PAR parameter being passed from the screen trigger to the procedure was empty. As CUSTOMERS is defined as an NFILE and is empty, the linked table is unlinked and so the procedure works on ALL the records in the CUSTOMERS table - not what I wanted! In order to prevent this, I defined CUSTOMERS as a regular FILE, accepting that there would be a performance hit when the procedure is called directly from a menu.

But why is the PAR parameter empty? I don't know (as yet), but when I removed the BACKGROUND part of the statement EXECUTE BACKGROUND ACTIVATE, the code worked properly. It appears that there is a difference between installations, as this code worked properly at one site but did not at another site.

Another point to notice is the use of ACTIVATE: normally I would use the external program WINACTIV. The documentation states that The difference between the WINACTIV command and the ACTIVATE and ACTIVATF commands is that WINACTIV has a user interface, meaning that you can define a progress bar and/or messages that require a response from the user (using a PRINTF, PRINTCONTF or CHOOSEF command) and these will be visible to users while the procedure is running, whereas the ACTIVATE and ACTIVATF commands will not display these elements. As such, the WINACTIV command cannot be used when working with the Priority web interface. The client where the code did not work properly uses the web interface as well as the traditional interface so I thought it prudent to use ACTIVATE instead of WINACTIV.

To conclude, my original code used two optimisations, namely BACKGROUND and NFILE. These optimisations may not work on some systems.

[Edit from 28/02/23] I faced a similar situation today: a procedure is executed automatically every few minutes, and if it finds a candidate order, that order is passed to another procedure that does something complicated to the order. The calling procedure was as follows

SELECT SQL.TMPFILE INTO :NEW FROM DUMMY;
LINK ORDERS TO :NEW;
GOTO 99 WHERE :RETVAL <= 0;
INSERT INTO ORDERS
SELECT * FROM ORDERS ORIG
WHERE ORD = :ORD;
EXECUTE BACKGROUND WINACTIV '-P', 'TEST_PROC', 'ORDERS', :NEW;
UNLINK AND REMOVE ORDERS

Whilst the test procedure was activated, it never received an order number. Eventually the solution dawned upon me: the temporary file was being deleted before the test procedure could get its hands on the file. Solution: remove 'background' and wait for the called procedure to complete before deleting the file.