Kbase 20008: ODBC/JDBC error 7864 caused by insufficient SQL-Width
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/19/2010 |
|
Status: Verified
SYMPTOM(s):
Getting error 7864 with SQL-92 client
Column <column> in table <table> has value exceeding its max length or precision (7864)
=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-210012
[JDBC Progress Driver]:Column <field> in table PUB.<file> has value exceeding its max length or precision.
Error 7864 with ODBC, JDBC clients or SQL explorer querying database
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
CAUSE:
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.
FIX:
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 solution P13348 for more information.