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?