[Stackoverflow] [Progress OpenEdge ABL] How do I fix a schema error in a metaschema table _Field? Column _Format in table PUB._Field has value exceedi

[Dai]

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:



...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 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:



...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.

Continue reading...