CODEWITHSUNDEEP
openedgeprogress-4glprogress-db
Dai
Dai

Reputation: 155145

How do I fix a schema error in a metaschema table _Field? Column _Format in table PUB._Field has value exceeding its max length or precision."

I'm working with a Progress 9.1E database application. (Yes, I'm aware of how bad that sounds).

My problem began when I ran SELECT * FROM SYSPROGRESS.SYSCOLUMNS_FULL, ...which gave me this error:

ERROR [HY000] [DataDirect][ODBC PROGRESS driver][PROGRESS]Column _Format in table PUB._Field has value exceeding its max length or precision.

(For those unaware, the SYSCOLUMNS_FULL table is actually a SQL VIEW that's defined in PUB._Sysviews, and it's defined as (essentially) SELECT ... FROM PUB._Field INNER JOIN PUB._File)

Now if this was a normal user table then the solution is to edit the SQL Width of that column in the Data Dictionary tool - but the problem here is that _Field is a built-in metaschema table, and the Data Dictionary tool does not allow editing of the SQL Width of its columns.

...but I figured out that you can Unfreeze the table, then edit it, then re-Freeze it, which is what I did: I changed _Format's SQL Width to 1024 chars:

enter image description here

...and disregarded the dire warning...

enter image description here

...and I fully restarted the machine (as the _mprosrv.exe and _sqlsrv2.exe processes keep the schema cached indefinitely, I understand).

...and it did not fix the problem.

So my next was to run DBTool's "SQL Width & Date Scan w/Report Option" and "SQL Width Scan w/Fix Option" modes; which reported finding zero SQL Width errors (and only a single Date error).

So my next step was to try to see what's in PUB._Field that could cause this; so I ran this query:

SELECT
    t."_File-Name"  AS Tbl,
    f."_Field-Name" AS Col,
    f."_Data-Type"  AS Typ,
    f."_Format"     AS Fmt,
    f."_Width"      AS Wid

FROM
    PUB."_Field" AS f
    INNER JOIN PUB."_File" AS t ON f."_File-recid" = t.ROWID

...which gave me the same error as above. Which makes sense: clearly I can't access or use the "_Format" directly without causing that error, so I tried the trick where you use the ODBC scalar-function escape syntax, which should prevent "bad values" from being exposed to the ODBC layer:

SELECT
    { fn LENGTH( ""_Format"" ) } AS len
FROM
    PUB.""_Field""

...and this did not fix the problem.

So I used the Data Administration tool to dump the _Field metaschema table/view to a .d file - as well as Exporting it to CSV so I could open it in Excel.

Here's the Text dump opened in Excel, with all rows sorted (descending) by the length of their _Format column value:

enter image description here

...and the longest defined _Format value is 65 characters long.

...so perhaps the problem isn't _Format at all, but is some bug somewhere else that merely presents itself this way? Either way, this is beyond my current abilities.

So, I'm stumped - and because this version is so out-of-date there's no option for professional support.

Upvotes: 1

Views: 110

Answers (2)

Stefan Drissen
Stefan Drissen

Reputation: 3370

sysnumstat is used by the query optimizer:

Contains one row for each column of data type NUMERIC. Used by the optimizer, each row contains a sampling of values in the column.

So you may be able to undo update statistics, after which, if you are using SQL you probably want to update statistics again, running into the same problem again.

Upvotes: 1

Tom Bascom
Tom Bascom

Reputation: 14020

It looks like the problem is the that actual value of the _format field is disagreeable to your client. Those fields are formatted for the maximum possible decimal value - 50 digits plus commas etc. Even if the fields were to get used (and they look like extra "just in case" fields to me) it seems pretty unlikely that they will have values that large. So do something like:

for each _field:

  if _field-name begins "_" and _format begins "->,>>" and length( _format ) > 40 then
    _format = "->,>>>,>>>,>>9.9999999999" no-error.

end.

(Obviously do this in a test system first.)

Upvotes: 1

Related Questions