STRING_TRUNCATE_ERROR session environment option
You can set the STRING_TRUNCATE_ERROR session environment option to control how string insertions (using DML statements INSERT, UPDATE, MERGE) are handled when their values exceed the column length in a non-ANSI database.
MODE ANSI databases generate error -1279 (Value exceeds string column length) whenever there is an attempt to INSERT, UPDATE or MERGE a string that is longer than the receiving column, or when there is an assignment in a stored procedure where the source string is longer than the target string, including when parameters are being evaluated). This behaviour can now be requested in non-ANSI databases.
The STRING_TRUNCATE_ERROR option, used with the SET ENVIRONMENT statement, controls how string insertions (using DML statements INSERT, UPDATE, MERGE) are handled when their values exceed the column length in a non-ANSI database. This option allows you to either generate an error -1279 (Value exceeds string column length) or to silently truncate the string without reporting an error.
Sometimes, you will get an error such as -271 (Could not insert a new row into the table) with the ISAM error -181 (ISAM error: Value exceeds string column length).
The STRING_TRUNCATE_ERROR session environment option temporarily overrides the STRING_TRUNCATE_ERROR configuration parameter value. This override applies for the current session or until the session environment variable is reset.
STRING_TRUNCATE_ERROR environment option
The SET ENVIRONMENT STRING_TRUNCATE_ERROR statement of SQL supports the following syntax:
Usage
- 'ignore'
- For non-ANSI databases, no warning or error is generated when an insert, update, or merge operation on CHAR, VARCHAR, NCHAR, NVARCHAR, or LVARCHAR data types results in string truncation.
- 'error'
- For non-ANSI databases, an error -1279 (Value exceeds string column length) is returned if an insert, update, or merge operation on a CHAR, VARCHAR, NCHAR, NVARCHAR, or LVARCHAR column leads to string truncation.
Effect of STRING_TRUNCATE_ERROR
The given table provides expected outcomes of string INSERT, UPDATE, or MERGE operations when string truncation occurs. These outcomes are determined by different combinations of the STRING_TRUNCATE_ERROR configuration parameter and the STRING TRUNCATE_ERROR option available with SET ENVIRONMENT statement.
| Serial No. | STRING_TRUNCATE_ERROR onconfig value | STRING_TRUNCATE_ERROR Value at session level | Result (ANSI database) | Result (Non-ANSI database) |
|---|---|---|---|---|
| 1 | UNSET | UNSET | error -1279 | No error, silent truncation |
| 2 | UNSET | ignore | error -26041, -1279 | No error, silent truncation |
| 3 | UNSET | error | error -1279 | error -1279 |
| 4 | UNSET | Invalid string(anything other than 'ignore'/'error' | error -26041, -1279 | error -26041, silent truncation |
| 5 | 0 | UNSET | error -1279 | No error, silent truncation |
| 6 | 0 | ignore | error -26041, -1279 | No error, silent truncation |
| 7 | 0 | error | error -1279 | error -1279 |
| 8 | 0 | Invalid string(anything other than 'ignore'/'error' | error -26041, -1279 | error -26041, silent truncation |
| 9 | 1 | UNSET | error -1279 | error -1279 |
| 10 | 1 | ignore | error -26041, -1279 | No error, silent truncation |
| 11 | 1 | error | error -1279 | error -1279 |
| 12 | 1 | Invalid string(anything other than 'ignore'/'error' | error -26041, -1279 | error -26041, silent truncation |
- 0 = silent string truncation and SQL execution for non-ANSI databases
- 1 = raise an SQL error -1279 (Value exceeds string column length) for non-ANSI databases