Tech Tip: Find and fix all Blank or Null UUID fields in an entire database

PRODUCT: 4D | VERSION: 15.2 | PLATFORM: Mac & Win

Published On: July 1, 2016

The following method can be used to quickly locate and fix all of the Blank or Null UUID fields in a database.

It works by building a list of tables and fields that are marked as UUID format placing the Table ID and Column ID numbers into the matched arrays $tabs and $cols. Next the method loops over the matched arrays and builds pointers to the tables and fields. Next we query for Null or Empty UUID fields. Finally we apply a new UUID to the selection using APPLY TO SELECTION.

ARRAY LONGINT($tabs;0)
ARRAY LONGINT($cols;0)
Begin SQL
   select TABLE_ID, COLUMN_ID
    from _USER_COLUMNS
    WHERE DATA_TYPE = 13
    AND DATA_LENGTH = 16
   into :$tabs, :$cols;
End SQL

C_LONGINT($i;$end)
$end:=Size of array($tabs)

For ($i;1;$end)
   C_POINTER($table;$field)
   $table:=Table($tabs{$i})
   $field:=Field($tabs{$i};$cols{$i})
   QUERY BY FORMULA($table->;\
     $field->="" | \
     $field->="20202020202020202020202020202020" | \
     $field->="00000000000000000000000000000000" | \
     Is field value Null($field->))
   APPLY TO SELECTION($table->;$field->:=Generate UUID)
End for