INFORMIX-ESQL/C Programmer's Manual INFORMIX-ESQL/C Data Types

When a discrepancy exists between the data types of two values, ESQL/C attempts to convert one of the data types. The process of converting a value from one data type to another is called data conversion.

The following list names a few common situations in which data conversion can occur:

• Comparison. Data conversion can occur if you use a condition that compares two different types of values, such as comparing the contents of a zip-code column to an integer value.

For example, to compare a CHAR value and a numeric value, ESQL/C converts the CHAR value to a numeric value before it performs the comparison.

• Fetching and inserting. Data conversion can occur if you fetch or insert values with host variables and database columns of different data types.
• Arithmetic operations. Data conversion can occur if a numeric value of one data type operates on a value of a different data type.

For more information on fetching and inserting, see the next section, "Fetching and Inserting with Host Variables." For more information on arithmetic operations, see Performing Arithmetic Operations.

If you try to fetch a value from a database column into a host variable that you do not declare according to the correspondence shown in Figure 3-1 on page 3-4, ESQL/C attempts to convert the data types. Similarly, if you try to insert a value from a host variable into a database column, ESQL/C might need to convert data types if the host variable and database column do not use the correspondences in Figure 3-1. ESQL/C converts the data types only if the conversion is meaningful.

This section provides the following information on data conversion for fetching and inserting values with host variables:

• How ESQL/C converts between numeric and character data
• How ESQL/C converts floating-point numbers to strings
• How ESQL/C converts BOOLEAN values to characters
• How ESQL/C converts DATETIME and INTERVAL values
• How ESQL/C converts between VARCHAR columns and character data

Before ESQL/C can convert a value from one data type to another, it must determine whether the conversion is meaningful. Figure 3-10 shows possible conversions between numeric data types and character data types. In this figure, N represents a value with a numeric data type (such as DECIMAL, FLOAT, or SMALLINT) and C represents a value with a character data type (such as CHAR or VARCHAR).

If conversion is not possible, either because it makes no sense or because the target variable is too small to accept the converted value, ESQL/C returns values that the Results column in Figure 3-10 describes.

In Figure 3-10, the phrase Does not fit means that the size of the data from the source variable or column exceeds the size of the target column or variable.

ESQL/C can automatically convert floating-point column values (data type of DECIMAL(n), FLOAT, or SMALLFLOAT) between database columns and host variables of character type char, varchar, string, or fixchar. When ESQL/C converts a floating-point value to a character string whose buffer is not large enough to hold the full precision, ESQL/C rounds the value to fit it in the character buffer. For more information, see Implicit Data Conversion.

The database server can automatically convert BOOLEAN values between database columns and host variables of the fixchar date type. The following table shows the character representations for the BOOLEAN values.

For more information on the BOOLEAN data type, see Chapter 5, Working with Numeric Data Types.

ESQL/C can automatically convert DATETIME and INTERVAL values between database columns and host variables of character type char, string, or fixchar. ESQL/C converts a DATETIME or INTERVAL value to a character string and then stores it in a host variable. For more information, see Implicit Data Conversion.

You can use ESQL/C library functions to explicitly convert between DATE and DATETIME values. For more information, see Data Conversion for datetime and interval Values.

ESQL/C can automatically convert VARCHAR values between database columns and host variables of character type char, string, or fixchar. For more information, see Fetching and Inserting VARCHAR Data.

When ESQL/C performs an arithmetic operation on two values, it might need to convert data types if the two values do not have data types that match. This section provides the following information on data conversion for arithmetic operations:

• How ESQL/C converts numeric values
• How ESQL/C handles operations that involve floating-point values

If two values of different numeric data types operate on one another, ESQL/C converts the values to the data type that Figure 3-11 indicates and then performs the operation.

Figure 3-11 shows that if ESQL/C performs an operation between an operand with a data type of FLOAT and a second operand with a data type of DECIMAL (DEC), ESQL/C generates a result that has a DECIMAL data type. For more information about DECIMAL values, see the next section, "Using Operations That Involve a Decimal Value."

The following table shows the numeric data types. Database columns use the SQL data types, and ESQL/C host variables use the corresponding ESQL/C data types.

When ESQL/C performs arithmetic operations on operands with numeric data types and one of the operands has a decimal value (an SQL data type of DECIMAL or an ESQL/C data type of decimal), ESQL/C converts each operand and the result to a decimal value.

An SQL DECIMAL data type has the format DECIMAL(p,s), where p and s represent the following parameters:

• The p parameter is the precision, which is the total number of significant digits in a real number.

For example, the number 1237.354 has a precision of seven.

• The s parameter is the scale, which is the number of digits that represent the fractional part of the real number.

For example, the number 1237.354 has a scale of three. If the DECIMAL data type includes a scale parameter (DECIMAL(p,s)), it holds fixed-point decimal numbers. If the DECIMAL data type omits a scale parameter (DECIMAL(p)), it holds floating-point decimal numbers.

The ESQL/C decimal data type keeps track of precision and scale differently from the SQL DECIMAL data type. For simplicity, this section uses the format of the SQL DECIMAL data type to describe how ESQL/C performs data conversion for arithmetic operations that involve a decimal value. However, this same data-conversion information applies to arithmetic operations that involve an ESQL/C decimal host variable.

For more information on how the decimal data type tracks precision and scale, see The decimal Structure.

ESQL/C converts all operands that are not already DECIMAL (or decimal) to DECIMAL before it performs the arithmetic operation. The following table shows the precision and scale that ESQL/C uses for the non-DECIMAL operand.

ESQL/C does not consider leading or trailing zeros as significant digits. Leading or trailing zeros do not contribute to the determination of precision and scale. If the operation is addition or subtraction, ESQL/C adds trailing zeros to the operand with the smaller scale until the scales are equal.

The precision and scale of the arithmetic result depend on the precision and scale of the operands and on whether one of the operands is a floating-point decimal, as follows:

• When one of the operands is a floating-point decimal, the arithmetic result is a floating-point decimal.

For example, for an arithmetic operation between a fixed-point decimal of DECIMAL(8,3) and a FLOAT value, ESQL/C converts the FLOAT value to a floating-point decimal of DECIMAL(17). The arithmetic result has a data type of DECIMAL(17).

• When both of the operands are fixed-point decimals, the arithmetic result is also a fixed-point decimal.

Figure 3-12 summarizes the rules for arithmetic operations on operands with definite scale (fixed-point decimals). In Figure 3-12, p₁ and s₁ are the precision and scale of the first operand, and p2 and s₂ are the precision and scale of the second operand.

Figure 3-12
Precision and Scale of Fixed-Decimal Arithmetic Results

Operation	Precision and Scale of Result
Addition and Subtraction	Precision: Scale:	MIN(32, MAX(p1 - s1, p2 - s2) + MAX(s1, s2) + 1) MAX(s1, s2)
Multiplication	Precision: Scale:	MIN(32, p1 + p2) s1 + s2; If (s1 + s2) > precision, the result is a floating-point decimal number (no scale value).
Division	Precision: Scale:	32 Result is a floating-point decimal number. The sum: 32 - p1 + s1 - s2 cannot be negative.

If the data type of the result of an arithmetic operation requires the loss of significant digits, ESQL/C reports an error.