Even more on 'a safer method': possibly the safest method of all

One comment to these blogs basically said that the PAR parameter in the initial INPUT step doesn't have to be the same as the implicit PAR parameter in HTMLCURSOR. Bearing this in mind, I wrote a new procedure that has the following intermediate step between the INPUT and HTMLCURSOR steps.

SELECT :$.PAR INTO :OLDPAR FROM DUMMY;
LINK CUSTOMERS OLD TO :OLDPAR;
SELECT SQL.TMPFILE INTO :$.PAR FROM DUMMY;
LINK CUSTOMERS TO :$.PAR;
DECLARE CUR CURSOR FOR
SELECT CUST, CUSTNAME
FROM CUSTOMERS OLD
WHERE CUST > 0;
OPEN CUR;
GOTO 300 WHERE :RETVAL <= 0;
LABEL 100;
FETCH CUR INTO :CST, :CNAME;
GOTO 200 WHERE :RETVAL <= 0;
LOOP 100 WHERE RSTRIND (:CNAME, 1, 1) <> '0';
INSERT INTO CUSTOMERS (CUST, CUSTNAME)
VALUES (:CST, :CNAME);
LOOP 100;
LABEL 200;
CLOSE CUR;
LABEL 300;
UNLINK CUSTOMERS OLD;
UNLINK CUSTOMERS ;

This actually works! Obviously I'm taking an arbitrary range of customers but one that meets the criterion of having a number that ends in 0 does have orders and the procedure does create a report of orders for this specific customer. In the real program, a series of tests are run, and only customers that pass these tests are inserted into the linked table.

This method is the safest and easiest - no deletions, no external tables and one person can run the report more than once concurrently with different parameters.

One reason why my previous tests using STACK as an intermediate table failed is that the new record has to be inserted with both the A and U keys (cust and custname respectively). STACK only has one field into which I was inserting the A key; looking at this now in retrospect, it's clear that this wasn't sufficiently as every tuple has to have a value for the U key.

Still more on 'a safer method'

Yesterday's blog included this statement HTMLCURSOR then becomes SELECT CUST FROM CUSTOMERS WHERE CUST > 0 AND TEST_WWWFLAG = 'Y'. Problem solved.

Looking at it this morning, this statement is not strictly true: the problem is solved as long as only one person is running the procedure at any given time.

A better way of solving this would be to define a new table, (say) TEST_CUSTUSER that would have two fields CUST and USER that together comprise the table's key. Then instead of writing UPDATE CUSTOMERS SET TEST_WWWFLAG = 'Y', one would write

DELETE FROM TEST_CUSTUSER WHERE USER = SQL.USER;
INSERT INTO TEST_CUSTUSER (CUST, USER)
SELECT CUST, SQL.USER 
FROM CUSTOMERS /* may or may not be linked */
WHERE CUST > 0;

And of course HTMLCURSOR has to be revised to become

SELECT CUST 
FROM TEST_CUSTUSER 
WHERE CUST > 0
AND USER = SQL.USER;

This still is not ideal: whilst two users can now run the procedure simultaneously, a given user cannot run the procedure with different inputs simultaneously. SQL.DATE would not work as the user could invoke the procedure twice with different parameters within the same minute; also SQL.DATE at the beginning of the procedure would probably not be the same as SQL.DATE at the HTMLCURSOR stage. 

A possible solution to this would be to add another field to the table, GUID. At the beginning of the program one would add the statement :$.MYG = SQL.GUID, then add this field to the delete and insert statements. I suspect, though, that HTMLCURSOR might not recognise this global variable ... and indeed it doesn't. Even had this worked, one doesn't need to use the SQL.GUID function (returning a random 32 char string); one could simply define an integer whose maximum value would be established on entering the procedure, then incremented for the current procedure's run. But again, there would be no way of using this field in HTMLCURSOR. Even storing this maximum value in the LASTS table and accessing it from there in HTMLCURSOR wouldn't work - LASTS is not on a per-user basis, and even if it were, the second run might finish before the first run, so the value in LASTS would not be the correct one.

 

Any ideas? 

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. 

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.

How to choose sections within an HTML document

I was recently tasked with creating a document that shows general details about a customer, how many price quotations have been issued to the customer, a list of the price quotations themselves, a list of customer orders, service calls, etc. There is a standard document called WWWSHOWCUST that shows some of this data but for some reason it is laid out in a very unattractive manner, and anyway I wanted to include data that isn't included in the standard report (as well as discarding certain sections).

I started off with a 'simple' HTML document: at the beginning, the document is presented with a list of customers and then one by one, the various data are shown, where each type of datum resides in its own report (that is, one report for sales opportunities, one for price quotations, etc). To complicate matters slightly, I decided to add a parameter at the beginning that defines from which date the data will be displayed (or in other words, don't display price quotations (for example) from before this date. So far, so good.

Then I considered the possibility that a user might not want to see all the data; for example, there is a certain amount of duplication between orders and projects (at least, in the way that we work), so there's little reason to show both. I decided that I would have a series of global variables that could be chosen, and depending on these values, the corresponding report would be shown.

To the left is shown the parameters' screen; the checkboxes are connected to variables :$:A1, :$.A2, etc. 

Today's tip is concerned with how those choices are translated into which reports are shown. Let's say that price quotations will be shown if variable :$.A4 = 'Y'. First of all, this parameter is passed to the report, along with CST, which holds the current customer number. In the report one adds the inscrutable line DUMMY.DUMMY whose expression is = 1 and :A4 = 'Y'. For reasons that have never been explained, every DUMMY.DUMMY expression has to start with = 1. If the second half of the expression is true, the DUMMY.DUMMY will be true and the report will be printed. If not ....

This technique enables the HTML document to have one format, where every user chooses what to display, instead of 7! (5040) different combinations!

A later request, that I was able to serve partially, was to display only data belonging to a given branch. This is not a problem for the reports that display price quotations, etc (one simply passes a parameter containing the branch numbers) but it's a problem for reports that don't display data per branch - our company header and the current customer's data. True, it is possible to define that a customer belongs to a specific branch, in which case there would be no problem (one would add the branch in the HTMLCURSOR stage), but we don't do this as customers are free to order from different branches (this is intended to reduce the number of duplicate customers). At the moment, if one passes to the procedure a range of customers, there will be a header for every customer even if there is no data for that customer. Not good.

The problem is how to filter the data (or weed out unwanted customers) before the procedure gets to the HTMLCURSOR stage. I spent about an hour working on this but didn't resolve the situation to my satisfaction. I can see now that I was filtering after the HTMLCURSOR stage whereas in fact I have to do so before this stage, at the very beginning. Maybe I will be able to find a solution after all.