XLSXGEN program in endless loop

[gwilburn]

I’ve been using these tools for many years to create Excel documents. Many thanks to you and the late Giovanni Perotti.

I recently updated both CGIDEV2 and HSSFCGI from older 2019 versions to the latest 2023 version. After doing so, one of my jobs ran amuck. The XLSXGEN program was stuck in an endless loop.
After several hours of debug, I found the issue in my XML data – I was creating a cell type of “Formula” where the cell data was blank. Previously, this simply put *ERROR* in the cell and moved on.
More importantly, I discovered that the XLSXGEN program was looping between an “Exit” subroutine and the *PSSR subroutine.

A java error occurs in ss_formula() when formula is blank, triggering the "Failed" subroutine - no issue here.
(From procedure CellFormula)

Code: Select all

              
              peFormula=%trim(formula);
              monitor;
                 ss_formula(row:rowcellNbr:peFormula:cellstyle);   // theRow:theRowcellNbr  ????
              on-error;
                 exsr Failed;
              endmon;

<other code here>

         Begsr Failed;
         CellError();        //create TEXT cell containing '*ERROR*'
         msgTxt='FORMULA cell failed to be created in +
                row no. ' + %trim(%editc(rowNbr+1:'J')) +
                ', cell no. ' + %trim(%editc(rowCellnbr+1:'J')) +
                ', sheet ' + %trim(sheetname) +
                ', workbook ' + %trim(outStmf);
         SndMsgBack('*DIAG     ':msgTxt);
         Endsr;

      /end-free
     P CellFormula     e

Procedure CellError is where the problem begins - SS_TEXT attempts to write a cell containing *ERROR*
But this generates another error indicating a reference to an object that no longer exists. "Row" contains a reference to
OBJECT(*JAVA:'org.apache.poi.ss.usermodel.Row').

Code: Select all

      *=========================================================================
      * Create a TEXT cell flagged as error
      *=========================================================================
     P CellError       b
     D CellError       pi
     D dataValue       s           5000    varying
      /free

         dataValue='*ERROR*';
         cellStyleArg='LGTXTC';
         SetCellstyle(cellStyleArg);  // assign/create cell style in variable "cellstyle"
         datavalue1024=datavalue; //proc hssf_text supports a max of 1024 chars
         SS_TEXT(Row:rowcellNbr:dataValue1024:cellStyle);

      /end-free
     P CellError       e
 

The error in SS_TEXT invokes *PSSR
Here is where we enter the endless loop - *PSSR executes subroutine EXIT

Code: Select all

      *========================================================================
      * Program status subroutine
      *========================================================================
      /free
         Begsr *PSSR;

         statCodeSave=PSDSSTSCD;    //save the Error Status Code: type of error

         if pssrInd=*on;    //*PSSR already entered
            [b]exsr Exit[/b];
         endif;

         pssrInd=*on;

In Exit, the error calling ss_freeLocalRef(outfile) line triggers *PSSR

Code: Select all

      *========================================================================
      * Exit program
      *========================================================================
      /free
         Begsr Exit;

         // Close the XLSGEN log file
         if %open(XLSGENLOG);
            close XLSGENLOG;
         endif;
         // Close the HSSFCGIDTA error messages file
         if %open(XLSGENERRS);
            close XLSGENERRS;
         endif;
         // Close the QTEMP warning messages file
         if %open(XLSGENWMSG);
            close XLSGENWMSG;
         endif;

         // If coming from *PSSR, ...
         if pssrInd=*on;
            [b]ss_freeLocalRef(outfile);[/b]
            ss_freeLocalRef(tempstr);
            ss_end_object_group();       //Remove space for object references in the object group
            FreeRefs();                  //Free JVM object references to free memory

From here we go round and round. While in this loop, it is continually writing to a joblog.
I would like to fix the error logic in XLSXGEN so this doesn’t happen again, and would appreciate any suggestions.

FWIW - I compared the source code from my previous version. The code previously checked the content in CellFormula before using SS_FORMULA... that was removed in favor of the monitor block.

Thx,
Greg

[Scott Klement]

Back in the 1970s and 1980s, *PSSR was very difficult to use because if an error occurs inside PSSR, it calls the same PSSR again, creating a loop, so you have to be very careful in your code.

In your case, you tried to prevent PSSR from running multiple times by doing "if pssrInd = *on"... the problem is you don't turn that indicator on until the end of the routine. So if anything goes wrong in your Exit subroutine, it will be an endless loop, because the indicator is never set on.

This is why you should never use *PSSR. It was our only option 30 years ago, its true... but today we have MONITOR/ENDMON with ON-ERROR/ON-EXCP. It is so many millions of times better than *PSSR... more powerful, more versatile, easier to make programs that don't enter endless loops... it is better in every way. Don't use *PSSR, please!

As for your statement about changes to the code -- I don't think I've made any changes to HSSFR4, and this does not appear to be my coding style. If someone built a tool using HSSFR4 (I assume XLSXGEN is one such tool?) then you must contact whomever is responsible for this tool. Or fix it yourself.

[gwilburn]

Thanks Scott... XLSXGEN was created by Giovanni Perotti (easy400.net). Unfortunately, he passed away in February of 2024. Maybe he wasn't at his best when updating this in late 2023...

I was hoping maybe you knew more about his code than I do. I have been programming 29 years and have never used *PSSR

Wonder if I could alleviate the issue if I simply move the pssrInd to the beginning of the subroutine?

In comparing the old vs new XLSXGEN, I see a move to use monitor statements more. At this point, I'm torn between resurrecting the 2019 version of the program versus fixing the new one. I get the feeling that I may be the only shop using this utility.