Kbase P105742: 4GL/ABL: How to remove all leading and trailing white space characters from all the CHARACTER fields
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
26/11/2008 |
|
Status: Verified
GOAL:
4GL/ABL: How to remove all leading and trailing white space characters from all the CHARACTER fields of all the user data tables of a database?
GOAL:
How to remove all instances of spaces, tabs, line feeds, and carriage returns from all the CHARACTER fields of all the user data tables of a database?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
The following 4GL/ABL procedure uses the 4GL/ABL TRIM function to remove all leading and trailing white space characters from all the CHARACTER fields of all the user data tables of the connected database:
/***************Define 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 all tables********/
FOR EACH _File NO-LOCK WHERE _Tbl-Type = "T":
ASSIGN
cTableName = _File-Name.
/* Create dynamic query for the current table */
CREATE BUFFER hBufferHandle FOR TABLE cTableName.
CREATE QUERY hQueryHandle.
hQueryHandle:SET-BUFFERS(hBufferHandle).
hQueryHandle:QUERY-PREPARE("for each " + cTableName + " NO-LOCK").
hQueryHandle:QUERY-OPEN.
/* Remove leading and trailing white space characters from all CHARACTER fields */
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 /* Field is NOT an Array */
DO TRANSACTION:
hQueryHandle:GET-CURRENT(EXCLUSIVE-LOCK).
hFieldHandle:BUFFER-VALUE = TRIM(hFieldHandle:BUFFER-VALUE).
END. /* DO TRANSACTION */
ELSE /* Field is an Array */
DO TRANSACTION:
hQueryHandle:GET-CURRENT(EXCLUSIVE-LOCK).
DO iExtentCounter = 1 TO hFieldHandle:EXTENT:
hFieldHandle:BUFFER-VALUE[iExtentCounter] = TRIM(hFieldHandle:BUFFER-VALUE[iExtentCounter]).
END. /* DO iExtentCounter */
END. /* DO TRANSACTION */
END. /* DO iFieldCounter */
END. .; /* REPEAT */
DELETE OBJECT hBufferHandle.
DELETE OBJECT hQueryHandle.
END. /* FOR EACH _File */.