Thinking out of the box

Recently a new BIN was installed in my company; this was supposed to solve some problems, but it also introduced new ones. The most egregious example that I could find is concerned with passing values from a procedural parameter to the procedure itself. If for example, I have a procedural parameter called PRT that is connected to the PART table, the code that the debugger develops for this when I run the procedure is near enough what one might expect:

INSERT INTO PART B SELECT * FROM PART A WHERE  A.PARTNAME 
LIKE '0957*';

Here the aliased table PART A comes from the procedural parameter. But if someone else runs the procedure, the following code is produced

INSERT INTO PART B SELECT * FROM PART A WHERE  A.PARTNAME 
='9057*' AND A.PARTNAME IN (SELECT PART.PARTNAME  FROM 
TEC_PART TEC_PART5 ?, PARTSPEC  , PARTDES  , PARTPRICE ?
(and another thirty lines of joins to other tables)

These extra joins cause the procedure to 'die' without completion. So how can this problem be overcome?

For this, a great deal of out of the box problem solving is required because the canonical solution has always worked. As it happens the internal SQL code is what gave me the clue to solving this challenge without the use of a procedural file parameter. I was working yesterday on a procedure that receives two procedure parameters based on PART where their type is LINE, not FILE, along with one 'regular' procedural parameter. Within the procedure, the internal part number is retrieved from the linked table with little difficulty; in the workaround, this procedural parameter is changed from type LINE to CHAR. I'll show below the original code and the workaround.

/* original version */
LINK PART TO :$.PRT;
ERRMSG 1 WHERE :RETVAL <= 0;
:OLDPART = 0;
SELECT PART, PARTNAME INTO :OLDPART, :OLDNAME
FROM PART
WHERE PART > 0;
UNLINK PART;
ERRMSG 2 WHERE :OLDPART = 0;
/* new version */
:OLDPART = 0;
SELECT PART, PARTNAME INTO :OLDPART, :OLDNAME
FROM PART
WHERE PARTNAME = :$.OLD;

But there is also a third procedural parameter linked to PART and this one is more complicated as it has to remain as a linked table and not a single part. There are several possible values that can be passed in the parameter: it can be empty, it can be *, it can be a specific part (e.g. 0926, if there is such a part) or a range (e.g. 0926*). How can this be handled?  The first two cases are relatively easy to handle after one makes the conceptual shift, but the third (or rather, the fourth) case is more difficult. My original code for this was as follows

GOTO 1 WHERE (:$.FAT = '') OR (:$.FAT = '*');
INSERT INTO PART
SELECT * FROM PART ORIG
WHERE ORIG.PARTNAME LIKE STRCAT (:$.FAT, '%');
LABEL 1;

In other words, don't link PART at all if :$.FAT is either empty or an asterisk. But this code is not legal syntax as LIKE cannot contain a variable. So what can be done? The solution is to replace LIKE with BETWEEN, where the lower limit will be the string passed in :$.FAT without the asterisk, and the upper limit will the the lower limit with ZZZ concatenated at the end. If :$.FAT is 0926*, then the lower limit will be 0926 and the upper limit 0926ZZZ - this covers all the parts in the same range as would 0926*.

GOTO 1 WHERE (:$.FAT = '') OR (:$.FAT = '*');
:N = STRINDEX (:$.FAT, '*', 1);
:A = SUBSTR (:$.FAT, 1, :N - 1);
:B = STRCAT (:A, 'ZZZZ');
SELECT SQL.TMPFILE INTO :FATHER FROM DUMMY;
LINK PART TO :FATHER;
ERRMSG 1 WHERE :RETVAL <= 0;
INSERT INTO PART
SELECT * FROM PART ORIG
WHERE ORIG.PARTNAME BETWEEN :A AND :B;
LABEL 1;

I thought at some stage that it would be better to start the entire block with the STRINDEX function; whilst this works when :$.FAT is empty (returning 0), a dump file was created when :$.FAT contained a single asterisk, so this condition is checked as a normal string condition. After this snippet executes, PART will either be unlinked and so containing all the parts, or it will be linked and will contain only the chosen parts. 

Ternary logic in procedures

Quite often I am faced with the problem of handling boolean fields in an SQL step; for example, if I want to write a query that has as one of its parameters the field ORDERITEMS.CLOSED, until now I have only been able to choose either lines where CLOSED = 'C' or ignore the field altogether. There is no way of choosing lines that are not closed. 

...
AND ORDERITEMS.CLOSED = (:$.FLG = 'Y' ? 'C' : ORDERITEMS.CLOSED)
...

The problem is that the parameter FLG can either be marked or unmarked, using the subform of the parameter to mark it of type Y, thus creating a checkbox. The equals sign that is displayed when running the procedure cannot be changed. So how can one handle the three different possibilites: closed, not closed, ignore?

The key to doing this is first to record three different but consecutive messages in the 'procedure messages' subform, eg 1 = 'only Y', 2 = 'only not Y', 3 = 'couldn't care'. Then a parameter has to be defined of type INT and in the parameter extension subform defined as type C, where the message numbers are from 1 to 3.

Then in the query one has to use syntax that is legal but unusual:

SELECT LINE
FROM ORDERITEMS
WHERE ((:$.FLG = 1 AND CLOSED = 'C') OR
(:$.FLG = 2 AND CLOSED <> 'C) OR
(:$.FLG = 3 AND CLOSED = CLOSED))
...

This passes the syntax check and even works! The third condition probably can be condensed to :$.FLG = 3.

[Edit: it has been pointed out to me that this is an alternative method to a CHOOSE step. I am aware of this, but there are two reasons why I developed this technique:

1. For its intrinsic value. I've never seen this before and I certainly haven't seen the SQL
2. In the procedure where this is used, there are two such variables as well as several other standard parameters. This would have meant three separate screens that the user would have to execute and I don't like to overwhelm the user.

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).

Injecting an English title for a report sent by TTS (2)

Several months ago, I described how an English title could be injected into a report that is sent via TTS. Whilst the code that I displayed there is fine, it transpires that the English text is too long for the report's title and so was being truncated. The solution for this would be to inject the title into the appropriate field in the subform REPTITLE. This makes the code both simpler (because my original solution is no longer needed) and more complicated. It now becomes

:EXE = 0; /* do this once at the beginning */
SELECT EXEC INTO :EXE
FROM EXEC
WHERE TITLE = '$'
AND TYPE = 'R';
...
GOTO 10 WHERE SQL.COUNTRY = 'ISR';
/* India */
:MSGNUM = 10;
:GROUP = 'INDIAGROUP';
GOTO 20;
LABEL 10;
/* Israel */
:MSGNUM = 0;
:GROUP = 'ISRAELGROUP';
LABEL 20;
GOSUB 900;
EXECUTE WINACTIV '$', SQL.TMPFILE, 'STACK', :$.STK, '-g', :GROUP;
GOTO 99 WHERE :MSGNUM = 0;
:MSGNUM = 0;
GOSUB 900;  /* Remove the injected title */
LABEL 99; /* End */
/***************************************************************/
SUB 900;
DELETE FROM REPTITLE 
WHERE EXEC = :EXE;
GOTO 901 WHERE :MSGNUM = 0;
:PAR1 = ENTMESSAGE ('$', 'P', 10);
INSERT INTO REPTITLE (EXEC, TITLE)
VALUES (:EXE, :PAR1);
LABEL 901;
RETURN;

Error messages from a procedure invoked by a form trigger are not displayed

I have a private POST-UPDATE trigger in form ORDERS that invokes a procedure whose task is to extract certain lines from the current order and insert them into a new order (this isn't particularly relevant). The procedure checks certain data and displays error messages if the checks fail. It transpires that these error messages are displayed when the procedure is run from a menu, but not when invoked from a form, which is when they are needed the most.

Below I describe a slightly complicated way to get around this problem. First I defined a new table TEST_ERRMSGS that has four fields: AKEY, USER, PROG and MESSAGE, where the first three fields create the composite primary key ('U' in Priority-speak). I can't be sure that someone else is going to run the same procedure, probably on a different order but who knows, so I need maximum 'separation'. AKEY will be the order number, USER is of course the user's number and PROG is the number of the procedure. Here is the relevant part of the form trigger pre-enhancement

SELECT SQL.TMPFILE INTO :ECORDERS FROM DUMMY;
LINK ORDERS OEC TO :ECORDERS;
GOTO 999 WHERE :RETVAL <= 0;
INSERT INTO ORDERS OEC
SELECT * FROM ORDERS ORIG
WHERE ORD = :$.ORD;
EXECUTE WINACTIV '-P', 'TEST_SEPARATE', 'ORDERS',
:ECORDERS;
UNLINK AND REMOVE ORDERS OEC;

Here are the lines that have to be added before the line 'EXECUTE WINACTIV'; one private integer field is utilised although this could be any field in the order as long as the procedure doesn't need it in the course of its normal execution. There's no need to worry about overwriting important data as it's a linked table that is being updated. Of course, it's best to use a private field. 

:TEST_PROG = 0;
SELECT EXEC INTO :TEST_PROG
FROM EXEC
WHERE TYPE = ‘P’
AND ENAME = 'TEST_SEPARATE'
;
SELECT SQL.TMPFILE INTO :ECORDERS FROM DUMMY;
LINK ORDERS OEC TO :ECORDERS;
GOTO 999 WHERE :RETVAL <= 0;
INSERT INTO ORDERS OEC
SELECT * FROM ORDERS ORIG
WHERE ORD = :$.ORD
;
UPDATE ORDERS OEC 
SET TEST_FIELD = -1 /* this signifies that the procedure is called from an order */
WHERE ORD = :$.ORD;

At the beginning of the procedure, the following query is executed to get the order number

LINK ORDERS TO :$.PAR;
ERRMSG 1 WHERE :RETVAL <= 0;
SELECT ORD, ORDNAME, CUST
INTO :OLDORD, :ONAME, ::CUST
FROM ORDERS WHERE ORD > 0;
UNLINK ORDERS;

To the SELECT list, I add the private field TEST_FIELD that is selected into the variable :FROMFORM. After this is the block

GOTO 10 WHERE :FROMFORM <> -1;
:TEST_PROG = 0;
SELECT EXEC INTO :TEST_PROG
FROM EXEC
WHERE TYPE = 'P'
AND ENAME = '$';
DELETE FROM TEST_ERRMSGS
WHERE USER = SQL.USER
AND AKEY = :ORD
AND PROG = :TEST_PROG;
LABEL 10;

In other words, if there is already an entry in the table for this user, order and procedure, clear it so that we can start afresh. Now the procedure runs, and every time that it detects an error, the rather cumbersome code that appears below has to be included. If before the code was

ERRMSG 62 FROM ORDERSA
WHERE ORD = :ORD
AND TEST_CONDITION = 'Y';

It now becomes

:ERR = 0;
SELECT 62 INTO :ERR
FROM ORDERSA
WHERE ORD = :OLDORD
AND TEST_CONDITION = 'Y';
GOTO 862 WHERE :ERR = 0;
GOSUB 9990;
ERRMSG 62;
LABEL 862;
...
SUB 9990;
GOTO 9991 WHERE :FROMFORM <> -1;
SELECT ENTMESSAGE ('$', 'P', :ERR) INTO :PAR1 FROM DUMMY;
INSERT INTO TEST_ERRMSGS (AKEY, USER, PROG, MESSAGE)
VALUES (:OLDORD, SQL.USER, :GLOB_PROG, :PAR1);
LABEL 9991;
RETURN;

Going back to the form trigger, after the line 'EXECUTE WINACTIV', the following has to be added

SELECT MESSAGE INTO :PAR1
FROM TEST_ERRMSGS
WHERE USER = SQL.USER
AND AKEY = :$.ORD
AND PROG = :TEST_PROG;
ERRMSG 990 WHERE :RETVAL = 1;

In other words, without digging into the code, the calling trigger has to signify to the procedure that it is being run from a trigger (TEST_FIELD = -1). The procedure then inserts a record into a special table if an error is detected, where the record includes the order number, the user, the procedure and the error message to be displayed. When the trigger resumes execution, it checks whether there is a suitable record and if so, displays the error message. This means that the record will remain in the table but this doesn't matter much for if the user runs the same procedure on the same order, the record will be deleted at the beginning of the procedure.

Injecting an English title for a report sent by TTS

I wrote¹ several months ago about the tedious process of adding English titles to reports and forms. That kind of work has settled down, but instead I've had to face some rather strange problems. The latest example is a report that is sent by email via TTS; the data is prepared by a procedure that sends the report with the EXECUTE WINACTIV command. The problem, as one might guess, is that when it is sent based on Indian data, the report is in Hebrew.

The immediate problem about the body of the report was fixed quite easily as the form for a TTS task has a 'language' field. When this was set to English, the body of the report was indeed sent in English (and left to right) but the title of the report stayed in Hebrew, despite my having defined a translation of the report title. When the report is run from the menu by a user, this English title appears.

I thought at first that there might be an undocumented flag for WINACTIV that would force an English environment; notionally such a flag would be -e, but this character is already used to signify that the report should be sent to an email address. I tried saving the output to a file (then send it on via MAILMSG) to see whether this would make any difference, but it didn't. There is no possibility of assigning a value to SQL.LANGUAGE; in comparison SQL.ENV holds the name of the current environment that can be changed by invoking the ENV function.

I'm not sure where the inspiration for my solution came from, but it occurred to me that all I needed to do was to update the title of the report in the EXEC table. So I added the following code towards the end of the procedure, after the data has been collected but before the report is sent.

GOTO 10 WHERE SQL.COUNTRY = 'ISR';
/* India */
:MSGNUM = 10;
:GROUP = 'INDIAGROUP';
GOTO 20;
LABEL 10;
/* Israel */
:MSGNUM = 20;
:GROUP = 'ISRAELGROUP';
LABEL 20;
GOSUB 900;
EXECUTE WINACTIV '$', SQL.TMPFILE, 'STACK', :$.STK, '-g', :GROUP;
GOTO 99 WHERE :MSGNUM = 20;
:MSGNUM = 20;
GOSUB 900;
LABEL 99; /* End */
/***********************************************/
SUB 900;
SELECT ENTMESSAGE ('$', 'P', :MSGNUM) INTO :PAR1 FROM DUMMY;
UPDATE EXEC
SET TITLE = :PAR1
WHERE ENAME = '$'
AND TYPE = 'R';
RETURN

Message 10 holds the English title whereas message 20 holds the Hebrew title. There's no point in resetting the title after the report has been created if it has already been set to Hebrew. This hack only works because the report is being sent by a spawned process; if it were run from the menu, the report is effectively already loaded into memory when the procedure starts and so run-time changes do not take effect (unless one is using the documented methods which aren't applicable here).

Internal links
[1] 119

The LOADFNC external program

This program takes the lines that are in the Interim Table-Journal Entries form and transforms them into journal entries. I needed to deal with this program as the next step in the long journey that began with the recursive FILELIST¹. There's one subtle pitfall with this program which I'll describe.

My procedure takes the results from the FILELIST program, connects to a given environment and loads a file found by FILEIST into the interim table. The sequence of commands is as follows

:TOFILE = STRCAT (SYSPATH ('LOAD', 0), :COMPANY, '\loadfnc.txt');
SELECT MESSAGE INTO :FN 
FROM STACK_ERR
WHERE LINE > 0 
AND STRPIECE (MESSAGE, '/', 1, 1) = :COMPANY
AND INTDATA2 > 30;   /* size of file - exclude dummy.txt */
:FNAME = STRCAT (:CHKDIR, '/', :FN);
EXECUTE MOVEFILE :FNAME, :TOFILE;
EXECUTE DBLOAD '-L', 'loadfnc.txt', '-E', 'loadfnc.err', SQL.TMPFILE;

The above is actually run in the framework of a cursor, but for simplicity, I am assuming that there is a single file. TOFILE is the name of the destination file; its name has to be the same as the name of the interface. The documentation says that such a file can either be in the system/load directory or in a subdirectory of system/load according to company/environment name. Once the file is found, FNAME restores the directory structure of the filename that was removed initially prior to the FILELIST command, and then FNAME is copied to TOFILE. Once copied, this file can then be uploaded via the DBLOAD program.

This first half of the program went well but the second half had me stumped. One can find a menu option that runs LOADFNC and there is the trigger LoadAccAE2 of the 'func' table that runs LOADFNC. There are three parameters that appear to be a message, the environment's name and a linked table of users. The last parameter is probably the easiest to create. The command in LoadAccAE2 is EXECUTE LOADFNC SQL.TMPFILE, :DNAME, :USERLNK. The first and third parameters were easy to figure out but the second parameter seemed problematic. DNAME is one of the fields in the ENVIRONMENT table so I thought that this was the name of the current environment. There was some code in the menu option LOADFNC2 which I didn't initially understand that would have helped me.

In order to figure out what the parameters to LOADFNC needed to be, I manually loaded some data into the interim table then ran the menu option. I received an error message saying that 'test' (the value that I had stored in the the interim table's 'source program' field) had not been defined in the Definition of Load Parameters form. Once I added 'test' to this table, I was able to run LOADFNC successfully. At this time, the code in the menu option became clear: DNAME is not the name of the environment, but the value stored in the 'source program' field. Not only that: this value has to be the same in all the lines being loaded. This was the pitfall of which I was not aware.

My test data had created a temporary journal entry that would have to have its status changed to 'final'. In order to find the entry, I would have to read the value of a certain field in the interim table to find this value in the journal entry, but successful loading of lines into journal entries removes the lines from the interim table. I thought at first that I would have to copy the interim table before loading then iterate over it, finding the required values, but I discovered that one of the flags in the Definition of Load Parameters form causes the newly entered journal entry to be finalised automatically. So there was no need to copy the interim table.

I have to figure out what to do if a file cannot be loaded successfully into the interim table or data cannot be turned into journal entries. Supposedly DBLOAD can create a file with data that did not get loaded, but if this program is running in a loop, that error file is going to be overwritten. Similarly, if LOADFNC can't turn data into a journal entry, the data will be left in the interim table, but this again will be overwritten with the data from the next file. I could attach the contents of the table to an email, but the recipient would have to enter the data manually into the interim table.

Internal links
[1] 122

Continuing the recursive FILELIST procedure

Four months ago, I wrote¹ about the recursive FILELIST and the fact that two essential flags were not documented. Over the past few days, I've been working with the results of that command; I had to copy files to a location in system/mail, then attach them to financial journal entries, then delete the files. Nothing worked ... or rather, everything that was not connected to copying and deleting the files worked properly, but the files were not being accessed.

Eventually there was no option left but to call for help from Priority Software, so today we had a grand debugging session. The programmer from PS at first was at a loss as to why the commands weren't workng. The reason only became clear when he used a program ('filezila' ?) to access the system/sync directory from the external side; it turns out that the company that was placing files in this directory had created a subdirectory system/sync in the sync directory, thus explaining why we couldn't access any of the files. 

To make this clear: the output from EXECUTE FILELIST looked like this

system/sync/dataplus/a191016/
system/sync/dataplus/a191016/dummy.txt
system/sync/dataplus/a191016/5031266_25034.pdf
...

I had assumed that the program was showing the complete path, but in reality the filename with path was ../../system/sync/system/sync/dataplus/a191016/5031266_25034.pdf. Once this had been taken into account on the internal side, my code worked perfectly.

Here are a few things that I picked up during the work session. At one stage, the command EXECUTE FILELIST was executed without any parameters (I think); this caused the program to show its help - the various flags that can be passed. I discovered that the mysterious -d parameter means "put directory names in result". Secondly, there are two integer fields in STACK_ERR (the table that holds the directory results) that can be useful: INTDATA1 holds the creation date of each file, and INTDATA2 holds the file size. I didn't need to use the creation date but this might have been useful. INTDATA2 was definitely useful as I could exclude accessing 'files' that had a size less than 30 bytes - the first two lines in the example that I quoted above have such file sizes. Using this field makes the cursor simpler. Finally, one doesn't have to prefix the file path with ../../system/sync: it's cleaner to do this with SYSPATH ('SYNC', 0) - actually I had done this at the beginning of the procedure in order to get the files.

So part of the final code is as follows

SELECT SQL.TMPFILE INTO :ST6 FROM DUMMY;
:CHKDIR = SYSPATH ('SYNC', 0);
EXECUTE FILELIST :CHKDIR, :ST6, '-R', '-d', SQL.TMPFILE;
LINK STACK_ERR TO :ST6;
/* First get the directories */
DECLARE C1 CURSOR FOR
SELECT DISTINCT STRPIECE (MESSAGE, '/', 4, 1)
FROM STACK_ERR
WHERE LINE > 0
AND STRPIECE (MESSAGE, '/', 4, 1) <> ''
AND EXISTS (SELECT 1 FROM ENVIRONMENT
WHERE DNAME = STRPIECE (MESSAGE, '/', 4, 1));
OPEN C1;
GOTO 300 WHERE :RETVAL <= 0;
LABEL 100;
FETCH C1 INTO :COMPANY;
GOTO 200 WHERE :RETVAL <= 0;
ENV :COMPANY;  /* switch to current company */
LINK GENERALLOAD TO :$.GEN;
:LINE = 0;
/* Get files */
DECLARE C2 CURSOR FOR
SELECT MESSAGE, STRPIECE (MESSAGE, '/', 5, 1)
FROM STACK_ERR
WHERE LINE > 0
AND STRPIECE (MESSAGE, '/', 4, 1) = :COMPANY
AND INTDATA2 > 30;   /* size of file - exclude dummy.txt */
OPEN C2;
LOOP 100 WHERE :RETVAL <= 0;
LABEL 110;
FETCH C2 INTO :BIGFNAME, :GNAME;
GOTO 150 WHERE :RETVAL <= 0;
...
/* copy file */
:HNAME = STRCAT (SYSPATH ('SYNC', 1), :BIGFNAME);
SELECT NEWATTACH (:GNAME) INTO :FOUT FROM DUMMY;
EXECUTE MOVEFILE :HNAME, :FOUT;
...

Apparently no one uses SFTP in order to place files from an external source into the system/sync directory, so in a sense, I and the implementor who hired my services, are pioneers with this file handling code.

Running COSTING automatically - continued

Today was the first day that COSTING ran automatically using my procedure to execute it, and it did so perfectly. After it finished, I thought of a new wrinkle. I had originally added my procedure to the TTS and marked it inactive; after running the accumulator last night, I marked it as active. It was my intention to mark it as inactive again until the next accumulator.

Then I thought: why not check whether the COSTFLAG is set for the most recent accumulator? If it is set, then don't run COSTING. As this external program is in a separate procedural step, I could issue an ERRMSG call that would halt the procedure, but I thought it better to set a flag that sets a :$.GO variable - and a GOTO step would skip over the COSTING stage if necessary. 

So now the procedure is in the TTS and will run every Friday, but most weeks it will do nothing.

In stage 10, SQLI:
/* My stuff */
:CURDATE = 01/01/88;
SELECT MAX (CURDATE) INTO :CURDATE FROM ACCDATES;
:FLAG = '\0';
SELECT COSTFLAG INTO :FLAG
FROM ACCDATES
WHERE CURDATE = :CURDATE;
GOTO 99 WHERE :FLAG = 'Y';
LINK ACCDATES TO :$.DAT;
...
LABEL 99;
:$.GO = (:FLAG = 'Y' ? 50 : 30);
/* End of stage 10 */

Stage/parameter	Name/type
10	SQLI
DAT	LINE
GO	INT
20	GOTO
GO	INT
30	COSTING
ARG	INT
MSG	ASCII
DAT	LINE
40	SQLI
50	END

Remember that ARG must be 1. Stage 40 sends me an email

Running COSTING automatically

From a financial/inventory point of view, the external program COSTING is one of the most important programs under the Priority umbrella. For those that don't know, this program calculates the value of every part stored in a given 'accumulator', that in itself stores the inventory of each part in every warehouse at a given date (the program can also calculate for previous accumulators). Unlike the daily costing program, this program saves its data forever and so is extremely useful when wanting to know the value of inventory at a given date (normally the last day of a month). 

There is a parameter (ARG) passed to COSTING that determines whether it will be a daily run (deleting previous values) or a monthly run (saving values). In the first case, ARG will equal 4, whereas in the second case, ARG will equal 1.

Until not so very long ago, one could schedule COSTING and it would run automatically, using the most current accumulator. Unfortunately, this behaviour changed a few versions ago, when a scheduled run would fail because it didn't have a date for the accumulator. As this program runs for 14-16 hours on my server, this meant that I would have to get up at 4:15 on a Friday morning, stumble to the computer, connect to the server and start the program.

Eventually I got fed up with this and tried to determine what would be needed to run COSTING automatically on the last accumulator only. Basically a two step procedure is required, but I added a third step to send me email that the procedure has completed. This code does indeed run COSTING automatically, creating a monthly costing.

Stage 10 - SQLI                                                                                                                
/* TEST_COSTINGACC - No'am, 25/09/25                                
A cut-down version of COSTINGACC that calculates for the last tzovar
only - automatically.
Parameters: DAT, type LINE
*/                                                                  
:LASTDATE = 0;                                                      
#include func/FashionCosting                                        
SELECT CURDATE INTO :LASTDATE FROM ACCDATES ORIG                    
WHERE COSTFLAG <> 'Y' AND NOT EXISTS                                
(SELECT 'X' FROM ACCDATES ORIG2                                     
WHERE ORIG2.COSTFLAG = 'Y' AND ORIG2.CURDATE > ORIG.CURDATE)        
ORDER BY CURDATE DESC;                                              
/* My stuff */                                                     
:CURDATE = 01/01/88;                                                
SELECT MAX (CURDATE) INTO :CURDATE FROM ACCDATES;                   
LINK ACCDATES TO :$.DAT;                                            
GENMSG 1001 WHERE :RETVAL <= 0;                                     
INSERT INTO ACCDATES                                                
SELECT * FROM ACCDATES ORIG                                         
WHERE CURDATE = :CURDATE;                                           
UNLINK ACCDATES;                                                    
/* Input stage */                                                   
SELECT VALUE INTO :STARTDATE FROM LASTS WHERE NAME='COSTSTARTDATE'; 
:CURDATE = :FIRSTDATE = 0;                                          
:COUNT = 0;                                                         
LINK ACCDATES TO :$.DAT;                                            
SELECT MIN(CURDATE), COUNT(*) INTO :CURDATE, :COUNT                 
FROM ACCDATES WHERE CURDATE > 0;                                    
UNLINK ACCDATES;                                                    
SELECT CURDATE INTO :FIRSTDATE FROM ACCDATES WHERE CURDATE > 0      
AND COSTFLAG = 'Y' ORDER BY CURDATE;                                
SELECT DTOA(0+:STARTDATE, 'XX/XX/XX') INTO :PAR1 FROM DUMMY;        
ERRMSG 1 WHERE :FIRSTDATE = 0 AND 0+:STARTDATE <> :CURDATE          
AND :STARTDATE <> 0;                                                
:ENAME = 'COSTINGACC'; :DATE = 0 + :CURDATE;                        
#include func/CostingLog    
-----------------------------------------------------------------
STAGE 20: COSTING
Parameters: ARG, type INT, value 1
MSG, type ASCII
DAT, type LINE
/* The parameters have to be in this order */
-----------------------------------------------------------------
STAGE 30: SQLI
#include func/CostingMpart
:PAR1 = '$';
MAILMSG 40 TO EMAIL 'tabula@gmail.com';

Recursive FILELIST

The SDK documents under heading Browsing the Contents of a Folder (page 233 in my copy of the V23 document) how to obtain a list of files in a folder. 

:DIR = '../../tmpDir'; 
SELECT SQL.TMPFILE INTO :ST6 FROM DUMMY; 
SELECT SQL.TMPFILE INTO :MSG FROM DUMMY; 
EXECUTE FILELIST :DIR,:ST6,:MSG; 
/* In the linked file of the STACK6 table, you will find all files
and folders under the input directory :DIR. */ 
LINK STACK6 TO :ST6; 
GOTO 99 WHERE :RETVAL <= 0; 
DECLARE NEWFILES CURSOR FOR 
SELECT TOLOWER(NAME) FROM STACK6 
WHERE TOLOWER(NAME) LIKE ' loadorder*'; 
OPEN NEWFILES;
...

I've written code based on this to poll a specific directory; if a file is found, then the procedure performs some action based on this file (e.g. reads the file and uses it as input for an interface). At the end, the procedure deletes the file so that it won't be found again.

This is all well and good when Priority is hosted on a company server, but is problematic when the web interface is used and Priority is hosted 'in the cloud'. The new online SDK discusses this scenario and states that The system/sync folder is a special folder available in Priority installations on the public cloud. It provides a location, accessible by SFTP, where users can upload files from an external source. The folder behaves a bit differently than regular Priority folders. I am working with a client for whom some external company is uploading files to a subfolder of system/sync; the client wants that these files (or rather, a reference to these files) be stored as an attachment to a financial journal entry. I tried using the 'standard' FILELIST code as written above but this was not bringing me any joy.

After approaching Priority Software, an undocumented feature of FILELIST was revealed: in order to traverse system/sync, one has to perform a recursive search by means of the flag '-R'. There is also another flag whose meaning escapes me at the moment, '-d'. Most importantly, instead of linking STACK6 to the results, one links STACK_ERR. It's not clear to me why this change is required: STACK6 has the fields NUM (equivalent to STACK_ERR.LINE), TYPE (equivalent to CHARDATA) and NAME (equivalent to MESSAGE), where the length of NAME is 100 characters. But the proof is in the pudding, and a recursive search does not work with STACK6.

Here is the required code when using system/sync as written for WINDBI as a test:

SELECT SQL.TMPFILE INTO :ST6 FROM DUMMY;
:CHKDIR = SYSPATH ('SYNC', 0);
EXECUTE FILELIST :CHKDIR, :ST6, '-R', '-d', SQL.TMPFILE;
LINK STACK_ERR TO :ST6;
SELECT LINE, CHARDATA, MESSAGE FROM STACK_ERR FORMAT;
UNLINK STACK_ERR;

This code does provide the names of files in the target directory.

While writing these words, I've had a problematic insight: if a procedure traverses the given folder and find files, it will attach them to journal entries. The next time the procedure runs, the same files will be found - it seems possible that one can attach the same file twice to some entity! The keys of the EXTFILE table are IV, TYPE and EXTFILENUM, and a quick test shows that indeed one can attach the same file more than once to the same entity! Obviously I will have to implement some form of check to prevent this; as opposed to the polling code that deletes the file after handling it, here the files have to remain 'forever'. No date information seems to be passed so one can't use this as a filter.

Converting procedures and reports to English

I am in the middle of converting many procedures and reports to English for a client who is starting operations in India. This process is fairly straight-forward and boring, but there are some problems that have to be overcome.

The process can be divided into two: translating labels (of the procedure/report itself, procedural parameters, field titles in reports) and handling fields that have an English value as well as a Hebrew value. Translating labels is straight-forward; the only point worth noting is that as labels are (generally) limited to 20 characters, one should enter the translated label for language 3 (American English) first then copy the label to language 2 (British English). It seems that the check for length occurs only for language 3.

One place that requires slightly special handling is reports that have a different title to the given title. Normally, the translated title of the report goes in the 'translation of entity title' sub-form, but a specific title that can be longer goes in the 'output title' sub-form. The first would be displayed in a menu whereas the second is only for output. This sub-form has its own sub-form, 'Translation of Output Title'.

Handling fields with a separate English value is more involved. There is only one table, DOCSTATUSES, that has both STATDES and ESTATDES fields, and choosing which to display is very simple:

(SQL.LANGUAGE = 1 ? DOCSTATUSES.STATDES : DOCSTATUSES.ESTATDES)

But most other tables have the English value in a separate table, eg. CUSTOMERS.CUSTDES and CUSTOMERSA.ECUSTDES. This is slightly more involved, as one has to write both the conditional statement as well as adding a left join between CUSTOMERS and CUSTOMERSA.

The real problems start when this field is a parameter: the client has a propensity for including the part status in reports, where it is frequently a parameter. In this case, one has to add a CHOOSE-FIELD trigger for the field that in itself is very interesting as it displays how Priority manages a UNION ALL.

SELECT PARTSTATS.STATDES, ''
FROM PARTSTATS
WHERE PARTSTAT <> 0
AND INACTIVEFLAG <> 'Y'
AND SQL.LANGUAGE = 1;
SELECT /* AND STOP */ DOCSTATUSES.ESTATDES, ''
FROM DOCSTATUSES
WHERE DOCSTATUS <> 0 
AND TYPE = '4'
AND SQL.LANGUAGE > 1;

The (currently) insolvable problem is with part status being a parameter to an INPUT or SQLI stage. One can give the parameter an English title and define the above CHOOSE-FIELD trigger, but into which table is the value stored, PARTSTATS or DOCSTATUSES? One, somewhat clumsy, solution is to use different stages for the different languages, viz.

[STAGE 10] :$.GO = (SQL.LANGUAGE = 1 ? 30 : 50;
[STAGE 20]: GOTO; {$.GO}
[STAGE 30]; /* Hebrew */
LINK PARTSTATS TO :$.STA;  /* parameter */
....
[STAGE 40] GOTO; {60}
[STAGE 50] /* English */
LINK DOCSTATUSES TO :$.STD; /* parameter */
...
[STAGE 60] Report

For a moment, I thought that I could write a CHOOSE-FIELD trigger similar to the above for the procedural parameter that would choose either PARTSTATS.PARTSTAT or DOCSTATUSES.DOCSTAT, but there are two problems with this: (a) the parameter has to a character value, not numerical (and using ITOA doesn't solve the problem; (b) the form that Priority uses from which a value will be chosen is dependent on the table linked to the parameter that is in this case PARTSTATS. So it looks like I'm stuck with the GOTO solution. 

One possible improvement to this would be in stage 50 - instead of duplicating whatever is in stage 30 but using DOCSTATUSES, the appropriate values could be entered into a linked instance of PARTSTATS. Similarly stage 30 only gets values for PARTSTATS. Then there need be only one 'real' SQLI stage, 60, that does whatever is necessary for the report.

[Update] The GOTO solution won't work either. Stage 50 has the linked file DOCSTATUSES, and so the form that will be displayed or from which values will be extracted is also DOCSTATUSES. Any attempt to run this form results in the error message 'This form is reserved for internal use by the system'. To get around this problem, I defined a new form that is based on DOCSTATUSES, shows only ESTATDES and SORT and is defined as Q. In the 'continuation' sub-form of the procedural parameter, I defined the target form to be my private form.

This works! But it's still clumsy.

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.

Writing multi-environment procedures

I have been tasked a few times to write a procedure that iterates over all the active environments and saves data in a special table that is going to be accessed by an API. There are some special gotchas that need to be overcome; the following will not work

DELETE FROM MYTABLE;
SELECT SQL.ENV INTO :HOME FROM DUMMY;
:LINE = 0;
DECLARE C1 CURSOR FOR
SELECT DNAME
FROM ENVIRONMENTA
WHERE ACTIVE = 'Y'
AND DNAME <> '';
OPEN C1;
GOTO 300 WHERE :RETVAL <= 0;
LABEL 100;
FETCH C1 INTO :NAME;
GOTO 200 WHERE :RETVAL <= 0;
ENV :NAME;
INSERT INTO MYTABLE (.................)
;
LOOP 100;
LABEL 200;
CLOSE C1;
LABEL 300;
ENV :HOME;

Presumably there is a form based on table MYTABLE; initially all the data is wiped, then the procedure iterates through the environments and saves whatever data need to be saved. At the end, the procedure returns to the initial environment (:HOME) and presumably the data is displayed in the appropriate screen. Unfortunately, as I noted before the code, this doesn't work: the MYTABLE table will have different instances in each environment, or in other words, MYTABLE in environment A is not the same as MYTABLE in environment B. The following will work

SELECT SQL.TMPFILE INTO :ROL FROM DUMMY;
LINK MYTABLE TO :ROL;
SELECT SQL.ENV INTO :HOME FROM DUMMY;
...
LABEL 300;
ENV :HOME;
DELETE FROM MYTABLE ORIG;
INSERT INTO MYTABLE ORIG
SELECT * FROM MYTABLE; /* the linked table */
UNLINK AND REMOVE MYTABLE;

Followup procedures

A few times I have had the need to perform some action after closing an invoice (sometimes customer invoices, sometimes supplier invoices); this has sometimes been updating an invoice that was opened in another company as a customer invoice and transferred to the current company as a supplier invoice. One quickly comes to the conclusion that this is not possible to do via a POST-UPDATE trigger on the invoice as the closure is performed by a separate procedure and the actual form does not create any event that can be handled.

The gurus at Priority Software were aware of this problem and so added a solution that unfortunately is barely known and certainly not documented. If one goes to the Financials > Maintenance of Financials > Basic Data > Financial Attributes > Financial Documents menu option (form name IVTYPES), two columns can be seen: Initial Procedure and Follow-up Procedure. The help text for the followup procedure appears below.

But of course, there is no documentation that might explain how such a procedure can be defined. Obviously an invoice number has to be passed in a linked file, but what is the parameter name for that file? Is it :$.IV - the CLOSEYIV procedure that closes a supplier invoice has the parameter defined as :$.IV - or is it :$.PAR? 

There's only one way to find out and that's by trial and error. It turns out that the parameter should be called :$.PAR. Further testing showed that it's best to extract the IV field from the linked table after which the linked table should be closed, and any futher access be to the unlinked INVOICES table. This is probably because the linked table contains stale data, primarily the new number of the invoice that it receives after having been closed. Following is a very simple sample procedure that writes the new invoice number to a private table, simply to check that the value is being obtained.

LINK INVOICES TO :$.PAR;
:IV = 0;
SELECT IV INTO :IV
FROM INVOICES
WHERE IV > 0;
UNLINK INVOICES;
SELECT IVNUM INTO :PAR2
FROM INVOICES
WHERE IV = :IV;
UPDATE TEST_CONST SET
VALUE = :IV,
CHARVALUE = :PAR2
WHERE NAME = 'AB';

A problem with this kind of procedure is that debugging by means of inserting WRNMSGs throughout the procedure doesn't work, or more accurately, the warning messages don't get displayed. That's the reason that I had to update fields in a table.

VERY IMPORTANT: the followup procedure has to appear on a menu such that the person who closes the invoice will have permission to run the procedure.

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;

Is this the lamest code I've ever seen?

In the course of debugging a procedure that someone else had written years ago for a customer, I came across the following code:

DECLARE UPD_DATA9 CURSOR FOR                                        
SELECT DTOA (:$.DAT, 'DD/MM/YY') FROM DUMMY;                         
OPEN UPD_DATA9;                                                     
LABEL 10;                                                           
FETCH UPD_DATA9  INTO :IVDATE;                                      
GOTO 100 WHERE :RETVAL <= 0;                                        
UPDATE TEST_INVLOADDIV                                               
SET TEST_IVDATE = ATOD (:IVDATE, 'DD/MM/YY');                         
LOOP 10;                                                            
LABEL 100;                                                          
CLOSE UPD_DATA9;  

This code simply updates the field 'TEST_IVDATE' in all the rows of the table 'TEST_INVLOADDIV' with the date :$.DAT. This must be the lamest code that I've ever seen as there are so many stylistic errors. That is not to say that this code won't achieve what it's supposed to but it is so wrong!

Where should I start? The global parameter :$.DAT is guaranteed to be a date so there's no need to turn it into a string in the second line, then turn it back into a date in the eighth line. There is absolutely no reason to use a cursor that iterates over ... a global parameter! Totally pointless. The above code was written as a separate SQLI stage in a procedure; there's no real reason why it couldn't have been included in another stage with more (bad) code. The above code can be written succinctly as

UPDATE TEST_INVLOADDIV SET TEST_IVDATE = :$.DAT;

Of course, if the programmer were being paid by lines written, then the original version is much better, having 11 lines. My streamlined version wouldn't earn the programmer very much, having only one line. But if the programmer is being paid by her level of sophistication ....

More fixing a complicated problem with BOM revisions and 'info only' parts

The code given in the previous blog was almost correct, but subtly wrong. I had ignored the fact that each son part would appear three times, and that the first instance would set SONACT to be -1 thus causing the son not to appear in the report, even though there would be a correct instance later on. A more correct version of this code is

 SUB 820;
 :OK = 0; /* the query below may fail */
 :RVDATE = 01/01/88;
 SELECT MAX (ORIG.RVTILLDATE) INTO :RVDATE
 FROM PARTARC ORIG
 WHERE ORIG.PART = :PARENT
 AND ORIG.SON = :SON
 AND ORIG.INFOONLY <> 'Y';
 :OK = (:RVDATE = 01/01/50 ? 1 : 0);
 GOTO 821 WHERE :OK = 1;
 UPDATE PARTARC /* effectively remove the part from the tree */
 SET SONACT = -1
 WHERE SON = :SON;
 LABEL 821;
 RETURN;

Saving the maximum value of RVTILLDATE into a variable was not in my original code; this was intended to help with running the procedure with the debugger. But for some reason the 'wonderful' web interface didn't work so I didn't get an automatic debug output.

There was a strange phenomenon whose source I failed to track down, even after wasting an hour on it. The part that I was using for testing should have four active sons, but for some reason five were being displayed. That fifth part had been marked 'for info only' in all the versions so the 'select max' query above should have failed. But somehow this fifth part never even got to the subroutine, a failure that I could not track. Eventually I added a cursor that is called immediately after the SONRAW procedure.

 DECLARE C840 CURSOR FOR
 SELECT SON, SONPARTREV
 FROM PARTARC
 WHERE SON > 0;
 OPEN C840;
 GOTO 843 WHERE :RETVAL <= 0;
 LABEL 841;
 FETCH C840 INTO :SON, :PARENT;
 GOTO 842 WHERE :RETVAL <= 0;
 :A = :B = 0;
 SELECT COUNT (*), SUM ((INFOONLY = 'Y' ? 1 : 0)) INTO :A, :B
 FROM PARTARC PA
 WHERE PART = :PARENT
 AND SON = :SON;
 LOOP 841 WHERE :A <> :B;
 UPDATE PARTARC /* this is the linked table */
 SET SONACT = -1
 WHERE SON = :SON;
 LOOP 841;
 LABEL 842;
 CLOSE C840;
 LABEL 843;

This cursor got rid of the spurious part and probably helped the rest of the procedure run a bit faster as there would have been fewer parts to check in the main cursor.

Working with BOM revisions is very complicated!

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.