ODBC/JDBC error 7864 caused by insufficient SQL-Width

Getting error 7864 with SQL-92 client

Error 7864 with ODBC, JDBC clients or SQL explorer querying database

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.

Option #1
Use DBTOOL, option 2 - "SQL Width Scan w/Fix option" to scan the database and fix any fields where the length of the data exceeds the defined SQL-WIDTH.
Run DBTOOL from the PROENV prompt using the command:

dbtool <databaseName>

Option #2
Expand SQL-WIDTH manually via Data Dictionary by following these steps:

From the Data Dictionary on UNIX:
1) Select Schema.
2) Select SQL Properties.
3) Select the desired table.
4) Change the width for desired field to a number that your data length is (less than 2K).
5) Save.

From Data Dictionary on Windows:
1) Select "SQL Properties" from "Options" menu.
2) Select "Adjust Field Width".
3) Change the width for desired field to a number that your data length is (less than 2K).
4) Save.

Option #3
Change the SQL width programmatically (via the Progress 4GL). A starting point is as follows:

find first _file where _file._file-name = "<table>".
find first _field of _file where _field._field-name = "<column>".
update _field._width.

See Progress Article 000012367 referenced below for more information.

References to Other Documentation:

Progress Articles:
"How to detect problematic SQL-WIDTH with a 4GL program"
"What is the DBTool ?"
 "What is the -checkwidth parameter for?"