truncateTooLarge fails with max length error on _File

Query against _File fails with max length or precision error on the _Field-map column despite setting -SQLTruncateTooLarge on the database or specifying the truncateTooLarge setting in the connection string.

Jun 3, 2016
Knowledge

Title

URL Name

truncateTooLarge-fails-with-max-length-error-on-File

Article Number

000185604

Environment

Product: OpenEdge Version: 11.5.1, 11.6.x OS: All supported platforms Other: SQL-92, Authorized data truncation

Question/Problem Description

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.

Steps to Reproduce

Clarifying Information

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

Error Message

Column _Field-map at rowid ##### in table PUB._File has value exceeding its max length or precision.

Defect Number

Enhancement Number

Cause

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

Resolution

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.

Workaround

Notes

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

Keyword Phrase

Last Modified Date

11/20/2020 7:23 AM

truncateTooLarge fails with max length error on _File

Files

Was this article helpful?

Was this article helpful?

truncateTooLarge fails with max length error on _File

Information

Related Articles