The following code example will report on all CHARACTER fields in the database where the current length of data in that field exceeds the current SQL-WIDTH value.
If a particular table's fields are of interest, the scope can be narrowed by replacing the line:
FOR EACH _File NO-LOCK WHERE _Tbl-Type = "T":
With the table-number:
FIND FIRST _File NO-LOCK WHERE _file-number = 4.
OR with the table-name:
FIND FIRST _File NO-LOCK WHERE _file-name = "Customer".
and removing one END statement in the current block.
Example of output produced:
Table# TableName FieldName sqlWidth actualWidth FIX
4 Customer Comments 160 2987 yes
5 Department DeptName 15 28 yes
/* SQLvsLENGTH.p */
DEFINE TEMP-TABLE ttSQLWidth NO-UNDO
FIELD tableName AS CHARACTER
FIELD tableNum AS INTEGER
FIELD fieldName AS CHARACTER
FIELD sqlWidth AS INTEGER
FIELD actualWidth AS INTEGER
FIELD requireFix AS LOGICAL INIT FALSE
INDEX tableNum
tableNum
INDEX tableName
tableName.
FOR EACH _File NO-LOCK WHERE _Tbl-Type = "T":
FOR EACH _Field OF _File WHERE _Field._Data-type = "character":
CREATE ttSQLWidth.
ASSIGN tableName = _File._File-name
tableNum = _File._File-num
fieldName = _Field._Field-name
sqlWidth = _Field._Width.
RELEASE ttSQLWidth.
END. /* FOR EACH _Field */
END. /* FOR EACH _File */
DEFINE VARIABLE bTab AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE queryString AS CHARACTER NO-UNDO.
FOR EACH ttSQLWidth NO-LOCK:
CREATE BUFFER bTab FOR TABLE tableName.
CREATE QUERY hQuery.
hQuery:ADD-BUFFER(bTab).
queryString = "FOR EACH " + tableName + " WHERE LENGTH(" + fieldName + ") >= " + STRING(sqlWidth) + " BY LENGTH(" + fieldName + ") DESC".
hQuery:QUERY-PREPARE(queryString).
hQuery:QUERY-OPEN().
IF hQuery:GET-NEXT() THEN DO:
ASSIGN actualWidth = LENGTH(bTab:BUFFER-FIELD(fieldName):BUFFER-VALUE)
requireFix = TRUE.
END. /* IF .. THEN DO */
hQuery:QUERY-CLOSE.
DELETE OBJECT hQuery.
bTab:BUFFER-RELEASE().
DELETE OBJECT bTab.
END. /* FOR EACH ttSQLWidth */
OUTPUT TO "./SQL2FIX.out".
PUT
"Table#" AT 1
"TableName" AT 8
"FieldName" AT 29
"sqlWidth" AT 55
"actualWidth" AT 65
"FIX" AT 78
SKIP.
FOR EACH ttSQLWidth WHERE requireFix = TRUE:
PUT UNFORM
tableNum AT 1
tableName AT 8
fieldName AT 29
sqlWidth TO 62
actualWidth TO 75
requireFix TO 80
SKIP.
END. /* FOR EACH ttSQLWidth */
OUTPUT CLOSE.
While DBTOOL which was introduced in Progress 9.1D06 can be used to report or report and fix SQL-Width, the code example above provides an alternative
a. In earlier versions which did not have DBTOOL
b. When DBTOOL does not work, example Article
DBTOOL SQL WIDTH crashes with "Unable to upgrade record"c. The code can be customised to report on subsets which the DBTOOL Option does not support. For example by specific tables in an area as opposed to all tables in an area.
