Kbase 18959: SQL-92: SQL Width and 4GL Field Formats
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
27/04/2009 |
|
Status: Verified
SYMPTOM(s):
SQL-92 engine will error when trying to return data that is greater than the defined SQL width of the field.
Problem accessing character fields in Progress tables.
Error Code: 210012 or Native: -210012
SQL STATE: 22P00 or SQL State: S1000
Column <column> in table <table> has value exceeding it max length or precision.
FACT(s) (Environment):
Progress 9.x
All Supported Operating Systems
CAUSE:
Character fields defined in the 4GL Data Dictionary are given a specific format. This format is a display-only format for the 4GL. It does not restrict the amount of data that can be stored in the field. The amount of data that can be stored in a field by the 4GL is nearly 32KB (32KB is the maximum size of a record in the 4GL).
The metaschema also contains a SQL width for this field definition. This is required for SQL applications to know how much data is expected in the field. This is specifically for the SQL-92 engine, not the SQL-89 engine. In the metaschema, this is _field._width.
The current algorithm for setting the SQL width of a char field is twice the 4GL format e.g. if the format is "x(8)", the SQL width will be 16 chars.
If the data contained in a field is greater than the SQL width, you will get the following error (both from ODBC and JDBC):
Error Code: 210012 or Native: -210012
SQL STATE: 22P00 or SQL State: S1000
Column <column> in table <table> has value exceeding it max length or precision
e.g if the field in the above had more than 16 chars, a query would return this error.
FIX:
1) Make sure that the data in the field is never greater than the SQL width defined in the field.
2) Change the SQL width in the metaschema. In 9.1 there will be code in the 4GL data dictionary to set this value. In 9.0B, this must be done programmatically. The following code will do this:
find first _file where _file._file-name = "<table>".
find first _field of _file where _field._field-name = "<column>".
update _field._width.
3) The preferred solution is to create a SQL view from a SQL-92 client to provide the necessary data truncation. This is the preferred option because the data can be maintained as-is from the 4GL, but the customer is then responsible for any truncation of the data. The view created by the customer will contain a substring() of the field in question. The following code is an example:
CREATE VIEW view1 (col1, col2) AS SELECT col1, substring(col2,1,8) FROM pub.table1
This assumes that table1.col2 is the field with the long values, and it is being truncated to 8 characters.