Consultor Eletrônico

Status: Unverified

GOAL:

How to programmatically change every existing instance of a specific field value to a new value in all the database tables containing that field.

FACT(s) (Environment):

Progress 9.x

FIX:

The following example changes every existing field value instance of 17 of the 'CustNum' field to the new value of 7777 in all the database tables that have the 'CustNum' field:

DEFINE VARIABLE cFieldName AS CHARACTER NO-UNDO INITIAL "Cust-Num".
DEFINE VARIABLE cOldValue AS CHARACTER NO-UNDO INITIAL "17".
DEFINE VARIABLE cNewValue AS CHARACTER NO-UNDO INITIAL "7777".

FOR EACH _Field NO-LOCK WHERE _Field-Name = cFieldName,
EACH _File OF _Field NO-LOCK WHERE _Tbl-Type = 'T' AND NOT _Hidden:
RUN FieldValueGlobalChange(
INPUT _file._file-name,
INPUT cFieldName,
INPUT cOldValue,
INPUT cNewValue).
END.

PROCEDURE FieldValueGlobalChange:

DEFINE INPUT PARAMETER ipcTableName AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER ipcFieldName AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER ipcOldValue AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER ipcNewValue AS CHARACTER NO-UNDO.

DEFINE VARIABLE bTable AS HANDLE NO-UNDO.
DEFINE VARIABLE bField AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.

CREATE BUFFER bTable FOR TABLE ipcTableName.

ASSIGN
bField = bTable:BUFFER-FIELD(ipcFieldName).

CREATE QUERY hQuery.
hQuery:SET-BUFFERS(bTable).
hQuery:QUERY-PREPARE("FOR EACH " + ipcTableName + " WHERE " + ipcFieldName + " = " + ipcOldValue + " .").
hQuery:QUERY-OPEN().
REPEAT TRANSACTION:
hQuery:GET-NEXT(EXCLUSIVE-LOCK).
IF hQuery:QUERY-OFF-END THEN
LEAVE.
ELSE DO:
CASE bField:DATA-TYPE:
WHEN "CHARACTER" THEN
bField:BUFFER-VALUE = ipcNewValue.
WHEN "INTEGER" THEN
bField:BUFFER-VALUE = INTEGER(ipcNewValue).
WHEN "DECIMAL" THEN
bField:BUFFER-VALUE = DECIMAL(ipcNewValue).
WHEN "LOGICAL" THEN
bField:BUFFER-VALUE = LOGICAL(ipcNewValue).
END CASE.
END.
bTable:BUFFER-RELEASE.
END. /* REPEAT */

DELETE OBJECT bField.
DELETE OBJECT bTable.
DELETE OBJECT hQuery.

ASSIGN
bField = ?
bTable = ?
hQuery = ?.
END PROCEDURE.