Solution ID: P4393
FACT(s) (Environment):
Progress 9.x
SYMPTOM(s):
Getting error 7864 with SQL92 client
Column <column> in table <table> has value exceeding it max length or precision (7864)
CAUSE:
This error appears on a SQL-92 Client 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 (except the 32k limit for the all record). 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 go beyond the SQL-WIDTH of the field, hence the error with SQL-92 Clients.
FIX:
The V9 4GL sample code given below scans the entire database and check to see whether data contained in character fields exceeds the SQL width by comparing the length of character fields with _field._width.
Please bear in mind that in order to solve the problem permanently, you must review the SQL-WIDTH of character fields that are supposed to handle large amounts of text (especially with editor widgets on the UI).
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 Solution 20008 (SQL92: How To Set SQL-WIDTH on UNIX and Windows) .
The code has been tested in version 9.1C and optimized against speed with the use of array variables (faster than using lists or handling an additional temp-table loop). 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.
Note that the code does not check the Raw fields. The length of such a field can be obtained with the 4GL GET-SIZE() function..