Kbase P105662: How change the value of all CHARACTER fields containing blanks ONLY to ""?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/26/2008 |
|
Status: Verified
GOAL:
4GL/ABL: How change the value of all CHARACTER fields containing blanks ONLY to ""?
GOAL:
How to remove the space character; CHR(32); from all fields of a table containing ONLY one or more blanks?
GOAL:
How to scan all the CHARACTER fields of the user tables in a database for fields containing only blank characters?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
The following sample 4GL procedure scans all the user tables of the connected database searching for CHARACTER fields containing ONLY blanks ( one or more CHR(32) characters and nothing else). The procedure reports the table name, field name, Field Value and Field Length of each such field encountered and then removes the blank spaces from the field and sets its value "":
/***************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.
/** Loop Through User Tables of Connected Database **/
FOR EACH _File NO-LOCK WHERE _Tbl-Type = "T":
RUN ScanTableAndChangeBlankFields(INPUT _File-Name).
END.
OUTPUT CLOSE.
PROCEDURE ScanTableAndChangeBlankFields:
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.
IF hFieldHandle:EXTENT = 0 THEN DO:
IF (LEFT-TRIM(hFieldHandle:BUFFER-VALUE, CHR(32)) <> "") OR
LENGTH( (hFieldHandle:BUFFER-VALUE)) = 0 THEN NEXT.
PUT UNFORMATTED
cTableName AT 1
hFieldHandle:NAME AT 15
hFieldHandle:BUFFER-VALUE AT 35
LENGTH(hFieldHandle:BUFFER-VALUE) AT 50
SKIP.
DO TRANSACTION:
hQueryHandle:GET-CURRENT(EXCLU.SIVE-LOCK).
hFieldHandle:BUFFER-VALUE = "".
END. /* DO TRANSACTION */
END.
ELSE
DO iExtentCounter = 1 TO hFieldHandle:EXTENT:
IF (LEFT-TRIM(hFieldHandle:BUFFER-VALUE[iExtentCounter], CHR(32)) <> "") OR
LENGTH(hFieldHandle:BUFFER-VALUE[iExtentCounter]) = 0 THEN NEXT.
PUT UNFORMATTED
cTableName AT 1
hFieldHandle:NAME AT 15
hFieldHandle:BUFFER-VALUE[iExtentCounter] AT 35
LENGTH(hFieldHandle:BUFFER-VALUE[iExtentCounter]) AT 50
SKIP.
DO TRANSACTION:
hQueryHandle:GET-CURRENT(EXCLUSIVE-LOCK).
hFieldHandle:BUFFER-VALUE[iExtentCounter] = "".
END. /* DO TRANSACTION */
END. /* DO iExtentCounter */
END. /* DO iFieldCounter */
END. /* REPEAT */
DELETE OBJECT hBufferHandle.
DELETE OBJECT hQueryHandle.
END PROCEDURE..