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.

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

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;

Solving two conradictory requirements

In the past, there have been two outside customers that were granted permission to connect to our database, but only to see their data. In order to satisfy this requirement, I turned on the 'Authorisation for Sales Reps' option and gave them the authorisation to see anything with their agent number. In order to compensate for this, I marked for all our regular users the 'All Sales Reps Auth?' flag in the Details of Current Company form. So far so good.

I was given a thorny problem to solve a few weeks ago: someone who works with our company had full access to the database, but he has been 'demoted' and now works only as an agent dealing with after sales orders (e.g. a customer wants an extra table or similar). For this capability, he should have the Authorisation for Sales Reps' option marked for his agent number and the 'All Sales Reps Auth?' flag unmarked. But he also has to see orders of other agents so that he can deal with their installation when required.

On the face of it, then, this person has to be able to both see only his orders and to see all orders: two contradictory requirements. For some time (a week), I deliberated how I could achieve this; I wasn't sure that it was a problem that could even be solved. Then I had a brainwave (in the shower, of course, where all good ideas come): I could write a procedure that marked the 'All Sales Reps Auth?' flag via an interface, then all the required orders could be saved in a temporary table, then the permission would be revoked. This sort of worked, but when I tried it under the person's username, I got no data. I realised that this was that by the time the user came to look at the report containing all the required orders, he was no longer be able to see them as the permission to do so had been removed.

The solution then was to divide this procedure into three stages: the first would give the permission, the second would show the report (i.e. the orders) and the third would retract the permission. Despite trying very hard, I couldn't get the third part to work which was very frustrating. I had also missed the fact that this person could do whatever he wanted to do with these orders when displayed, which was not part of the mission requirement.

A day later, the perfect solution popped into my mind. Instead of being fixated on a procedure and a linked report, I should display the data in a form that would be based on the ORDERS table, but allowing write access only to the fields that this person was allowed to change. Also, many fields that appear in the ORDERS form wouldn't have to be displayed thus greatly simplifying the form's logic. The magic in this form comes from something that is documented but that most people would never need. On page 80 of the SDK for version 23.0 appears the following

PRE-FORM triggers perform operations before the form is opened. This applies
to all root forms, as well as sub-level forms for which the Automatic Display
column of the Sub-Level Forms form (a sub-level of the Form Generator) is
blank. This type of trigger may be used, for example:
 to reset the value of a user-defined variable
 to generate a warning or error message concerning retrieved data
 to retrieve and display all records when the user opens the form —
:KEYSTROKES = ‘*{Exit}’;
 to refresh all retrieved records in a form following a Direct Activation —
:ACTIVATEQUERY = 1;
 to deactivate data privileges in a form — :$.NOCLMNPRIV.T = 1;
 to deactivate data privileges for a specific table in a form: in a new PRE-FORM 
trigger for the form in question, define the :$.NOTBLPRIV.T variable
with the name of the desired table; if the table you want to exclude has a
join ID, this should also be specified

In other words, if I include in the pre-form trigger the command $.NOTBLPRIV.T = 'AGENTS' then the user would not be limited to seeing only his orders but could see everyone's (where a different field contained his order order). This didn't work, but swapping this command with :$.NOCLMNPRIV.T = 1 did.

So I managed to solve two conradictory requirements.

Cursors in combined form triggers

Over the past few months, I sometimes recieved an error message about a cursor being declared twice in TRANSORDER_E/TEST-POST-INS-UPD. This trigger is activated after entering a record, either inserting it or updating it. I would look at the code and not see any problem. Last week I was working with someone on a private form where a trigger, again combined, wasn't doing what it was supposed to do (it did nothing).

A few days ago I closely read the new SDK 25.1 and came across this statement at the beginning where changes were listed: Added a warning that cursors cannot be used in combined (e.g. POST-UPD-INS) form triggers. This explained concisely why the private from trigger wasn't working, and why I received once again the error message about a cursor being declared twice in a trigger.

The answer is to create separate post-insert and post-update triggers. In the private form, there was a slight difference required between the two triggers, but the triggers for TRANSORDER_E will be the same. I don't know whether extracting the cursor code to a buffer will solve the problem so I'll probably just repeat the code.

Now that I think of it, I do have a way of testing whether I can extract the cursor code to a buffer: we have a test server running Priority 25.1, so I can change the code there and see what happens without disturbing anybody.

Multi-company forms

Yesterday I was asked to create a multi-company form. I know how to create a multi-company report but I had yet to create a form like this. Here are the conditions for a multi-company report:

• A displayed column, with a Column Name of TITLE and a Table Name of
ENVIRONMENT.
• A hidden column, with a Column Name of DNAME and a Table Name of
ENVIRONMENT. Its Expression/Condition should be: = SQL.ENV

Naively, I added these fields to the form. As it happens, my customer wanted the company name to appear anyway, so ENVIRONMENT.TITLE was necessary. When I opened the form, there was massive duplication of data (a cartesian join); basically there were no conditions on the Environment table which is why everything appeared several times.

I then turned to the SDK; as it happens, I have a new copy of the SDK for version 25.1 that documents many things that previously were not documented, but unfortunately there is very little about multi-company forms. There is a section heading that reads simply To prevent users from defining a given form as a multi-company form, specify x in the Oneto-many column, but it doesn't mention exactly how to create one as opposed to preventing one. There are a few more mentions about multi-company forms but these are about variables and not relevant.

That one sentence did give me an idea, though. I went to the one-to-many column in the form header and saw that there was an option 'm' that defines a multi-company form. Choosing that option gave me the message 'There must be a field called ZOOMDNAME, a character string of length 8 characters. I guessed that this should replace the field with the column name DNAME (even though the field name is still DNAME as this is the name of the field within the ENVIRONMENT table), and that the TITLE column should be replaced by ZOOMDTITLE. 

Lo and behold, the cartesian join disappeared and the correct data appeared. So this functionality is still undocumented. There are at least 20 standard forms with the 'm' flag (the actual number depends on the version) so one can learn from these.

WINHTML flags

WINHTML is the procedure to call when one wants to print/save/view an HTML document. There are two flavours to this procedure: the earlier flavour receives a linked table of records and the later flavour that allows each record to be printed separately.

There is an error in the documentation for the later flavour that I will show here. The syntax is

EXECUTE WINHTML '-d', 'document_name', 'table', 
'linked_file', '-v', 'record_id', ['-trc', debug_file,] ['-s',] 
['-e',]  ['-edoc' | '-signpdf',] ['-format', format_num,] 
['-lang', lang_num,] ['-AMAIL',]
['-o' |'-pdf' | '-wo' | '-wpdf',] ['output_file',]

Here is an example using this syntax. The output is sent to a PDF file that is later sent to an email address.

:FNAME = STRCAT (:DISK, :PAR2, '.PDF');
EXECUTE WINHTML '-d', :ENAME, '', '', '-v', :ADOC, 
'-format, -10', '-pdf', :FNAME;
MAILMSG 5 TO EMAIL :EMAIL DATA :FNAME;

Returning to the documentation, the description of the '-s' flag is that it supresses the notification window that pops up when preparing the document. In my experience, it does not supress the notification, but more importantly, it causes the document not to appear, or in the case of my example code, no PDF code is created!

So DO NOT USE THE -s FLAG!

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.

The single character bug returns

This morning I was tasked with retrieving the location of inventory within a specific warehouse. I wrote the following code that is part of a larger procedure that creates a warehouse transfer document.

:LOC = '0'; /* default value */
SELECT WAREHOUSES.LOCNAME INTO :LOC
FROM WAREHOUSES, WARHSBAL
WHERE WAREHOUSES.WARHS = WARHSBAL.WARHS
AND WARHSBAL.PART = :PART
AND WAREHOUSES.WARHSNAME = 'Main';

When the procedure containing this code was run, there was a complaint that location R does not exist for warehouse Main. This error was doubly confusing - at first, I thought that the R might refer to the part's type, which would make no sense whatsoever. When I ran the snippet in Windbi, the result was something like R.1.2, which was when I understood what was happening.

:LOC is initially set to the single character '0', for if the retrieval fails (for example, if the part has no inventory) then at least :LOC will have a value that is guaranteed to exist. Unfortunately, as I discovered several years ago¹, initialising a string with a single character confuses Priority who thinks that this is a character variable, not a string, and so saving further values into this variable will only save the first character.

The solution is to have an initialisation line at the beginning, like :LOC = '00000000' (I imagine that the length doesn't matter too much, as long as it's longer than one character. Once this line had been added, the addition to the procedure worked flawlessly. 

I notice that I wrote in the earlier blog that this issue had been fixed in version 21. I'm not sure with which version I was working today (it was for an external client), but it was web-based and probably not less than version 23.

Internal links
[1] 51

WARNINGYES

It's very easy to define a business rule: if such and such happens, then either: display an error message, display a warning message, send an email, send an SMS, open a task or send a push message (whatever that is). A warning message displays some text along with two buttons, "continue" or "cancel". I've never noticed until now that the default button is "continue". Thus it's very easy to consider the scenario whereby a user does something that causes a rule to fire, but the user ignores the warning message and simply presses "continue", thus basically negating the whole point of the warning.

I discovered (very belatedly) that there is a system constant, WARNINGYES, that controls which button is the default; 1, which is the predefined value, means that the "continue" button is the default, whereas 0 means that "cancel" is the default. I changed the value of the constant to 0 and now hopefully users will actually read any message displayed.

This is to a certain extent shooting myself in the foot: once every couple of days I have to create delivery notes from packing lists (don't ask), and when I paste an order number into the delivery notes screen, two or three messages are displayed. I don't read them and simply press enter to continue. Now I'm going to have to press an arrow key and then enter. The message are generally "Packing list X is connected to this order" and some text referring to the customer. Because I belong to the 'tabula' users group, I can't execute the privileges program, otherwise I would turn these warning messages off. Such are the tribulations of a system manager.

I made that change 20 minutes ago and no one has written to me about it yet. Normally a change like that gets noticed very quickly.

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

More about the FILELIST program

The normal way of executing this program is as follows (STACK_ERR because I'm using the -R flag)

SELECT SQL.TMPFILE INTO :ST6 FROM DUMMY;
:CHKDIR = SYSPATH ('LOAD', 0);
EXECUTE FILELIST :CHKDIR, :ST6, '-R', '-d', SQL.TMPFILE;
LINK STACK_ERR TO :ST6;
SELECT * FROM STACK_ERR FORMAT; /* this is in Windbi */

If one omits the final parameter (SQL.TMPFILE), then the following is displayed

the option '-msg_file' is required but missing
Positional arguments:
  --dirname arg           directory name
  --linked_stack_file arg link to STACK6 or STACK_ERR for results
  --msg_file arg          text file for return messages

General arguments:
  --u                 names lowercased (result in STACK6 by default)
  --l                 names uppercased (result in STACK6 by default)
  --f                 forces resuts to be written to STACK_ERR
  --R                 performes subdirectories scan (result in STACK_ERR)
  --d                 put directory names in result
  --A 

The option '-msg_file' presumably refers to SQL.TMPFILE that I omitted from the procedure call. I have no idea where else this technique of omitting 'the text file for return messages' can be used in order to get a program's help. COPYFILE and MOVEFILE don't have such a parameter, although they do allow an optional STACK parameter that can be checked for program success or failure.

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';

Saving a part's picture by means of an interface

The scenario: a part is copied from company A to company B, along with various fields, including the path to the part's picture. The picture has a special status in the table PARTEXTFILE: the value of the field EXTFILENUM will be -1.

In the procedure that has been running for the past few years, the path to the picture has been obtained correctly, but was being stored as a regular entry in company B. The person responsible for company B had never brought this subject to my attention until now. When I saw that the picture was being saved as a regular entry, I tried to overcome this by passing the value -1 in the appropriate tuple in GENERALLOAD. This did not work.

My next attempt was a typical hack: find the value of EXTFILENUM for the part in company B and update it to -1. This worked, but obviously was not the correct way of solving the problem. Fortunately sanity was restored when I asked myself how regular saving of the picture works in the form LOGPART. This is when I discovered that the path is stored in the form variable EXTFILENAME, and that the trigger BUF11 causes this value to be stored as a picture in PARTEXTFILE (below is the standard code).

/* Insert, Update and Delete EXTFILENAME */
GOTO 111 WHERE :$.EXTFILENAME = :$1.EXTFILENAME
;
/* clean previous record */
DELETE FROM PARTEXTFILE
WHERE PART = :$.PART
AND EXTFILENUM = -1
;
GOTO 112 WHERE :$1.EXTFILENAME = '';
EXECUTE DELATTACH '-I', :$1.EXTFILENAME;
LABEL 112
;
/* insert new attachment */
GOTO 111 WHERE :$.EXTFILENAME = '';
INSERT INTO PARTEXTFILE (PART, EXTFILENUM, EXTFILENAME)
VALUES (:$.PART, -1, :$.EXTFILENAME)
;
LABEL 111;

Note the use of the procedure DELATTACH: this can cause the actual file to be deleted if it is not referenced anywhere. Saving the file parth in PARTEXTFILE is done by an insertion, thus enabling the value -1 to be set; doing this via an interface doesn't work.

Now I save the file path as the equivalent to :$.EXTFILENAME in the same tuple as the part name before passing it to the interface. And this works.