ABL applications at times produce data that is larger than the size defined by SQL, since the ABL character data type (although has database record size limitations) does not provide a maximum defined size. OpenEdge SQL now allows database administrators (DBAs) to authorize truncation of data, so that the selected data fits the defined column size and the part of the value larger than the defined size of the column is truncated.

When the truncation option is set to ON or ALL, OpenEdge SQL uses a common, uniform model of data truncation. It operates on the truncated data by identifying the database as if it contains only the data truncated to its defined size and not the large data that may exist in a table. Internally, it may temporarily identify the large data in its full size, but the effective (logical) use of the data is in its truncated form. This simple data model of truncated data provides consistency and understandability across the many ways in which SQL operates on data: using indexes, performing direct table scans, evaluating expressions, evaluating predicates, and other operations.

When the truncation option is set to OUTPUT, the OpenEdge SQL uses an alternate model of data truncation, in which data is allowed internally to exceed its defined size and data is operated on at its actual, full size. When SQL outputs data to the application, SQL truncates any data that exceeds it size, to its defined size. This model provides consistency between SQL operations and the physical dimensions of database values. The application safely receives data in its logically defined size, after truncation.
Note: The data truncation operation affects the output result set, as it contains the truncated data. Only columns of type VARCHAR are affected by an authorized data truncation. Data truncation can be limited only to output using the SQLTruncateTooLarge parameter.
A DBA can authorize truncation of data in the following ways:
  • The SQLTruncateTooLarge can be set to ON or ALL to truncate data exceeding the column size while reading from the database and also in the output. If the parameter is set to OFF, no data is truncated and SQL displays an error. SQL also displays an error if the SQLTruncateTooLarge parameter is not specified at all in case of data exceeding the column size. The default value for the SQLTruncateTooLarge parameter is OFF. If the SQLTruncateTooLarge parameter is set to OUTPUT, data truncation is performed only on the output. All SQL operations such as comparison, sorting use actual data and only the output is truncated.
     PROSERVE –db <dbname> -S <port-number> -SQLTruncateTooLarge <on |all|output|off>
  • Embedding a data truncation option in the connection URL - SQL has an optional parameter truncateTooLarge that can be embedded in the connection URL to authorize truncation of data that exceeds the column size. With its value set to ON or ALL, the parameter allows truncating data exceeding the column size and all SQL operations are performed on truncated data. If the value is set to OFF, no data is truncated and an error is displayed. If the value is set to OUTPUT, only the output is truncated. The sql operations are performed on data that is not truncated. An error is also displayed if the truncateTooLarge parameter is not specified at all. The default value for the truncateTooLarge parameter is OFF. Not specifying the parameter is equivalent to setting its value to OFF.

    The value of the truncateTooLarge parameter is connection specific and is remembered by SQL only during the connection session. The log setting of a connection-specific truncation overwrites the authorized data truncation setting at the database level.

    jdbc:datadirect:openedge://<ip>:<port>;databaseName=<dbname>;truncateTooLarge=<on/all/output/off>;
    Note: The ODBC drivers have been updated to support the new truncateTooLarge parameter. See the ODBC driver changes section for more details.
An authorized data truncation operation in a CREATE INDEX statement fails in the following cases:
  • One of the index components is of type VARCHAR
  • At least one row in the table on which the index is being created has a column value exceeding the defined maximum size of the column