OpenDBX/DBMS Datatypes

Data type support

The tables below lists the data types supported by the OpenDBX database backend implementations preceded by explanations of the types. Each cell contains values for language and type support, e.g. MySQL supports the BOOLEAN keyword but maps it to a 8 bit integer type whereas PostgreSQL supports both, the keyword and the type.

Integer types

• BOOLEAN: 1 bit type from 0 to 1
• SMALLINT: 16 bit type from -32768 to 32767
• INTEGER: 32 bit type from -2147483648 to 2147483647
• BIGINT: 64 bit type from -9223372036854775808 to 9223372036854775807

Database	BOOLEAN	SMALLINT	INTEGER	BIGINT
Firebird	no / no	yes / yes	yes / yes	yes / yes
MySQL	yes [1] / no [2]	yes / yes	yes / yes	yes / yes
Oracle	no / no	yes / no [6]	yes / no [6]	yes / no [6]
PostgreSQL	yes / yes	yes / yes	yes / yes	yes / yes
SQL Server	no / no	yes / yes	yes / yes	yes / yes
SQLite	yes / no [3]	yes / no [3]	yes / no [3]	yes / no [3]
SQLite3	yes / no [4]	yes / no [4]	yes / no [4]	yes / yes
Sybase ASE	no / no	yes / yes	yes / yes	yes / no [5]

[1] Supported since MySQL 4.1.0, before only BOOL was recognized

[2] MySQL maps BOOLEAN to TINYINT(1)

[3] SQLite stores integer types as strings in CLOBs

[4] SQLite3 always use BIGINTs for storing integer types

[5] Sybase use its NUMERIC type to support BIGINT values

[5] Oracle maps all integer types to its NUMBER type

Real BOOLEANs are only supported by PostgreSQL while MySQL converts BOOLEAN to tinyint, SQLite to CLOB type and SQLite3 to BIGINT type. For compatibility use CHAR(1) or SMALLINT instead - whatever fits best for your needs.

Decimal and floating point types

• DECIMAL: Exact number with arbitrary precision and scale, e.g. DECIMAL(5,2) supports -999.99 to 999.99
• REAL: 32 bit approximate type from -3.40e+38 to -1.17e-38 and from 1.17e-38 to 3.40e+38
• FLOAT(p): Up to 64 bit approximate type with user defined precision (mantissa) from 1 to 53
• DOUBLE PRECISION: 64 bit approximate type from -1.79e+308 to -2.22e-308 and from 2.22e-308 to 1.79e+308

Database	DECIMAL(p,s)	REAL	FLOAT(p)	DOUBLE PRECISION
Firebird	yes / yes	yes / yes	yes / no [1]	yes / yes
MySQL	yes [2] / yes	yes / yes	yes / yes [2]	yes / yes
Oracle	yes / yes	yes / no [5]	yes / no [5]	yes / no [5]
PostgreSQL	yes / yes	yes / yes	yes / yes	yes / yes
SQL Server	yes / yes	yes / yes	yes / yes	yes / yes
SQLite	yes / no [3]	yes / no [3]	yes / no [3]	yes / no [3]
SQLite3	yes / no [4]	yes / no [1]	yes / no [1]	yes / yes
Sybase ASE	yes / yes	yes / yes	yes / yes	yes / yes

[1] Implementations always use a double value

[2] Supported since MySQL 3.23

[3] SQLite stores all values as CLOBs

[4] SQLite3 returns for DECIMAL the same type as for DOUBLE PRECISION

[5] Oracle maps all floating point types to its NUMBER type

Character types

• CHARACTER: Fixed length character data type
• NATIONAL CHARACTER: Fixed length national character data type
• CHARACTER VARYING: Variable length character data type
• NATIONAL CHARACTER VARYING: Variable length national character data type

The maximum length of these types is at least 255 bytes which results in 255 or less characters because national characters can use more than one byte. You should consider the CLOB type if you need a character type with more space.

Database	CHARACTER	NATIONAL CHARACTER	CHARACTER VARYING	NATIONAL CHARACTER VARYING
Firebird	yes / yes	yes / no [1]	yes / yes	yes / no [1]
MySQL	yes / yes	yes [2] / no [3]	yes / yes	yes [2] / no [3]
Oracle	yes / yes	yes / no [1]	yes / yes	yes / no [1]
PostgreSQL	yes / yes	yes / no [1]	yes / yes	yes / no [1]
SQL Server	yes / yes	yes / no [4]	yes / yes	yes / no [4]
SQLite	yes / no [5]	yes / no [5]	yes / no [5]	yes / no [5]
SQLite3	yes / no [5]	yes / no [5]	yes / no [5]	yes / no [5]
Sybase ASE	yes / yes	yes / no [1]	yes / no [1]	yes / no [1]

[1] Implementations map NATIONAL CHARACTER (VARYING) to CHARACTER (VARYING)

[2] Supported since MySQL 4.1.0 with UTF-8 character set as default

[3] MySQL maps NATIONAL CHARACTER (VARYING) to CHARACTER VARYING with UTF-8 character set

[4] The FreeTDS and Sybase dblib implementations (using OpenDBX mssql backend) segfault when retrieving NATIONAL CHARACTER (VARYING) columns

[5] SQLite and SQLite3 map all varieties of CHARACTER types to CLOBs using the UTF-8 character set

Many database implementation support abbreviations like CHAR or VARCHAR but you should use the SQL2003 names for maximum compatibility. For the same reason, using a larger maximum of (NATIONAL) CHARACTER VARYING as supported by many databases isn't recommended.

Large object types

• CLOB: Large variable length character data type with a limit of ca. 32 KB
• NCLOB: Large variable length national character data type
• XML: Variable length character data type for XML data
• BLOB: Variable length binary data type

Database	CLOB	NCLOB	XML	BLOB
Firebird	no / no	no / no	no / no	yes / no
MySQL	no [1] / yes	no / no	no / no	yes / yes
Oracle	yes / no [5]	yes / no [5]	no [4] / no	yes / no
PostgreSQL	no [1] / yes	no / no	no / no	no / no
SQL Server	no [1] / yes	?	?	?
SQLite	yes / yes	no [2] / no	no / no	no / no
SQLite3	yes / yes	no [2] / no	no / no	yes / yes
Sybase ASE	no [1] / yes	no / no	no / no	no [3] / yes

[1] Implementations use TEXT instead of CLOB

[2] Implementations support the long form NATIONAL CHARACTER LARGE OBJECT

[3] Sybase uses IMAGE instead of BLOB

[4] Oracle use XMLTYPE instead of XML

[5] Oracle CLOBs converted to strings have a limit of 4000 bytes instead of 32 KB

Temporal types

• TIME: Time data type in HH:mm:ss format
• TIME WITH TIME ZONE: Time data type in HH:mm:ss+hh or HH:mm:ss-hh format
• TIMESTAMP: Combined date and time data type in YYYY-MM-DD HH:mm:ss format
• TIMESTAMP WITH TIME ZONE: Combined date and time data type in YYYY-MM-DD HH:mm:ss+hh or YYYY-MM-DD HH:mm:ss-hh format
• DATE: Date data type in YYYY-MM-DD format
• INTERVAL: Time or date interval

Database	TIME	TIME WITH TIME ZONE	TIMESTAMP	TIMESTAMP WITH TIME ZONE	DATE	INTERVAL
Firebird	yes / yes	no / no	yes / yes	no / no	yes / yes	no / no
MySQL	yes / yes	no / no	no [1] / yes	no / no	yes / yes	no / no
Oracle	no / no	no / no	yes / yes	yes / yes	yes / yes	yes / yes
PostgreSQL	yes / yes	yes / yes	yes / yes [2]	yes / yes	yes / yes	yes / yes
SQL Server	yes / no [4]	no / no	no [1] / yes	no / no	yes / no [4]	no / no
SQLite	yes / no [3]	yes / no [3]	yes / no [3]	yes / no [3]	yes / no [3]	no / no
SQLite3	yes / no [3]	yes / no [3]	yes / no [3]	yes / no [3]	yes / no [3]	no / no
Sybase ASE	yes / no [4]	no / no	no [1] / yes	no / no	yes / no [4]	no / no

[1] Implementations use DATETIME instead of TIMESTAMP as defined by the SQL standard

[2] PostgreSQL 7.3 and later is compliant to TIMESTAMP as defined by the SQL standard

[3] SQLite and SQLite3 store temporal values as CLOBs

[4] MS SQL Server and Sybase ASE convert TIME and DATE values to its DATETIME type and therefore they are always printed in YYYY-MM-DD HH:mm:ss format

Misc types

• ARRAY: Variable length set of the same data type
• MULTISET: Set of different data types
• DATALINK: Link to external (file) resource

Database	ARRAY	MULTISET	DATALINK
Firebird	no / no [1]	no / no	no / no
MySQL	no / no	no / no	no / no
Oracle	no / no	no / no	no / no
PostgreSQL	yes / yes	no / no	no / no
SQL Server	no / no	no / no	no / no
SQLite	no / no	no / no	no / no
SQLite3	no / no	no / no	no / no
Sybase ASE	no / no	no / no	no / no

[1] Firebird / Interbase only support a non-standard implementation of the ARRAY type

These types are so rarely implemented that you should not even consider using them if you want to write portable code.

---

Back to Overview