Numeric data types
Informix®
Informix supports several data types to store numbers:
| Informix data type | Description |
|---|---|
SMALLINT |
16 bit signed integer |
INTEGER |
32 bit signed integer |
BIGINT |
64 bit signed integer |
INT8 |
64 bit signed integer (replaced by
BIGINT) |
DECIMAL |
Equivalent to DECIMAL(16) |
DECIMAL(p) |
Floating-point decimal number (max precision is 32) |
DECIMAL(p,s) |
Fixed-point decimal number (max precision is 32) |
MONEY |
Equivalent to DECIMAL(16,2) |
MONEY(p) |
Equivalent to DECIMAL(p,2) (max precision is
32) |
MONEY(p,s) |
Equivalent to DECIMAL(p,s) (max precision is
32) |
REAL / SMALLFLOAT |
32-bit floating point decimal (C float) |
DOUBLE PRECISION / FLOAT[(n)] |
64-bit floating point decimal (C double) |
SQLite
SQLite 3 supports INTEGER (8 byte integer) and REAL (8 byte
floating point) as native types to store numbers, but allows also synonyms:
| Supported synonyms | SQLite type affinity |
|---|---|
INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT,
UNSIGNED BIG INT, INT2, INT8 |
INTEGER (8 bytes!) |
REAL, DOUBLE, DOUBLE PRECISION, FLOAT |
REAL (8 bytes!) |
DECIMAL(p,s), NUMERIC |
NUMERIC (based on
REAL) |
Exact decimal types like DECIMAL(p,s) may be stored as
floating point numbers (REAL), INTEGER or TEXT
types, according to the type affinity selected by SQLite. When converted to floating point type,
data loss and rounding rule differences are possible with SQLite.
Solution
Informix numeric types are not translated by the SQLite database driver: The numeric types are used as is when creating tables, since SQLite supports a wide range of type synonyms.
Since SQLite 3 does not have exact decimal types like DECIMAL(p,s), you must pay
attention to the rounding rules and data loss when using numbers with many significant digits.
Arithmetic operations like division have different results than with Informix. It is better to fetch the original column value into a
DECIMAL variable, and do arithmetic operations in the application program.
Avoid using DECIMAL type in FGL or SQL: Due to the implementation
differences in Informix SQL / Genero BDL and the native SQL type, such data type is not recommended.
Always specify a precision and scale with [(p)]DECIMAL(p,s).
dbi.database.dsname.ifxemul.datatype.smallint = { true | false }
dbi.database.dsname.ifxemul.datatype.integer = { true | false }
dbi.database.dsname.ifxemul.datatype.bigint = { true | false }
dbi.database.dsname.ifxemul.datatype.int8 = { true | false }
dbi.database.dsname.ifxemul.datatype.decimal = { true | false }
dbi.database.dsname.ifxemul.datatype.money = { true | false }
dbi.database.dsname.ifxemul.datatype.float = { true | false }
dbi.database.dsname.ifxemul.datatype.smallfloat = { true | false }