Problems when deleting files via the web interface

I have written several procedures that receive a file from 'outside' (eg purchase orders), perform whatever is necessary with the file (such as creating a customer order from the purchase order) and then delete the file. Of course this works fine with the Windows client, but with the web client, the file was not being deleted.

It took some time to figure out what was causing the problem, and as usual, when the cause was known, the solution was not far behind. Let's assume that a procedure has a parameter :$.NAM that will contain the name (and path) of the file to be handled. In the Windows client, that parameter would maintain its value and could be passed to the deletion command with no change. But this wasn't happening in the web interface.

When I ran the procedure under the debugger, I could see where the problem. Let's assume that the name of the file is 'R:\1.txt'. What does the debugger show?

:TEST.TEST.NAM = 'c:/tmp/tekwebsrv4f54z1784033580yc6/p2143200363.TXT';

The name of the procedure is TEST_TEST and as far as the debugger is concerned, :$.NAM becomes expanded to :TEST.TEST.NAM. But the value of this variable is something completely different to what was entered. Obviously the web server doesn't know what disk R is and a certain amount of translation occurs, but even if the file was located in a known directory such as ../../system/mail, this translation would still occur (and believe me, I tried this).

So when I write 'EXECUTE DELWINDOW 'f', :$.NAM, I am asking the system to delete a file called c:/tmp/tekwebsrv4f54z1784033580yc6/p2143200363.TXT, which of course does not exist. As a result of the translation, the file r:\1.txt does not get deleted. At first, I wondered how I was going to get the original name of the file as the first step that happens within the procedure is replacing the name with some unintelligable name. But then it occurred to me that the procedure is creating a customer order whose number is known (from the field GENERALLOAD.KEY1) and if I know the order number then I can extract from the order the purchase order number that is stored in the REFERENCE field. Thus I can rebuild the filename and delete it.

Sending reports via email in the Web interface, continued

Continuing from what I wrote¹ the other day, I have converted the code to be a trigger within the 'func' screen (i.e. a library of routines) to make life easier for any procedure that needs this functionality. Thus within 'func' there is now a trigger called PRIV_WEBSENDEMAIL.

/* PRIV_WEBSENDEMAIL- No'am, 09/07/26
send a report by email to a group in the web interface.
Inputs:
PAR1: report name
PAR2: group name
*/
SELECT SQL.TMPFILE INTO :PRIV_FILESIZE FROM DUMMY ;
LINK STACK TO :PRIV_FILESIZE ;
GENMSG 1001 WHERE :RETVAL <= 0;
:PRIV_TMPFILE = '' ;
SELECT STRCAT (SQL.TMPFILE, '.HTML') INTO :PRIV_TMPFILE FROM DUMMY;
EXECUTE ACTIVATF '-R', :PAR1, '-o', :PRIV_TMPFILE;
EXECUTE GETSIZE :PRIV_TMPFILE, :PRIV_FILESIZE;
GOTO 99 WHERE NOT EXISTS (
SELECT 1 FROM STACK WHERE ELEMENT > 0);
MAILMSG 600 TO GROUP :PAR2  DATA :PRIV_TMPFILE ;
LABEL 99;
UNLINK AND REMOVE STACK;

This trigger is invoked in a procedure as follows

/* Send report by email */
:PAR1 = 'PRIV_IMPORT_ORDSON';
:PAR2 = 'SPORDER';
GOTO 1 WHERE :SQL.NET = 1;
EXECUTE WINACTIV '-R', :PAR1, '-g', :PAR2;
GOTO 2
;
LABEL 1;
#INCLUDE func/PRIV_WEBSENDEMAIL
LABEL 2;

The only other definition that is required and is not described above is message #600 in the calling procedure; this message will be used as the title for the email sent from the web interface. This is because the message number passed to mailmsg has to be a numeric literal and not a variable.

Internal links
[1] 141

Sending reports via email in the Web interface

I am occupied these days by ensuring that all the procedures that I wrote for the Windows client also work with the Web interface. Yesterday I had an interesting example of this: a procedure accepts an external file, creates a customer order from the data in the file then sends a confirmatory email in the form of a report. Almost everything executed correctly, but the email was not being sent.

Looking at the 25.1 SDK, the following appears:

You can execute a procedure from an SQLI step of another procedure by executing any of
the following commands: WINACTIV, ACTIVATE or ACTIVATF. This is useful, for example,
when you want to run a report and send it to recipients via e-mail. 
Reports can be executed using the WINACTIV command only.
... (half a page later)
As noted above, reports can only be run by WINACTIV, which should not be used in the Web
interface.

(Emphasis mine)

So if reports can only be run by WINACTIV which should not be used in the Web interface, how does one send a report via email from the Web interface?

One of my private clients has a source inside Priority Software, and 'Deep throat' produced the following code:

SELECT SQL.TMPFILE INTO :FILESIZE FROM DUMMY;
LINK STACK TO :FILESIZE; 
ERRMSG 10 WHERE :RETVAL <= 0;
:TMPFILE = '';
SELECT STRCAT(SQL.TMPFILE, '.html') INTO :TMPFILE FROM DUMMY;
EXECUTE ACTIVATF '-R', '[reportname]', '-o', :TMPFILE;
EXECUTE GETSIZE :TMPFILE, :FILESIZE;
GOTO 2703 WHERE NOT EXISTS (
SELECT 'X' FROM STACK WHERE ELEMENT > 0);
MAILMSG 12 TO GROUP '[groupname]' DATA :TMPFILE;
LABEL 2703;

So much for the statement the 'Reports can be executed using the WINACTIV command only'. Here we have an example of ACTIVATF running the report. At the moment, I can't unconditionally report that this works: I tried this on my home computer, connected via VPN but not running RDP, whilst running Outlook. A letter was created with the required file and sent, but I didn't receive it at my work address. 

When I tried it yesterday on the work server, I initially got the error that I was trying to perform an action that requires the web plugin. The plugin existed but it wasn't turned on. When I tried again this morning, nothing was sent. Running the procedure with the debugger, I saw that the 'mailmsg' was jumped over. From this, I understand that the problem probably is as follows: any report to be run must not have any parameters for input. I had written the report so as to overcome this minor problem, but it only reports data for 'today', and today there is no appropriate data (although there was yesterday).

Debugging in the web interface

Before I get started, I have to note that debugging in the web interface is a pain, when compared to debugging with the classic interface.

In the documentation can be found the following: A common step when debugging code that includes linked temporary tables is dumping the contents of the temporary table to a file. This is used to investigate the values the system was working with at a certain point in the code. This usually follows the structure:

SELECT COLUMN1, COLUMN2... FROM LINKED_TABLE
TABS :FILENAME;

A common question when developing on Priority Web is how to access these files in a situation when there is no access to the server machine.

I won't quote the documentation further because I think that it gives a false and incomplete solution. I want to show a solution that I developed that creates debug filest then saves them as attachments to a specific customer (www) for viewing. This solution also allows the creation of several files that is useful when the procedure creating those files is run under the TTS. I'll display the complete subroutine first after which I will explain the various lines.

[1] SELECT SQL.TMPFILE INTO :INFILE FROM DUMMY;
[2] SELECT * FROM HTMLCOLORS TABS :INFILE;
[3] SELECT STRCAT(SYSPATH('MAIL', 1), '/TEST/$,',
DTOA (SQL.DATE, 'DD-MM-YY hh-mm'), '.txt') INTO :OUTFILE FROM
DUMMY;
[4] EXECUTE COPYFILE :INFILE, :OUTFILE;
[5] SELECT SQL.TMPFILE INTO :TEST_FILE FROM DUMMY;
[6] LINK GENERALLOAD RCF TO :TEST_FILE;
[7] GOTO 901 WHERE :RETVAL <= 0;
[8] INSERT INTO GENERALLOAD RCF (LINE, RECORDTYPE, TEXT6)
[9] VALUES (1, '1', 'www');
[10] INSERT INTO GENERALLOAD RCF (LINE, RECORDTYPE, TEXT7, TEXT3)
[11] VALUES (2, '2', :OUTFILE, 'Debug');
[12] EXECUTE INTERFACE 'TEXT_ADDEXT2CUST', SQL.TMPFILE, '-L',
:TEST_FILE;
[13] UNLINK AND REMOVE GENERALLOAD RCF;
[14] LABEL 901;

The online documentation says to create a temporary file and output the required data to this file. This is what happens in lines 1 and 2. Line 3 creates a string whose value will be ../../system/mail/test' + name of the procedure + date and time.txt. This line differs from the website and it's important to explain why: the website uses the NEWATTACH procedure to create a filename - from my tests, this file will be in an arbitrary subdirectory of ../system/mail, e.g. ../../system/mail/202402/0mjw3vv/name of file.txt. In retrospect, this doesn't matter too much for reasons that I will explain shortly. Line 4 copies the temporary file to the filename that was built in the previous line; this causes a physical file to be created whose name is stored in :OUTFILE. 

Lines 5-13 are concerned with creating a new instance of GENERALLOAD and populating it with the customer www and the file that was created in line 4. As the variable :OUTFILE is used, it doesn't really make any difference if the SYSPATH or NEWATTACH method is used; in the web interface, the user can't see the directory, so it doesn't matter if the file is in a specific directory or in an arbitrary one.

The interface TEXT_ADDEXT2CUST has two forms: CUSTOMERS, where CUSTNAME ('www') is stored in field TEXT6 and CUSTEXTFILE (in the singular!), where the name of the file is stored in TEXT7 and the description in TEXT3. When I was developing the subroutine, a strange error message about the interface appeared; it transpires that I automatically used the son form EXTFILES (in the plural) as the second form in the interface, and not CUSTEXTFILE.

Two final notes:

1. The attachments will be displayed in reverse order of addition, i.e. the first file will be the last to be created. This is because I couldn't be bothered to use the EXTFILENUM field in the interface. I think that it's better this way as one doesn't have to scroll through a list of files in order to find the newest.
2. Deleting a line in the attachments form will delete the physical file! Newer versions of Priority display a warning message that it is not necessarily clear. This does allow old and irrelevant files to be removed.

Fun and games with the :$.PAR parameter / 2

I proudly connect to the client's client's computer, update the IVSTORNO replacement procedure ... and watch it do nothing.

It took a while to realise that I work with the classic, Windows, interface where EXECUTE WINACTIV is fine, whereas the client works with the web interface that requires EXECUTE ACTIVATE. Once that little detail was fixed, the procedure worked flawlessly.

Web interface report bug

One of my clients exclusively uses the web interface of Priority; whilst this is the face of the future, it's also more expensive as every user needs a licence (the classic Windows interface allows concurrent licences). I find the user interface much more difficult to work with (there are some items that I use frequently in the classic interface that I have yet to find in the web interface) but this is no doubt a function of habit. Upkeep and maintenance is probably less with the web interface as one can have one's database(s) hosted on another company's computer, thus one does not have to purchase and maintain servers. This is good for new companies but less good for existing companies.

But I digress. Unfortunately it often happens that I develop a report that I know will work with the classic interface but causes the web interface to crash. I was able to identify one cause of this behaviour that I am going to describe here.

In a report, one can define that certain fields will appear in a header line. In the classic interface, one can define however many fields as necessary to appear in the header, and the web browser that displays the report will automatically split the fields over two lines if the total width of the header exceeds the width of the report. Thinking about this, this probably occurs in the Priority procedure that takes the data, reads the report interface and creates the HTML code.

This does not happen with the web interface! Should a report have a header that is too wide, the feared error message will appear. The solution of course is very simple: one has to manually move the surplus fields to a second header line instead of having this occur automatically. Naturally this is not documented: it was only by observing what happened to a report with many fields in its header did I understand what was triggering the bug.