SUMMARY:
This Solution provides a way to prevent the following error message when accessing data from SQL-92 clients:
Column <column> in table <table> has value exceeding it max length
or precision (7864)
EXPLANATION
This error appears when trying to return data stored in a character field greater than the defined SQL width of the field.
With a 4GL connection to a Progress Database there is no size limit for a character field. By default, the SQL-WIDTH is set to twice the length of the displayed format. Since programs may not take the displayed format into account, the data size may easily overflow the SQL-WIDTH of the field, hence the error with SQL-92 Clients.
SOLUTION:
The aim of the V9 sample code given below is to scan the entire database and check to see whether data contained in character fields exceeds the SQL width. The code is based upon 3 loops (table, record and character fields) and a dynamic query. With static queries, it is possible to get rid of the third loop and have better performance with less CPU usage (make a program generator, need the right to compile).
This code can be compiled and run against any Version 9 database.
Once the report is generated, the SQL-WIDTH may be modified by applying the techniques given in Progress Knowledge Base Solution 20008.
The code has been tested in version 9.1C. You can expect the following scan rates with 600MHz machines, and a high CPU usage:
~4GBytes per hour on UNIX/Linux
~2GBytes per hour on NT
DEFINE VARIABLE FieldList AS CHARACTER NO-UNDO. /*optimize the Dynamic query*/
DEFINE VARIABLE qh AS HANDLE NO-UNDO. /*Query handle*/
DEFINE VARIABLE bh AS HANDLE NO-UNDO. /*buffer handle*/
DEFINE VARIABLE s AS INTEGER NO-UNDO. /*size*/
DEFINE VARIABLE i AS INTEGER NO-UNDO. /*to manage the extents*/
DEFINE VARIABLE k AS INTEGER NO-UNDO. /*to manage character field 'number'*/
DEFINE VARIABLE mk AS INTEGER NO-UNDO. /*max k*/
&SCOPED-DEFINE MaxExt 500 /*increase if needed*/
/* fastest way is to use arrays (faster than a temp-table defined LIKE _field)*/
DEFINE VARIABLE an AS CHARACTER EXTENT {&MaxExt} NO-UNDO. /*names*/
DEFINE VARIABLE abfh AS HANDLE EXTENT {&MaxExt} NO-UNDO. /*buffer field handle*/
DEFINE VARIABLE aex AS INTEGER EXTENT {&MaxExt} NO-UNDO. /*extents*/
DEFINE VARIABLE aw AS INTEGER EXTENT {&MaxExt} NO-UNDO. /*sql width*/
DEFINE VARIABLE ams AS INTEGER EXTENT {&MaxExt} NO-UNDO. /*max size*/
DEFINE VARIABLE ar AS ROWID EXTENT {&MaxExt} NO-UNDO. /*rowid of max size*/
DEFINE STREAM scr. /*screen*/
DEFINE STREAM rep. /*report*/
OUTPUT STREAM scr TO TERMINAL.
OUTPUT STREAM rep TO FieldSizeReport.txt.
PUT STREAM rep " FieldSizeReport.txt started the " TODAY " at " STRING(TIME,"hh:mm:ss").
PUT STREAM rep "~n Given info are: ~tField-Name~tWidth~tMaxSize~tMaxRowid~tExtent".
FOR EACH _file NO-LOCK:
/* Shall we keep it or skip it? */
IF (_file._file-name BEGINS "_"
AND LOOKUP(_file._file-name,"_file,_field,_index") = 0)
OR _file._file-name BEGINS "SYS"
OR _file._owner <> "PUB" THEN NEXT.
PUT STREAM rep UNFORMATTED "~n~n" STRING(TIME,'hh:mm:ss') "
Table: " _file._file-Name.
PAUSE 0.
DISPLAY STREAM scr STRING(TIME,'hh:mm:ss') + " Table: "
+ _file._file-Name FORMAT "X(77)" WITH THREE-D.
/* copy Schema and prepare FieldList */
DELETE OBJECT qh NO-ERROR.
DELETE OBJECT bh NO-ERROR.
FieldList = "".
CREATE BUFFER bh FOR TABLE _file._file-name BUFFER-NAME "yo".
k = 0.
FOR EACH _field OF _file NO-LOCK
WHERE _field._Data-Type = "CHARACTER":
ASSIGN
k = k + 1
an[k] = _field._field-name
abfh[k] = bh:BUFFER-FIELD(_field._field-name)
aex[k] = _field._extent
aw[k] = _field._width
FieldList = (IF FieldList = "" THEN "" ELSE FieldList + " ")
+ _field._field-name.
END.
IF FieldList = "" THEN NEXT. /*No char field, go to next one*/
/* Sweep the records, detect and store the max sizes and rowids*/
ASSIGN
ams = 0 /*reset the size array*/
mk = k. /*keep max value of k*/
CREATE QUERY qh.
qh:SET-BUFFERS(bh).
qh:QUERY-PREPARE("FOR EACH yo FIELDS (" + FieldList
+ ") NO-LOCK").
qh:QUERY-OPEN.
DO WHILE TRUE: /*record loop*/
qh:GET-NEXT().
IF qh:QUERY-OFF-END THEN LEAVE.
DO k = 1 TO mk: /*field loop*/
IF aex[k] = 0 THEN DO:
s = LENGTH(abfh[k]:BUFFER-VALUE).
IF s > ams[k] THEN ASSIGN
ams[k] = s
ar[k] = bh:ROWID.
END.
ELSE DO i = 1 TO aex[k]: /*extent loop if needed*/
s = LENGTH(abfh[k]:BUFFER-VALUE(i)).
IF s > ams[k] THEN ASSIGN
ams[k] = s
ar[k] = bh:ROWID.
END.
END.
END.
/* Report the fields where size > SqlWidth in schema */
DO k = 1 TO mk:
IF ams[k] < aw[k] THEN NEXT.
PUT STREAM rep UNFORMATTED "~n~t" an[k] "~t" aw[k] "~t"
ams[k] "~t" STRING(ar[k]) "~t" aex[k].
END.
END.
PUT STREAM rep "~n~n report finished the " TODAY " at "
STRING(TIME,"hh:mm:ss").
OUTPUT STREAM rep CLOSE.
OUTPUT STREAM scr CLOSE.
References to Written Documentation:
Progress Knowledge Base Solution 20008, "SQL92: How To Set SQL-WIDTH on UNIX and Windows"