A safer method of choosing tuples with complex conditions within an HTML document

Continuing this unintended series of blogs about choosing customers for display within an HTML document when the conditions for the inclusion of a customer are complex .... We've seen that deleting records from the linked CUSTOMERS table can be dangerous, because in certain circumstances that table might not be linked.

There was a very interesting comment left on that entry that suggested using a temporary table such as STACK: one could link this table, load it with records from CUSTOMERS, test the values and then delete from the STACK table. It wouldn't matter too much if this table were not linked. Whilst this technique can work in complex procedures, it won't work in an HTML document. The SDK states in the section 'Basic commands' in chapter 5 that HTMLCURSOR creates a linked file that holds the records selected in the PAR input parameter (emphasis mine). I have always wondered how this command 'knew' from which table to select, and now I know. Linking STACK to :$.PAR then inserting records into this linked table from CUSTOMERS didn't work in my tests (the insertion worked fine, but the procedure did not display any documents).

Here is a much safer method, although it requires some preparation. Add a field to the CUSTOMERS table and call it something like TEST_WWWFLAG with type CHAR, length 1. Set the value of this field to 'Y' in the step that checks the suitability of records for inclusion before the cursor iterates over the records. Thus one can simply write UPDATE CUSTOMERS SET TEST_WWWFLAG = 'Y'. As this should be the only place in Priority that accesses this field, it doesn't matter whether the table is linked or unlinked. At the end of the cursor code, replace the infamous DELETE statement with this: UPDATE CUSTOMERS SET TEST_WWWFLAG = '\0' WHERE CUST = :CST. Thus any record that fails all the checks will have this field empty.

HTMLCURSOR then becomes SELECT CUST FROM CUSTOMERS WHERE CUST > 0 AND TEST_WWWFLAG = 'Y'. Problem solved.

If anyone is wondering: fortunately the deletion of customers happened at the end of the day so not many people were inconvenienced. After a series of phone calls, the database administrator was able to restore the missing table. I checked that the data had been restored successfully by accessing customer orders that had been entered that day then checking their status log to see until when there were changes. Even so, there was one check that had not occurred to me: it might have been that a customer had been added after the backup that was restored; in this case, there would be no record in CUSTOMERS but there would be in CUSTOMERSA. A quick check revealed that indeed there was such a record, so I quickly added a new customer; this received the same autoinc key number as the final record in CUSTOMERSA. In the customers screen, I saw that this customer had a linked contact person (!) , causing me to check whether there was a sales opportunity for this unknown customer. Indeed there was, enabling me to see who had opened the customer; from this person I obtained the customer's name. So that episode is safely behind us.