The following 4GL/ABL procedure, which is also attached to this article, 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 */.
