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.