INFORMIX
Informix-ESQL/C Programmer's Manual
Chapter 2: INFORMIX-ESQL/C Data Types
Home Contents Index Master Index New Book

Data Conversion

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:

    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.

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

Fetching and Inserting with Host Variables

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 2-1, 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 2-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:

Converting Numbers and Strings

 Before ESQL/C can convert a value from one data type to another, it must determine whether the conversion is meaningful. Figure 2-11 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 2-11 describes.

Figure 2-11

(1 of 2)

Conversion Problem Result

C C

Does not fit

ESQL/C truncates the string, sets a warning (sqlca.sqlwarn.sqlwarn1 to W and SQLSTATE to 01004), and sets any indicator variable to the size of the original string.

For more information, see "Fetching and Inserting Character Data Types".

N C

None

ESQL/C creates a string for the numeric value; it uses an exponential format for very large or very small numbers.

For more information, see "Converting Floating-Point Numbers to Strings".

N C

Does not fit

ESQL/C fills the string with asterisks, sets a warning (sqlca.sqlwarn.sqlwarn1 to W and SQLSTATE to 01004), and sets any indicator variable to a positive integer.

When the fractional part of a number does not fit in a character variable, ESQL/C rounds the number. Asterisks appear only when the integer part does not fit.

C N

None

ESQL/C determines the numeric data type based on the format of the character value; if the character contains a decimal point, ESQL/C converts the value to a DECIMAL value.

C N

Not a number

The number is undefined; ESQL/C sets sqlca.sqlcode and SQLSTATE to indicate a runtime error.

C N

Overflow

The number is undefined; ESQL/C sets sqlca.sqlcode and SQLSTATE to indicate a runtime error.

N N

Does not fit

ESQL/C attempts to convert the number to the new data type.

For information on possible errors, see the Informix Guide to SQL: Reference.

N N

Overflow

The number is undefined; ESQL/C sets sqlca.sqlcode and SQLSTATE to indicate a runtime error.

Data Conversion Problems and Results In Figure 2-11, 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.

Converting Floating-Point Numbers to Strings

ESQL/C can automatically convert floating-point column (data type of DECIMAL(n), FLOAT, or SMALLFLOAT) values 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".

Converting BOOLEAN Values to Characters

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.

BOOLEAN Value Character Representation

'\01'

'T'

'\00'

'F'

For more information, see "The BOOLEAN Data Type".

Converting DATETIME and INTERVAL Values

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".

Converting Between VARCHAR and Character Data Types

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".

Arithmetic Operations

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:

Converting Numbers to Numbers

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

Figure 2-12

Operands DEC FLOAT INT SERIAL SMALLFLOAT SMALLINT

DEC

DEC

DEC

DEC

DEC

DEC

DEC

FLOAT

DEC

FLOAT

FLOAT

FLOAT

FLOAT

FLOAT

INT

DEC

FLOAT

INT

INT

FLOAT

INT

SERIAL

DEC

FLOAT

INT

INT

FLOAT

INT

SMALLFLOAT

DEC

FLOAT

FLOAT

FLOAT

FLOAT

FLOAT

SMALLINT

DEC

FLOAT

INT

INT

FLOAT

INT

Data Types for Which ESQL/C Carries Out Numeric Operations Figure 2-12 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 "Operations That Involve a Decimal Value".

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.
SQL Data Type ESQL/C Data Type

INTEGER

long integer

SMALLINT

short integer

DECIMAL

decimal

MONEY

decimal

FLOAT

double

SMALLFLOAT

float

When ESQL/C performs arithmetic operations on operands with numeric data types and one of the operands has a decimal value (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:

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

    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 Data Type".

To Convert the Non-DECIMAL Numeric Operand
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.
Operand Type Convert To

FLOAT

DECIMAL(16)

SMALLFLOAT

DECIMAL(8)

INTEGER

DECIMAL(10,0)

SMALLINT

DECIMAL(5,0)

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.

To Obtain the DECIMAL Data Type of the Arithmetic Result
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:

    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(16). The arithmetic result has a data type of DECIMAL(16).

    Figure 2-13 summarizes the rules for arithmetic operations on operands with definite scale (fixed-point decimals). In Figure 2-13, p1 and s1 are the precision and scale of the first operand, and p2 and s2 are the precision and scale of the second operand.

Figure 2-13
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.



Informix-ESQL/C Programmer's Manual, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.