truncateTooLarge fails with max length error on _File

truncateTooLarge fails with max length error on _File.

Query against _File fails with max length or precision error despite enabling authorized data truncation.

Problem is reported for the _File._Field-map column.

Error occurs despite:
- setting -SQLTruncateTooLarge on the database
- specifying the truncateTooLarge setting in the connection string.

Data truncation works as expected against other database tables/columns; no error occurs and data is truncated to the MAX-WIDTH setting.

From OpenEdge 11.5.1 onward, OpenEdge SQL allows database administrators (DBAs) to authorize truncation of data, so that the selected data fits the defined column size and the part of the value larger than the defined size of the column is truncated.

In prior versions, or when data truncation is disabled, data exceeding the defined column size (MAX-WIDTH) would cause the query to fail with the error "Column <name> at rowid ##### in table <schema>.<tableName> has value exceeding its max length or precision."

This is expected behavior. _File._Field-map has type VARBINARY. Per the SQL Development manual, authorized data truncation only works against columns of type VARCHAR:

"Note: The data truncation operation affects the output result set, as it contains the truncated data. Only columns of type VARCHAR are affected by an authorized data truncation."

Option #1
Run DBTOOL against the database to fix up the SQL widths in order to avoid the max length / precision error. DBTOOL will fix up VARCHAR and VARBINARY columns.

Option #2
Query one of the the built-in system Views via SQL to obtain table information, e.g.

SELECT * FROM sysprogress.systables

The following Views are available and provide data from _File:
 

View	Description
SYSPROGRESS.SYSTABLES	Contains one row for each table in the database.
SYSPROGRESS.SYSTABLES_FULL	A superset of information in the SYSTABLES core system table.

References to other documentation:

OpenEdge® Data Management:SQL Development, Chapter 8, "Data Control Language and Transaction Behavior > Authorized data truncation"
OpenEdge® Data Management:SQL Reference, Chapter 6, "OpenEdge SQL System Catalog Tables"

Progress articles:
"SQL: Error: "Column _Field-map in table PUB._File has value exceeding its max length or precision"."
"How to enable Authorized Data Truncation in a JDBC or ODBC connection."