Using portable data types
Only a limited set of data types are really portable across several database engines.
The ANSI SQL specification defines standard data types, but for historical
reasons most databases vendors have implemented native (non-standard) data
types. You can usually use a synonym for ANSI types, but the database server
will uses the native types behind the scenes. For example, when you create
a table with an INTEGER
column in Oracle, the native
NUMBER
data type is used.
In your programs, avoid data types that do not have a native equivalent
in the target database. This includes simple types like floating point numbers,
as well as complex data types like INTERVAL
. Numbers may
cause rounding or overflow problems, because the values stored in the database
have different limits. For the DECIMAL
types, always use the
same precision and scale for the program variables and the database columns.
CHAR(n)
VARCHAR(n)
BIGINT
INTEGER
SMALLINT
DECIMAL(p,s)
DATE
DATETIME HOUR TO MINUTE
DATETIME HOUR TO SECOND
DATETIME HOUR TO FRACTION(n)
DATETIME YEAR TO MINUTE
DATETIME YEAR TO SECOND
DATETIME YEAR TO FRACTION(n)
TEXT
/BYTE
(for LOBs)
Database Server Type | Data type topic |
---|---|
IBM® DB2® LUW (UNIX™) | Data type conversion table: Informix to DB2 |
IBM Informix® | Genero BDL is base on Informix SQL data types... |
IBM Netezza® | Data type conversion table: Informix to Netezza |
Microsoft™ SQL Server | Data type conversion table: Informix to SQL Server |
Oracle® MySQL / MariadDB | Data type conversion table: Informix to MySQL |
Oracle Database Server | Data type conversion table: Informix to Oracle |
PostgreSQL | Data type conversion table: Informix to PostgreSQL |
SAP HANA® | Data type conversion table: Informix to SAP HANA |
SQLite | Data type conversion table: Informix to SQLite |