Kbase P104093: Query/RESULTS: How to remove invalid stale relationship definitions from a RESULTS QC7 file using 4G
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/26/2008 |
|
Status: Verified
GOAL:
Query/RESULTS: How to remove invalid stale relationship definitions from a RESULTS QC7 file using 4GL?
GOAL:
4GL/ABL: How to programmatically remove relationship definitions that reference non existing database tables from a RESULTS QC7 file?
FACT(s) (Environment):
Windows
Progress 8.x
Progress 9.x
OpenEdge 10.x
FIX:
This 4GL procedure removes all table invalid table relationship definition lines from an existing RESULTS QC7 file and outputs a new QC7 file with valid table relationship. To run this code, connect to the database and have the QC7 file in the PROPATH. Edit the code to assign valid old and new QC7 names before executing the code
DEFINE VARIABLE cOldQC7FileName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cNewQC7FileName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cLine AS CHARACTER NO-UNDO.
DEFINE VARIABLE cFirstTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSecondTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cDatabaseTableList AS CHARACTER NO-UNDO.
/* Assign the old and new QC7 name names */
ASSIGN
cOldQC7FileName = "Sports2000.qc7"
cNewQC7FileName = "mySports.qc7".
/* Construct a list of all valid database table names */
FOR EACH _file WHERE _Tbl-type = 'T':
cDatabaseTableList = cDatabaseTableList + "," + _file-name.
END.
cDatabaseTableList = LEFT-TRIM(cDatabaseTableList, ",").
/*** Read the QC7 file and output the report on tables in QC7 file relationships that are not valid table names ***/
INPUT FROM VALUE(cOldQC7FileName).
OUTPUT TO VALUE(cNewQC7FileName).
/* Read the QC7 file line by line and if it is a relationship line, process it */
REPEAT :
IMPORT UNFORMATTED cLine.
IF NOT TRIM(cLine) BEGINS 'relat[' THEN
PUT UNFORMATTED cLine SKIP.
RUN GetTableNames( INPUT cLine, OUTPUT cFirstTableName, OUTPUT cSecondTableName).
IF LOOKUP (cFirstTableName , cDatabaseTableList, ",") = 0 THEN
NEXT.
IF LOOKUP (cSecondTableName , cDatabaseTableList, ",") = 0 THEN
NEXT.
PUT UNFORMATTED cLine SKIP.
END.
INPUT CLOSE.
OUTPUT CLOSE.
/*** Process a line ***/
PROCEDURE GetTableNames:
DEFINE INPUT PARAMETER pcLine AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER pcFirstTableName AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER pcSecondTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE iFirstDotPosition AS INTEGER NO-UNDO.
DEFINE VARIABLE iSecondDotPosition AS INTEGER NO-UNDO.
DEFINE VARIABLE iFirstQuotePosition AS INTEGER NO-UNDO.
DEFINE VARIABLE iSecondQuotePosition AS INTEGER NO-UNDO.
DEFINE VARIABLE iFirstTableLength AS INTEGER NO-UNDO.
DEFINE VARIABLE iSecondTableLength AS INTEGER NO-UNDO.
ASSIGN
iFirstDotPosition = INDEX (pcline,'.', 1)
iSecondDotPosition = INDEX (pcline,'.', iFirstDotPosition + 1)
iFirstQuotePosition = INDEX (pcline,'~"', iFirstDotPositio.n + 1)
iSecondQuotePosition = INDEX (pcline,'~"', iSecondDotPosition + 1)
iFirstTableLength = iFirstQuotePosition - iFirstDotPosition - 1
iSecondTableLength = iSecondQuotePosition - iSecondDotPosition - 1
pcFirstTableName = SUBSTRING(pcline, iFirstDotPosition + 1, iFirstTableLength)
pcSecondTableName = SUBSTRING(pcline, iSecondDotPosition + 1, iSecondTableLength).
END PROCEDURE..