Kbase P86359: How to find a field in a database containing a given value
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
13/07/2004 |
|
Status: Unverified
GOAL:
How to find a field in a database containing a given value
GOAL:
How to scan a database to find a field with a specific value using 4GL code
FIX:
The code scans all 4GL tables in a database. If the given value is found, record ROWID and the field name with the value are reported. For arrays, the report includes also subscript.
DEFINE VARIABLE FieldList AS CHARACTER NO-UNDO. /*optimize the Dynamic query*/
DEFINE VARIABLE qh AS HANDLE NO-UNDO. /*Query handle*/
DEFINE VARIABLE bh AS HANDLE NO-UNDO. /*buffer handle*/
DEFINE VARIABLE s AS INTEGER NO-UNDO. /*size*/
DEFINE VARIABLE i AS INTEGER NO-UNDO. /*to manage the extents*/
DEFINE VARIABLE k AS INTEGER NO-UNDO. /*to manage character field 'number'*/
DEFINE VARIABLE mk AS INTEGER NO-UNDO. /*max k*/
&SCOPED-DEFINE MaxExt 500 /*increase if needed*/
/* fastest way is to use arrays (faster than a temp-table defined LIKE _field)*/
DEFINE VARIABLE an AS CHARACTER EXTENT {&MaxExt} NO-UNDO. /*names*/
DEFINE VARIABLE abfh AS HANDLE EXTENT {&MaxExt} NO-UNDO. /*buffer field handle*/
DEFINE VARIABLE aex AS INTEGER EXTENT {&MaxExt} NO-UNDO. /*extents*/
DEFINE VARIABLE ams AS INTEGER EXTENT {&MaxExt} NO-UNDO. /*max size*/
DEFINE VARIABLE ar AS ROWID EXTENT {&MaxExt} NO-UNDO. /*rowid of max size*/
DEFINE VARIABLE ars AS CHARACTER EXTENT {&MaxExt} NO-UNDO FORMAT "X(16)" . /*rowid string max size*/
DEFINE VARIABLE cDataType AS CHARACTER NO-UNDO.
DEFINE VARIABLE lEqual AS LOGICAL NO-UNDO.
/* variables for comparision */
DEFINE VARIABLE chrValToComp AS CHARACTER NO-UNDO.
DEFINE VARIABLE intValToComp AS INTEGER NO-UNDO.
DEFINE VARIABLE datValToComp AS DATE NO-UNDO.
DEFINE VARIABLE decValToComp AS DECIMAL NO-UNDO DECIMALS 10.
/* select one of the following to test */
/*
ASSIGN cDataType = "CHARACTER"
chrValToComp = "dynSDO".
*/
ASSIGN cDataType = "DECIMAL"
decValToComp = 4.24.
/* decValToComp = 3000001840.090000000. */
/*
ASSIGN cDataType = "INTEGER"
intValToComp = 10.
*/
/*
ASSIGN cDataType = "DATE"
datValToComp = 01/08/1960.
*/
/* ============================================ */
DEFINE STREAM scr. /*screen*/
DEFINE STREAM rep. /*report*/
OUTPUT STREAM scr TO TERMINAL.
OUTPUT STREAM rep TO ValueFoundIn.txt.
PUT STREAM rep " ValueFoundInReport.txt started the " TODAY " at "
STRING(TIME,"hh:mm:ss").
PUT STREAM rep
"~n Given info are: ~tField-Name~tRowid~tExtent".
FOR EACH _file NO-LOCK:
/* Shall we keep it or skip it? */
IF (_file._file-name BEGINS "_"
AND LOOKUP(_file._file-name,"_file,_field,_index") = 0)
/* next 2 lines are V9+ database specific */
OR _file._file-name BEGINS "SYS"
OR _file._owner <> "PUB" THEN NEXT.
PUT STREAM rep UNFORMATTED "~n~n" STRING(TIME,'hh:mm:ss') "
Table: " _file._file-Name.
PAUSE 0.
DISPLAY STREAM scr STRING(TIME,'hh:mm:ss') + " Table: "
+ _file._file-Name FORMAT "X(77)" WITH THREE-D.
/* copy Schema and prepare FieldList */
DELETE OBJECT qh NO-ERROR.
DELETE OBJECT bh NO-ERROR.
FieldList = "".
CREATE BUFFER bh FOR TABLE _file._file-name BUFFER-NAME "yo".
k = 0.
FOR EACH _field OF _file NO-LOCK
WHERE _field._Data-Type = cDataType:
ASSIGN
k = k + 1
an[k] = _field._field-name
abfh[k] = bh:BUFFER-FIELD(_field._field-name)
aex[k] = _field._extent
FieldList = FieldList + " " + _field._field-name.
END. /* FOR EACH _field */
FieldList = TRIM(FieldList).
IF FieldList = "" THEN NEXT. /*No char field, go to next one*/
/* Sweep the records, detect and store the max sizes and rowids*/
ASSIGN
ams = 0 /*reset the size array*/
mk = k. /*keep max value of k*/
CREATE QUERY qh.
qh:SET-BUFFERS(bh).
qh:QUERY-PREPARE("FOR EACH yo FIELDS (" + FieldList + ") NO-LOCK").
qh:QUERY-OPEN().
DO WHILE TRUE: /*record loop*/
qh:GET-NEXT().
.IF qh:QUERY-OFF-END THEN LEAVE.
DO k = 1 TO mk: /*field loop*/
IF aex[k] = 0 THEN DO: /* no extents */
CASE cDataType:
WHEN "DECIMAL" THEN lEqual = DecValToComp = abfh[k]:BUFFER-VALUE.
WHEN "CHARACTER" THEN lEqual = ChrValToComp = abfh[k]:BUFFER-VALUE.
WHEN "INTEGER" THEN lEqual = IntValToComp = abfh[k]:BUFFER-VALUE.
WHEN "DATE" THEN lEqual = DatValToComp = abfh[k]:BUFFER-VALUE.
END CASE.
IF lEqual THEN
PUT STREAM rep UNFORMATTED
"~n~tFOUND: " an[k] "~t" STRING(bh:ROWID) "~t" 0.
END. /* No extents -> aex[k] = 0 */
ELSE DO: /* extents */
DO i = 1 TO aex[k]:
CASE cDataType:
WHEN "DECIMAL" THEN lEqual = DecValToComp = abfh[k]:BUFFER-VALUE(i).
WHEN "CHARACTER" THEN lEqual = ChrValToComp = abfh[k]:BUFFER-VALUE(i).
WHEN "INTEGER" THEN lEqual = IntValToComp = abfh[k]:BUFFER-VALUE(i).
WHEN "DATE" THEN lEqual = DatValToComp = abfh[k]:BUFFER-VALUE(i).
END CASE.
IF lEqual THEN
PUT STREAM rep UNFORMATTED
"~n~tFOUND: " an[k] "~t" STRING(bh:ROWID) "~t" i.
END. /* DO i = 1 TO -> loop through extents */
END. /* ELSE DO -> extents */
END. /* field loop */
END. /* DO WHILE record loop */
END.
PUT STREAM rep "~n~n report finished the " TODAY " at " STRING(TIME,"hh:mm:ss").
OUTPUT STREAM rep CLOSE.
OUTPUT STREAM scr CLOSE..