Handle_IO_Retry.qvs

SET v_DelaysBeforeRetry = [3,3,3]; // comma-separated list of delays in seconds
LET v_StoreErrorsTo = 'Lib://C_Downloads/_simulate/IO_Errors_' & DocumentName(); // timestamp and ".txt" will be added
LET v_Continue_On_IO_Error = 1;

/* 
This scripts defines two important code-snippets to handle retries of LOAD or STORE statements.

In case of errors when Loading or storing data, it retries as many times, as there are values
in the variable v_DelaysBeforeRetry, waiting the number of seconds defined there between each retry.
e.g. SET v_DelaysBeforeRetry = [5,15];

NOTE: To save the error found --> set variable v_StoreErrorsTo  

e.g. LET v_StoreErrorsTo = 'Lib://:DataFiles/IO_Errors_' & DocumentName(); // timestamp and ".txt" will be added

and provide the two parameters when calling handle_io_retry_start: the operation type (LOAD or STORE) and
the source/target file

If v_StoreErrorsTo is not set or the parameters are not provided, it will just do the retries without saving the
error details in a log file.

If you want to continue script execution even if the last attempt was unsuccessful, set the variable
v_Continue_On_IO_Error to 1. If missing or any other value, it will break the execution after last negative retry.

Usage in a LOAD situation:

$(handle_io_retry_start(LOAD,<source>)); // before the LOAD statement you want to protect with retry logic

table:
LOAD * FROM [<source>] (<format>);

$(handle_io_retry_end); // immediately after the LOAD  statement you want to protect with retry logic



Usage in a STORE situation:

$(handle_io_retry_start(STORE,<destination>)) before the STORE statement you want to protect with retry logic

STORE table INTO [<destination>] (<format>);

$(handle_io_retry_end) immediately after the STORE statement you want to protect with retry logic

Additional complexity in the code snippets comes from the fact that they neutralize the effect of QUALIFY *;
The log file generated will not contain qualified field names (no "." in the fields)

*/

// begin of script-variable definition: handle_io_retry_start

SET handle_io_retry_start = `
  IF '$~(v_DelaysBeforeRetry)' = '' THEN
        [v_DelaysBeforeRetry not defined. Aborting.
  		To use handle_io_retry, variable v_DelaysBeforeRetry must be set with comma-separated retry delays in seconds, e.g. SET v_DelaysBeforeRetry = 5,15;
  		];
  END IF
  LET v_104_ErrorModeBefore = ErrorMode;
  LET v_104_LoadRetry = 0;
  LET v_104_Operation = '$1';
  LET v_104_CreateLog = '$2' <> ('$' & '2'); // if parameter $2 was provided, create log entry 
  LET v_104_ioFile = '$2';
  LET v_104_FileLogTimeStamp = TimeStamp(Now(),'YYYY-MM-DD hh-mm-ss');
  LET v_104_FieldAliasing = '';

  DO

      LET v_104_ErrorCountBefore = ScriptErrorCount;
      SET ErrorMode = 0;
      TRACE handle_io_retry ($~(v_104_ErrorCountBefore) script errors so far);
`;

// end of script-variable definition: handle_io_retry_start

LET handle_io_retry_start = PurgeChar(handle_io_retry_start, '~');

//  begin of script-variable definition: handle_io_retry_end

SET handle_io_retry_end = `
      LET v_104_ErrorsCatch = ScriptErrorCount & Chr(9) & PurgeChar(ScriptErrorList, Chr(13));
      LET v_104_ErrorsNew = SubField(v_104_ErrorsCatch, Chr(9), 1) - v_104_ErrorCountBefore;
      LET v_104_ErrorMsg = SubField(SubField(v_104_ErrorsCatch, Chr(9), 2), Chr(10), -2);
      WHEN v_104_ErrorsNew TRACE $~(v_104_Operation) had an error!;
      
      LET ErrorMode = v_104_ErrorModeBefore;

      IF v_104_ErrorsNew THEN

          LET v_104_LoadRetry = v_104_LoadRetry + 1;

          IF v_104_CreateLog AND Len(v_StoreErrorsTo) THEN 
              LET v_104_LogEntry = TimeStamp(Now(),'YYYY-MM-DD hh:mm:ss') 
                  & '^' & DocumentName() & '^' & DocumentTitle();

              IF Alt(TableNumber('IO_ERROR_LOG'), -1) > -1 THEN
                  CONCATENATE(IO_ERROR_LOG) LOAD *;
//               ELSE
//                   LOAD *;
              END IF

              IO_ERROR_LOG: 
              LOAD * INLINE [
                  -DATETIME- ^ -APPID- ^ -APPNAME- ^ -OPERATION- ^ -FILE- ^ -RETRY- ^ -MSG- ^ -ABORTED-
                  $~(v_104_LogEntry) ^ $~(v_104_Operation) ^ $~(v_104_ioFile) ^ $~(v_104_LoadRetry) ^ $~(v_104_ErrorMsg) ^ 
              ] (delimiter is '^');

              LET v_104_LogEntry = Null();


              IF Alt(FieldNumber('-DATETIME-', 'IO_ERROR_LOG'), 0) AND Len(v_104_FieldAliasing)=0 THEN
                  // no qualifying of field names
                  LET v_104_FieldAliasing = '*';
              ELSE
                  TRACE Neutralizing field-qualifying effect from IO_ERROR_LOG;

                  FOR EACH v_104_FieldToRen IN '-DATETIME-', '-APPID-', '-APPNAME-', '-OPERATION-', '-FILE-', '-RETRY-', '-MSG-', '-ABORTED-'
                      IF Alt(FieldNumber('IO_ERROR_LOG.$~(v_104_FieldToRen)', 'IO_ERROR_LOG'), 0) THEN
                          RENAME FIELD [IO_ERROR_LOG.$~(v_104_FieldToRen)] TO [IO_ERROR_LOG-1.$~(v_104_FieldToRen)];
                          LET v_104_FieldAliasing = If(Len(v_104_FieldAliasing), '$~(v_104_FieldAliasing),') & '[IO_ERROR_LOG-1.$~(v_104_FieldToRen)] AS [$~(v_104_FieldToRen)]';
                      END IF
                  NEXT v_104_FieldToRen 
              END IF

              TRACE Saving IO_ERROR_LOG to $(v_StoreErrorsTo)_$~(v_104_FileLogTimeStamp).txt ...;
              STORE $~(v_104_FieldAliasing) FROM IO_ERROR_LOG INTO [$(v_StoreErrorsTo)_$~(v_104_FileLogTimeStamp).txt] (txt, delimiter is '\t');
          END IF

          LET v_104_WaitSeconds = SubField(v_DelaysBeforeRetry, ',', v_104_LoadRetry);
          TRACE $~(v_104_LoadRetry). retry of $~(v_104_Operation) (waiting $~(v_104_WaitSeconds) seconds) ... $~(v_104_LastRetry);

          LET v_104_WaitUntil = Now() + v_104_WaitSeconds/24/3600;
          DO
          LOOP UNTIL Now() > v_104_WaitUntil; // wait until retry seconds are passed

      END IF

  LOOP UNTIL v_104_ErrorsNew = 0 OR v_104_LoadRetry = (1+SubstringCount(v_DelaysBeforeRetry, ','));

  IF v_104_ErrorsNew > 0 AND '$~(v_Continue_On_IO_Error)' <> '1' THEN
      IF v_104_CreateLog AND Len(v_StoreErrorsTo) THEN 
          // replace empty -ABORTED- field in last row with text 'ABORTED'
          IF v_104_FieldAliasing = '*' THEN
              DROP FIELD [-ABORTED-] FROM IO_ERROR_LOG;
          ELSE
              DROP FIELD [IO_ERROR_LOG-1.-ABORTED-] FROM IO_ERROR_LOG;
          END IF

          LEFT JOIN (IO_ERROR_LOG) 
          LOAD NoOfRows('IO_ERROR_LOG') AS [-RETRY-], 'ABORTED' AS [-ABORTED-] 
          AUTOGENERATE(1);

          STORE $~(v_104_FieldAliasing) FROM IO_ERROR_LOG INTO [$(v_StoreErrorsTo)_$~(v_104_FileLogTimeStamp).txt] (txt, delimiter is '\t');
      END IF
      // stop script now.
      SET ErrorMode = 1;
      TRACE handle_io_retry tried $~(v_104_LoadRetry) times to $~(v_104_Operation) without success.;
      [Stop execution];
  END IF
  
  IF Alt(TableNumber('IO_ERROR_LOG'), -1) > -1 THEN
      DROP TABLE IO_ERROR_LOG;
  END IF


  // remove temp variables
  LET v_104_ErrorModeBefore = Null();
  LET v_104_LoadRetry = Null();
  LET v_104_ErrorCountBefore = Null();
  LET v_104_WaitSeconds = Null();
  LET v_104_WaitUntil = Null();
  LET v_104_ErrorsNew = Null();
  LET v_104_CreateLog = Null();
  LET v_104_LogTimeStamp = Null();
  LET v_104_LogEntry = Null();
  LET v_104_Operation = Null();
  LET v_104_ioFile = Null();
  LET v_104_FileLogTimeStamp = Null();
  LET v_104_FieldToRen = Null();
  LET v_104_FieldAliasing = Null();
  LET v_104_ErrorsCatch = Null();
  LET v_104_ErrorMsg = Null();
`;

//  end of script-variable definition: handle_io_retry_end

LET handle_io_retry_end = PurgeChar(handle_io_retry_end, '~');