Kbase P29346: How to determine which table and area a record id (recid) exists in for V9
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
2/22/2011 |
|
Status: Verified
GOAL:
How to determine which table and area a record id (recid) exists in for V9
GOAL:
What area does a specific recid in?
GOAL:
What table is a specific recid in?
GOAL:
How to locate table and area information by recid?
GOAL:
How to find out what table a recid is in?
GOAL:
How to find a recid in a specific table?
GOAL:
How to determine which table a recid is in?
GOAL:
How to determine which table a recid exists in?
GOAL:
How to determine what table a record exists in?
GOAL:
Is there a way to identify a table name based only on the recid of a record?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
FIX:
The following code can be run to identify which tables and areas a contain a given recid. Execute the code, enter the recid and database name when prompted. The table names and areas will be displayed in message boxes.
DEFINE VARIABLE iRecID AS INTEGER NO-UNDO LABEL "Enter RECID".
DEFINE VARIABLE cDB AS CHARACTER NO-UNDO LABEL "Enter Database to Search".
DEFINE VARIABLE cTableList AS CHARACTER NO-UNDO.
IF NUM-DBS GT 0 THEN
ASSIGN cDB = LDBNAME(1).
UPDATE iRecID FORMAT ">>>>>>>>>9" cDB FORMAT "X(50)".
RUN BuildListOfTables IN THIS-PROCEDURE (iRecID, cDB, OUTPUT cTableList).
RUN BuildListOfAreas IN THIS-PROCEDURE (iRecID, cDB, cTableList).
PROCEDURE BuildListOfTables:
DEFINE INPUT PARAMETER piRecID AS INTEGER NO-UNDO.
DEFINE INPUT PARAMETER pcDB AS CHARACTER NO-UNDO.
DEFINE OUTPUT PARAMETER pcTableList AS CHARACTER NO-UNDO.
DEFINE VARIABLE cQueryString AS CHARACTER NO-UNDO.
DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hQueryTable AS HANDLE NO-UNDO.
DEFINE VARIABLE hBufferFile AS HANDLE NO-UNDO.
DEFINE VARIABLE hBufferTable AS HANDLE NO-UNDO.
DEFINE VARIABLE hBufferField AS HANDLE NO-UNDO.
CREATE QUERY hQuery.
CREATE BUFFER hBufferFile FOR TABLE pcDB + '._File'.
hQuery:SET-BUFFERS(hBufferFile).
hQuery:QUERY-PREPARE('FOR EACH ' + pcDB + '._File NO-LOCK WHERE SUBSTRING(_File-Name,1,1) NE "_"').
hQuery:QUERY-OPEN().
REPEAT WHILE hQuery:QUERY-OFF-END = FALSE:
hQuery:GET-NEXT() NO-ERROR.
IF hBufferFile:AVAILABLE = TRUE THEN
DO:
ASSIGN hBufferField = hBufferFile:BUFFER-FIELD('_File-Name')
cTableName = TRIM(hBufferField:STRING-VALUE)
cQueryString = 'FOR EACH &1.&2 NO-LOCK WHERE RECID(&1.&2) = &3'
cQueryString = SUBSTITUTE(cQueryString,pcDB,cTableName,piRecID).
CREATE QUERY hQueryTable.
CREATE BUFFER hBufferTable FOR TABLE pcDB + '.' + cTableName NO-ERROR.
IF ERROR-STATUS:GET-MESSAGE(1) = '' THEN
DO:
hQueryTable:SET-BUFFERS(hBufferTable).
hQueryTable:QUERY-PREPARE(cQueryString).
hQueryTable:QUERY-OPEN().
hQueryTable:GET-FIRST().
IF hBufferTable:AVAILABLE = TRUE THEN
IF pcTableList NE '' THEN
ASSIGN pcTableList = pcTableList + ',' + cTableName.
ELSE
ASSIGN pcTableList = cTableName.
hQueryTable:QUERY-CLOSE().
END.
DELETE OBJECT hQueryTable NO-ERROR.
DELETE OBJECT hBufferTable NO-ERROR.
END.
END.
hQuery:QUERY-CLOSE().
DELETE OBJECT hQuery NO-ERROR.
DELETE OBJECT hBufferFile NO-ERROR.
END PROCEDURE.
PROCEDURE BuildListOfAreas:
DEFINE INPUT PARAMETER piRecID AS INTEGER NO-UNDO.
DEFINE INPUT PARAMETER pcDB AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER pcTableList AS CHARACTER NO-UNDO.
DEFINE VARIABLE cQueryString AS CHARACTER NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hBufferFile AS HANDLE NO-UNDO.
DEFINE VARIABLE hBufferField AS. HANDLE NO-UNDO.
DEFINE VARIABLE hBufferStorageObject AS HANDLE NO-UNDO.
DEFINE VARIABLE iLoop AS INTEGER NO-UNDO.
DO iLoop = 1 to NUM-ENTRIES(pcTableList):
ASSIGN cQueryString = 'FOR EACH &1.&2 WHERE &1.&2._File-Name = "&3" NO-LOCK, EACH &1.&4 WHERE &1.&4._Object-Number = &1.&2._File-Number AND &1.&4._Object-Type = 1 NO-LOCK'
cQueryString = SUBSTITUTE(cQueryString, pcDB, '_File', TRIM(ENTRY(iLoop,pcTableList)), '_StorageObject').
CREATE QUERY hQuery.
CREATE BUFFER hBufferFile FOR TABLE pcDB + '._File'.
CREATE BUFFER hBufferStorageObject FOR TABLE pcDB + '._StorageObject'.
hQuery:SET-BUFFERS(hBufferFile, hBufferStorageObject).
hQuery:QUERY-PREPARE(cQueryString).
hQuery:QUERY-OPEN().
hQuery:GET-FIRST().
IF hBufferFile:AVAILABLE = TRUE AND hBufferStorageObject:AVAILABLE = TRUE THEN
DO:
ASSIGN hBufferField = hBufferStorageObject:BUFFER-FIELD('_Area-Number').
MESSAGE 'RECID ' +
STRING(piRecID) +
' was found for table "' +
TRIM(ENTRY(iLoop,pcTableList)) +
'", which is located in storage area ' +
hBufferField:BUFFER-VALUE
VIEW-AS ALERT-BOX.
END.
hQuery:QUERY-CLOSE().
DELETE OBJECT hQuery NO-ERROR.
DELETE OBJECT hBufferFile NO-ERROR.
DELETE OBJECT hBufferStorageObject NO-ERROR.
END.
END PROCEDURE.
.