How to determine if a procedure needs to be recompiled after a database change

How to determine whether or not a procedure needs to be recompiled after a database change
How to determine that a .R program will not have a CRC error when ran against a database
How to compare the R-Code CRC against the table CRCs

In order to determine if a R-Code file will not have a CRC error:

• Use the TABLE-CRC-LIST attribute with TABLE-LIST attribute of the RCODE-INFO system handle.
• Compare the CRC value for all tables referenced in the R-Code file with the CRC values for those tables stored in the database.

The following code example: 

• Displays a list of tables and their CRC values referenced in the R-Code file 
• Which can be compared with the CRC value of the referenced tables in the database or the r-code will not run.
• While the RCODE-INFO handle does not provide information on INDEX CRCs,  both Table CRC and Index CRCs are included in the r-code. This code can be run against two databases to confirm the Index CRC's match before deploying the r-code. 

DEFINE VARIABLE cTemp AS CHARACTER   NO-UNDO.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE idot AS INTEGER     NO-UNDO.
DEFINE VARIABLE cDBname AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cTable AS CHARACTER   NO-UNDO.

DEFINE VARIABLE cTableList AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cCRCList AS CHARACTER   NO-UNDO.
DEFINE VARIABLE cFileList AS CHARACTER   NO-UNDO.

DEFINE VARIABLE cIndexes AS CHARACTER NO-UNDO.

DEFINE TEMP-TABLE ttTableCRC NO-UNDO
   FIELD cNAME AS CHARACTER  FORMAT "X(20)"   /* Table name*/
   FIELD iCRC AS INTEGER FORMAT ">>>>>>9 ".    /* Table's CRC value */

/* R file to check */
RCODE-INFO:FILE-NAME = "D:\rcode\crcustords.r".

/* Retrieve table list as well as their CRC values */
ASSIGN
   cTableList = RCODE-INFO:TABLE-LIST
   cCRCList =  RCODE-INFO:TABLE-CRC-LIST.

/* Store tables referenced in the .R file into the Temp-Table */
REPEAT i = 1 TO NUM-ENTRIES(cTableList):
   ASSIGN
       cTemp = ENTRY(i,cTableList)
       idot = INDEX(cTemp,".")
       cDbname = substring(cTemp,1,idot - 1)
    /* SUBSTRING removes the database name from Table-List */
       cTable = SUBSTRING(cTemp,idot + 1, LENGTH(cTemp) - idot).

   CREATE ttTableCRC.
   ASSIGN
       ttTableCRC.cName = cTable
       ttTableCRC.iCRC = INTEGER(ENTRY(i,cCRCList)).
END.

/* Display the table CRC value stored in the .R file */
/* and the table CRC value in the datbase            */
 OUTPUT TO foo.txt APPEND.
 
     PUT UNFORM   
        "RCODE: " + RCODE-INFO:FILE-NAME  SKIP
        "RCODE_CRC: " + STRING(RCODE-INFO:CRC-VALUE) SKIP(1).

FOR EACH ttTableCRC:
   FIND FIRST _file WHERE _file._file-name = ttTableCRC.cName NO-LOCK.
      IF NOT AVAILABLE _file THEN NEXT.

cIndexes = "".

      PUT UNFORM      
        "Table Name: " + tttableCRC.cName SKIP
         "[.R] Table CRC: " + STRING(ttTableCRC.iCRC) SKIP
         "[DB] Table CRC: " + STRING(_file._CRC) SKIP(1).

   FOR EACH _index WHERE _index._file-recid EQ RECID(_file) NO-LOCK:
    cIndexes = cIndexes  + (IF (cIndexes GT "") EQ TRUE THEN CHR(10) ELSE "")  + 
     "[DB] Index(" + _index._index-name + ") " + STRING(_index._idx-crc). 
     END.

      PUT UNFORM
         cIndexes    SKIP(1).

END.
OUTPUT CLOSE.

 

Progress Articles:

 Does the RCODE-INFO handle provide information on INDEX CRCs?   
 How to display the CRC values for tables & indexes in the database  
Loading some attributes via delta.df does NOT affect _mstrblk._mstrblk-timestamp