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 NAME = '$'
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;