Kbase P140597: How to compare data in all tables on 2 different databases dynamically?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
1/30/2009 |
|
Status: Unverified
GOAL:
How to access 2 databases dynamically
GOAL:
How to read data on each of the tables dynamically
GOAL:
How to verify primary unique keys
FACT(s) (Environment):
Progress 9.1D
OpenEdge 10.x
FIX:
FUNCTION getKeyInfo RETURNS CHARACTER
( INPUT phBuffer AS HANDLE ):
DEFINE VARIABLE cIndexInfo AS CHARACTER NO-UNDO.
DEFINE VARIABLE cKeys AS CHARACTER NO-UNDO.
DEFINE VARIABLE iIndex AS INTEGER NO-UNDO.
DEFINE VARIABLE iEntry AS INTEGER NO-UNDO.
ASSIGN iIndex = 1
cIndexInfo = phBuffer:INDEX-INFORMATION(iIndex).
IDX-BLK:
DO WHILE cIndexInfo NE ?:
IF ENTRY(3,cIndexInfo) EQ "1" THEN DO:
IF ENTRY(2,cIndexInfo) EQ "0" THEN
RETURN ?.
DO iEntry = 5 TO NUM-ENTRIES(cIndexInfo) BY 2:
cKeys = cKeys + (IF (TRIM(cKeys) GT "") EQ TRUE THEN "," ELSE "") +
ENTRY(iEntry,cIndexInfo).
END.
LEAVE IDX-BLK.
END.
ASSIGN iIndex = iIndex + 1
cIndexInfo = phBuffer:INDEX-INFORMATION(iIndex).
END.
RETURN cKeys.
END FUNCTION.
DEFINE VARIABLE cDB AS CHARACTER NO-UNDO EXTENT 2.
DEFINE VARIABLE cQueryString AS CHARACTER NO-UNDO.
DEFINE VARIABLE cChangedFields AS CHARACTER NO-UNDO.
DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE iDB AS INTEGER NO-UNDO.
DO iDB = 1 TO NUM-DBS:
cDB[iDB] = LDBNAME(iDB).
END.
DEFINE VARIABLE hFile1 AS HANDLE NO-UNDO.
DEFINE VARIABLE hBuffer1 AS HANDLE NO-UNDO.
DEFINE VARIABLE hBuffer2 AS HANDLE NO-UNDO.
DEFINE TEMP-TABLE ttDiff
FIELD tcTableName AS CHARACTER
FIELD tRowRowid AS ROWID
FIELD tRowSrcRowid AS ROWID
FIELD tcChanges AS CHARACTER.
CREATE BUFFER hFile1 FOR TABLE cDB[1] + "._file".
DEFINE VARIABLE hQuery1 AS HANDLE NO-UNDO.
OUTPUT TO test.txt.
CREATE QUERY hQuery1.
hQuery1:SET-BUFFERS(hFile1).
cQueryString = "FOR EACH _file WHERE _tbl-type EQ ~'T~'".
hQuery1:QUERY-PREPARE(cQueryString).
hQuery1:QUERY-OPEN().
hQuery1:GET-FIRST().
DO WHILE NOT hQuery1:QUERY-OFF-END
ON ERROR UNDO, LEAVE:
cTableName = hFile1:BUFFER-FIELD("_file-name"):BUFFER-VALUE.
CREATE BUFFER hBuffer1 FOR TABLE cDB[1] + "." + cTableName.
CREATE BUFFER hBuffer2 FOR TABLE cDB[2] + "." + cTableName.
RUN compareBuffers ( INPUT hBuffer1,
INPUT hBuffer2 ).
RUN compareBuffers ( INPUT hBuffer2,
INPUT hBuffer1 ).
hQuery1:GET-NEXT().
DELETE OBJECT hBuffer1.
DELETE OBJECT hBuffer2.
END.
OUTPUT CLOSE.
FOR EACH ttDiff:
DISPLAY ttDiff.tcTableName ttDiff.tcChanges.
END.
PROCEDURE compareBuffers:
DEFINE INPUT PARAMETER hBuf.fer1 AS HANDLE NO-UNDO.
DEFINE INPUT PARAMETER hBuffer2 AS HANDLE NO-UNDO.
DEFINE VARIABLE cKeys AS CHARACTER NO-UNDO.
DEFINE VARIABLE cKey AS CHARACTER NO-UNDO.
DEFINE VARIABLE cQueryString AS CHARACTER NO-UNDO.
DEFINE VARIABLE iKey AS INTEGER NO-UNDO.
DEFINE VARIABLE iField AS INTEGER NO-UNDO.
DEFINE VARIABLE hBuff1Qry AS HANDLE NO-UNDO.
DEFINE VARIABLE hField AS HANDLE NO-UNDO.
DEFINE VARIABLE lChar AS LOGICAL NO-UNDO.
CREATE QUERY hBuff1Qry.
hBuff1Qry:SET-BUFFERS(hBuffer1).
cQueryString = "FOR EACH " + hBuffer1:NAME + " NO-LOCK".
hBuff1Qry:QUERY-PREPARE(cQueryString).
hBuff1Qry:QUERY-OPEN().
hBuff1Qry:GET-FIRST().
cKeys = getKeyInfo(hBuffer1).
IF cKeys EQ ? THEN DO:
MESSAGE "Could not compare " hBuffer1:NAME " because there are no unique indices to use for comparison."
VIEW-AS ALERT-BOX ERROR BUTTONS OK.
RETURN.
END.
cQueryString = "".
DO WHILE NOT hBuff1Qry:QUERY-OFF-END:
cQueryString = "".
DO iKey = 1 TO NUM-ENTRIES(cKeys):
cKey = ENTRY(iKey,cKeys).
hField = hBuffer1:BUFFER-FIELD(cKey).
lChar = (hField:DATA-TYPE BEGINS "CHAR").
cQueryString = cQueryString + (IF (TRIM(cQueryString) GT "") EQ TRUE THEN
" AND " ELSE "") +
hField:NAME + " EQ " + (IF lChar THEN "~'" ELSE "") +
hField:STRING-VALUE + (IF lChar THEN "~'" ELSE "").
END.
hBuffer2:FIND-FIRST("WHERE " + cQueryString,NO-LOCK) NO-ERROR.
IF NOT hBuffer2:AVAILABLE THEN DO:
CREATE ttDiff.
ASSIGN ttDiff.tcTableName = hBuffer1:NAME
ttDiff.tRowRowid = hBuffer1:ROWID
ttDiff.tRowSrcRowid = hBuffer2:ROWID
ttDiff.tcChanges = "Missing".
END.
IF hBuffer2:AVAILABLE AND
NOT hBuffer1:BUFFER-COMPARE(hBuffer2) THEN DO:
CREATE ttDiff.
ASSIGN ttDiff.tcTableName = hBuffer1:NAME
ttDiff.tRowRowid = hBuffer1:ROWID
ttDiff.tRowSrcRowid = hBuffer2:ROWID.
&.nbsp; DO iField = 1 TO hBuffer1:NUM-FIELDS:
IF hBuffer1:BUFFER-FIELD(iField):BUFFER-VALUE NE
hBuffer2:BUFFER-FIELD(iField):BUFFER-VALUE THEN
ttDiff.tcChanges = ttDiff.tcChanges + (IF (TRIM(ttDiff.tcChanges) GT "") EQ TRUE THEN
"," ELSE "") +
hBuffer1:BUFFER-FIELD(iField):NAME + CHR(10) + CHR(9) +
"~'" + hBuffer1:BUFFER-FIELD(iField):STRING-VALUE + "~' = ~'" +
hBuffer2:BUFFER-FIELD(iField):STRING-VALUE + "~'".
END.
END.
hBuff1Qry:GET-NEXT().
END.
DELETE OBJECT hBuff1Qry.
END PROCEDURE.
.