CONVERSION_MODE option

Function

Restricts implicit conversion between binary data types (BINARY, VARBINARY, and LONG BINARY) and other non-binary data types (BIT, TINYINT, SMALLINT, INT, UNSIGNED INT, BIGINT, UNSIGNED BIGINT, CHAR, VARCHAR, and LONG VARCHAR) on various operations.

Allowed values

0, 1

Default

0

Scope

Can be set either publicly or temporarily. DBA permissions are not required to set this option.

Description

The default value of 0 maintains implicit conversion behavior prior to version 12.7. Setting CONVERSION_MODE to 1 restricts implicit conversion of binary data types to any other non-binary data type on INSERT, UPDATE, and in queries. The restrict binary conversion mode also applies to LOAD TABLE default values and CHECK constraint. The use of this option prevents implicit data type conversions of encrypted data that would result in semantically meaningless operations.

Implicit conversion restrictions

The CONVERSION_MODE option restrict binary mode value of 1 restricts implicit conversion for these operations:

LOAD TABLE The restrict implicit binary conversion mode applies to LOAD TABLE with CHECK constraint or default value.

For example:

CREATE TABLE t3 (c1 INT,
    csi SMALLINT,
    cvb VARBINARY(2),
    CHECK (csi<cvb));
SET TEMPORARY OPTION CONVERSION_MODE = 1;

This request:

LOAD TABLE t3(c1 ',', csi ',', cvb ',')
    FROM '/s1/mydata/t3.inp'
    QUOTES OFF ESCAPES OFF
    ROW DELIMITED BY '\n'

fails with the message:

"Invalid data type comparison in predicate
(t3.csi < t3.cvb), [-1001013] ['QFA13']"

INSERT The restrict implicit binary conversion mode applies to INSERT...SELECT, INSERT...VALUE, and INSERT...LOCATION.

For example:

CREATE TABLE t1 (c1 INT PRIMARY KEY,
    cbt BIT NULL,
    cti TINYINT,
    csi SMALLINT,
    cin INTEGER,
    cui UNSIGNED INTEGER,
    cbi BIGINT,
    cub UNSIGNED BIGINT,
    cch CHAR(10),
    cvc VARCHAR(10),
    cbn BINARY(8),
    cvb VARBINARY(8),
    clb LONG BINARY,
    clc LONG VARCHAR);

CREATE TABLE t2 (c1 INT PRIMARY KEY,
    cbt BIT NULL,
    cti TINYINT,
    csi SMALLINT,
    cin INTEGER,
    cui UNSIGNED INTEGER,
    cbi BIGINT,
    cub UNSIGNED BIGINT,
    cch CHAR(10),
    cvc VARCHAR(10),
    cbn BINARY(8),
    cvb VARBINARY(8),
    clb LONG BINARY,
    clc LONG VARCHAR);

CREATE TABLE t4 (c1 INT, cin INT DEFAULT 0x31);

SET TEMPORARY OPTION CONVERSION_MODE = 1;

This request:

INSERT INTO t1(c1, cvb) SELECT 99, cin FROM T2
WHERE c1=1

fails with the message:

"Unable to convert column 'cvb' to the requested
datatype (varbinary) from datatype (integer).
[-1013043] ['QCA43']"

UPDATE The restrict implicit binary conversion mode applies to these types of UPDATE:

For example, this request:

UPDATE t1 SET cbi=cbn WHERE c1=1

fails with the message:

"Unable to implicitly convert column 'cbi' to datatype
(bigint) from datatype (binary). [-1000187] ['QCB87']"

Positioned INSERT and positioned UPDATE via updatable cursor The restrict implicit binary conversion mode applies to these types of INSERT and UPDATE via updatable cursor:

Queries The restrict implicit binary conversion mode applies to all aspects of queries in general.

  1. Comparison Operators When CONVERSION_MODE = 1, the restriction applies to these operators: =, !=, <, <=, >=, <>, !>, !< BETWEEN … AND IN used in a search condition for these clauses: WHERE clause HAVING clause CHECK clause ON phrase in a join IF/CASE expression

    For example, this query:

    SELECT COUNT(*) FROM T1
    WHERE cvb IN (SELECT csi FROM T2)
    

    fails with the message:

    "Invalid data type comparison in predicate 
    (t1.cvb IN (SELECT t1.csi ...)), [-1001013]
    ['QFA13']"
    
  2. String Functions When CONVERSION_MODE = 1, the restriction applies to these string functions: CHAR CHAR_LENGTH DIFFERENCE LCASE LEFT LOWER LTRIM PATINDEX RIGHT RTRIM SIMILAR SORTKEY SOUNDEX SPACE STR TRIM UCASE UPPER

    For example, this query:

    SELECT ASCII(cvb) FROM t1 WHERE c1=1
    

    fails with the message:

    "Data exception - data type conversion is not
    possible. Argument to ASCII must be string,
    [-1009145] ['QFA2E']"
    

    The following functions allow either a string argument or a binary argument. When CONVERSION_MODE = 1, the restriction applies to mixed type arguments, that is, one argument is string and the other argument is binary.INSERTSTR LOCATE REPLACE STRING STUFF

    For example, this query:

    SELECT STRING(cvb, cvc) FROM t1 WHERE c1=1
    

    where the column cvb is defined as VARBINARY and the column cvc is defined as VARCHAR, fails with the message:

    "Data exception - data type conversion is not
    possible. Arguments to STRING must be all binary 
    or all string, [-1009145] ['QFA2E']"
    

    The restriction does not apply to these string functions: BIT_LENGTH BYTE_LENGTH CHARINDEX LENGTH OCTET_LENGTH REPEAT REPLICATE SUBSTRING

  3. Arithmetic Operations and Functions When CONVERSION_MODE = 1, the restriction applies to these operators used in arithmetic operations: +, -, *, /

    The restriction applies to these bitwise operators used in bitwise expressions: & (AND), | (OR), ^ (XOR)

    The restriction also applies to integer arguments of these functions: ROUND “TRUNCATE” TRUNCNUM

    For example, this query:

    SELECT ROUND(4.4, cvb) FROM t1 WHERE C1=1
    

    fails with the message:

    "Data exception - data type conversion is not
    possible. Second Argument to ROUND cannot be
    converted into an integer, [-1009145] ['QFA2E']"
    
  4. Integer Argument to Various Functions When CONVERSION_MODE = 1, the restriction applies to integer argument of these functions: ARGN SUBSTRING DATEADD YMD

    For example, this query:

    SELECT ARGN(cvb, csi, cti) FROM t1 WHERE c1=1
    

    fails with the message:

    "Data exception - data type conversion is not
    possible. First Argument to ARGN cannot be converted
    to an integer, [-1009145] ['QFA2E']"
    
  5. Analytical Functions, Aggregate Functions, and Numeric Functions When CONVERSION_MODE = 1, no further restriction applies to analytical functions, aggregate functions, and numeric functions that require numeric expressions as arguments.

See also

For more information on data type conversion, see Chapter 7, “Moving Data In and Out of Databases” in the System Administration Guide: Volume 1.

For more information on column encryption, see Advanced Security in Sybase IQ. Users must be specifically licensed to use the encrypted column functionality of the Sybase IQ Advanced Security Option.