SQL-92: SQL Width and 4GL Field Formats

SQL-92 engine raises error when trying to return data that is greater than the defined SQL width of the field.
Problem accessing character fields in Progress tables from SQL applications.
 

In version 9.0x, 9.1A, 9.1B, 9.1C, and upto 9.1D05 a problem exists where the SQL92 will not correctly reflect an update made to the 4GL schema when the content of a field exceeds the format phrase defined.

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, the following error will be returned in an ODBC or JDBC connection:

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.

Upgrade to 9.1D06 or later. Which fixes one cause of the 210012 error.

Beginning with Progress 9.1E, the DBTOOL utility is provided to identify and fix SQL width violations of character and decimal data, as well as identify data values incompatible with SQL-92. See the References to Other Documentation below for more information on DBTOOL.

In Progress 9.1D05 and earlier, the following options are available to avoid errors from data in a field exceeding its SQL width:

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:
 

3) The preferred solution in Progress 9.1D and earlier 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:
 
This assumes that table1.col2 is the field with the long values, and it is being truncated to 8 characters.
 

While at least one cause to the error 210012 has been fixed in 9.1D06 it may not be possible for some to upgrade to that version.
For those that cannot upgrade try the workaround below.
a. shutdown the database
b. set SQLREUSE envirnoment variable to false
e.g. through <Control Pannel>-><System>-><Advanced>-><System Environment Variables> on Windows
e.g SQLREUSE=false;export SQLREUSE on Unix
c. restart the database

References to Other Documentation:

OpenEdge Data Management: Database Administration, Chapter 25, "Other Database Administration Utilities > DBTOOL utility"

Progress Article(s):

 What is DBTOOL?