Kbase P125251: 4GL: How to list all CHARACTER field values in a database that have leading or trailing blanks?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/18/2011 |
|
Status: Verified
GOAL:
4GL: How to list all CHARACTER field values in a database that have leading or trailing blanks?
GOAL:
How to generate a report for all the CHARACTER fields in a database that contain leading or trailing spaces?
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
FIX:
The following procedure generates a report for all the CHARACTER fields in the connected database that contain leading or trailing spaces:
/*** Procedure to report all character fields whose values have leading or trailing blanks ***/
/***************Define needed variables************/
DEFINE VARIABLE hBufferHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE iFieldCounter AS INTEGER NO-UNDO.
DEFINE VARIABLE iExtentCounter AS INTEGER NO-UNDO.
DEFINE VARIABLE hFieldHandle AS HANDLE NO-UNDO.
DEFINE VARIABLE hQueryHandle AS HANDLE NO-UNDO.
OUTPUT TO datareprot.txt.
PUT UNFORMATTED
"Table Name" AT 1
"Field Name" AT 15
"Field Value" AT 35
"Field Length" AT 50
SKIP.
FOR EACH _File NO-LOCK WHERE _Tbl-Type = "T":
RUN ScanTable(INPUT _File-Name).
END.
OUTPUT CLOSE.
/******** Initialize Database Table Name ********/
PROCEDURE ScanTable:
DEFINE INPUT PARAMETER cTableName AS CHARACTER.
/* Create dynamic query for the cTablename */
CREATE BUFFER hBufferHandle FOR TABLE cTableName.
CREATE QUERY hQueryHandle.
hQueryHandle:SET-BUFFERS(hBufferHandle).
hQueryHandle:QUERY-PREPARE("for each " + cTableName + " NO-LOCK").
hQueryHandle:QUERY-OPEN.
/* Iterate through all the table's CHARACTER fields modifying those with one or more blank characters */
REPEAT:
hQueryHandle:GET-NEXT().
IF hQueryHandle:QUERY-OFF-END THEN LEAVE.
DO iFieldCounter = 1 TO hBufferHandle:NUM-FIELDS:
hFieldHandle = hBufferHandle:BUFFER-FIELD(iFieldCounter).
IF (hFieldHandle:DATA-TYPE <> "CHARACTER") THEN NEXT.
DO iExtentCounter = IF hFieldHandle:EXTENT = 0
THEN 0
ELSE 1
TO hFieldHandle:EXTENT:
IF LENGTH(TRIM(hFieldHandle:BUFFER-VALUE[iExtentCounter])) = LENGTH(hFieldHandle:BUFFER-VALUE[iExtentCounter]) THEN NEXT.
PUT UNFORMATTED
cTableName AT 1
hFieldHandle:NAME AT 15
&n.bsp; hFieldHandle:BUFFER-VALUE[iExtentCounter] AT 35
LENGTH(hFieldHandle:BUFFER-VALUE[iExtentCounter]) AT 50
SKIP.
END. /* DO iExtentCounter */
END. /* DO iFieldCounter */
END. /* REPEAT */
DELETE OBJECT hBufferHandle.
DELETE OBJECT hQueryHandle.
END PROCEDURE.
.