Data is Truncated when Retrieved from Progress Databases

Background

Counterintuitively, the Progress 4GL database engine permits storage of character strings which exceed the field width (SQL_WIDTH) declared in the Progress Data Dictionary (by default, Progress sets SQL_WIDTH to twice the character-width of a field). For instance, given a table with a CHAR(16) column, Progress will allow insertion of any number of characters into that column. In a related but different issue, the Progress 4GL engine returns a fixed width of 8 for all character expressions in SQL queries, regardless of how long their actual value may be.

These two behaviors create problems with client applications that use SQLDescribeCol() (as they should) to determine the maximum amount of data in a field. Applications are only told about, and may only receive, the Data Dictionary-defined length of data (e.g., 16 characters in the CHAR(16) field), even though the actual data might be 20 (or 200, or more!) characters long, leading to data truncation, buffer overruns, and other issues.

This condition is often revealed by the error message --

Column <column_name> in table <table_name> has value exceeding its max length or precision.

Various errors related to buffer overruns -- signal 11, SIGSEGV, segmentation fault -- may also be caused by this condition.

Optimal Solution (mandatory for SQL-92 connections)

The best way to work around the first behavior is to set the Data Dictionary definitions appropriately for the actual content of the fields, and this is mandatory for SQL-92 connections (i.e., all connections to Progress OpenEdge 10.x, and many connections to Progress 9.1x). Progress OpenEdge 10.x and later include native reporting utilities (As quoted in this Progress Community forum post, Progress KB P7640 indicated that "Since 9.1D06, DBTool can be used to identify and fix SQL_WIDTH problems.") which can provide the actual maximum data width, and you can then use the Options >> SQL Properties >> Adjust Field Width utility to change the field definition to match. Older versions of Progress may have corresponding features which you can use with the help of a Progress DBA, Progress Support, or the Progress Knowledgebase.

Workaround Options (only possible with SQL-89 connections)

However, if you cannot change your Data Dictionary for any reason, OpenLink drivers offer work-around settings which may serve your needs for SQL-89 connections (available to any Progress 7.x through 9.x instance). (These work-arounds are not appropriate for all situations, so please test your own environment carefully!)

• When MIN_FIELD_LEN, or Field Size Minimum, is set, it will over-ride any lower value for any column in the Data Dictionary, including the value of 8 for character expressions in SQL queries. This value will be returned for SQLDescribeCol() calls, and any data values which are shorter than this length will be space-padded to reach it. Note: This space-padding can radically increase network traffic, and thus this work-around should only be used when the Data Dictionary absolutely cannot be modified.

• When MAX_FIELD_LEN, or Field Size Maximum, is set, this value will over-ride the 4GL's default of 8 for all character expressions in SQL queries. Note: Field Size Minimum (MIN_FIELD_LEN) over-rides Field Size Maximum (MAX_FIELD_LEN), if both are set, and Field Size Minimum is greater.

Be advised: The Progress Data Dictionary may still affect display size and precision. Data will always be returned, but some applications will still only display the amount of data accounted for in the Dictionary. Moreover, all fields will return the amount of data specified by MIN_FIELD_LEN or Field Size Minimum. Values which are shorter than the specified length will be space-padded.

To use these work-arounds, follow the instructions for your driver type:

Multi-Tier Enterprise Edition

1. Access your OpenLink server components installation.
   
2. Use a text editor to open your Broker Rulebook (e.g., oplrqb.ini), found by default in the /bin/ sub-directory of your OpenLink installation.
   
3. Locate the [Environment PROGRESSXXX] section corresponding to the active Databse Agent for Progress. For example:
   
   

   
   [Environment PROGRESS82A]
   ;TABLEVIEW =
   ;TABLEVIEW_QUALIFIER = Y
   ;DEADLOCK_TIMEOUT = 60 ; Seconds to wait for lock to release
   ;INSERT_LOGICAL = Y
   ;UPDATE_LOGICAL = Y
   ;MIN_FIELD_LEN =
   ;MAX_FIELD_LEN =
   CURSOR_SENSITIVITY = LOW
   DLC = D:\DBS\Progress\Pro82a
   Path = D:\DBS\Progress\Pro82a\BIN;D:\WINNT\System32
   

   
   
4. Remove the leading semicolon ";" from the MIN_FIELD_LEN entry, and set the value to the actual maximum length of your field contents.
   
   

   
   MIN_FIELD_LEN = 143
   

   
   
5. Remove the leading semicolon ";" from the MAX_FIELD_LEN entry, and set the value to the actual expected length of your SQL query character expressions, if longer than MIN_FIELD_LEN.
   
   

   
   MAX_FIELD_LEN = 200
   

   
   
6. Save your changes and exit the file.
   
7. Restart or reinitialize the Broker to make your change take effect.

Single-Tier Lite Edition

1. Launch the Data Sources (ODBC) control panel (typically, Start >> Control Panel >> Administrative Tools)
   
2. Locate and highlight your Progress DSN.
   
3. Click Configure.
   
4. Click the Progress Options tab.
   
5. Set Field Size Minimum to the maximum size of the problem field(s).
   
6. Set Field Size Maximum to the maximum expected length of any SQL query character expressions, if longer than Field Size Minimum.
   
7. Save your DSN.
   
8. Test.