Show TOC

CONVERSION_ERROR Option [TSQL]Locate this document in the navigation structure

Controls reporting of data type conversion failures on fetching information from the database.

Allowed Values

ON, OFF

Default

ON

Scope

Option can be set at the database (PUBLIC) or user level. At the database level, the value becomes the default for any new user, but has no impact on existing users. At the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.

Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.

Remarks

This option controls whether data type conversion failures, when data is fetched from the database or inserted into the database, are reported by the database as errors (CONVERSION_ERROR set to ON), or as warnings (CONVERSION_ERROR set to OFF).

When CONVERSION_ERROR is set to ON, the SQLE_CONVERSION_ERROR error is generated.

If the option is set to OFF, the warning SQLE_CANNOT_CONVERT is produced. Each thread doing data conversion for a LOAD statement writes at most one warning message to the .iqmsg file.

If conversion errors are reported as warnings only, the NULL value is used in place of the value that could not be converted. In Embedded SQL, an indicator variable is set to -2 for the column or columns that cause the error.

Note SAP IQ no longer truncates converted tinyint, smallint, int, unsigned int, double, bigint, unsigned bigint, date, datetime values that are greater than the column width. If the CONVERSION_ERROR option is OFF,  SAP IQ inserts a NULL value when possible. If CONVERSION_ERROR is ON, SAP IQ logs a conversion error.