CHAR and VARCHAR data types
Informix®
Informix supports the following character data types:
| Informix data type | Description |
|---|---|
CHAR(n) |
SBCS and MBCS character data (max is 32767 bytes) |
VARCHAR(n |
SBCS and MBCS character data (max is 255 bytes) |
NCHAR(n) |
Same as CHAR, with specific collation
order |
NVARCHAR(n |
Same as VARCHAR, with specific collation
order |
LVARCHAR(n) |
max size varies depending on the IDS version |
With Informix, both
CHAR/VARCHAR and NCHAR/NVARCHAR data types can be used to store
single-byte or multibyte encoded character strings. The only difference between
CHAR/VARCHAR and NCHAR/NVARCHAR is in how they use sorting:
N[VAR]CHAR types use the collation order, while [VAR]CHAR types
use the byte order.
The character set used to store strings in
CHAR/VARCHAR/NCHAR/NVARCHAR columns is defined by the DB_LOCALE environment
variable.
Informix uses Byte Length Semantics (the size
N that you specify in [VAR]CHAR(N) is expressed in bytes, not characters as in some
other databases)
Netezza®
Netezza supports following data types to store character data:
| Netezza data type | Description |
|---|---|
CHAR(n) |
Latin-9 character data, where n is specified in bytes (max is 64000 bytes) |
VARCHAR(n) |
Latin-9 character data, where n is specified in bytes (max is 64000 bytes) |
NCHAR(n) |
Unicode/UTF-8 character data, where n is specified in characters (max is 16000 characters) |
NVARCHAR(n) |
Unicode/UTF-8 character data, where n is specified in characters (max is 16000 characters) |
Netezza uses the Latin-9 (ISO-8859-15) code set
for CHAR/VARCHAR columns, and UTF-8 for NCHAR/NVARCHAR
columns.
CHAR/VARCHAR columns,
the client application character set (LC_ALL, LANG) must match the Latin-9/ISO-8859-15 character
set. When using NCHAR/NVARCHAR columns, the client application character set must
be UTF-8.Solution
When your application uses a Latin-9 / ISO-8859-15 locale, create tables with the
CHAR/VARCHAR SQL types. To store UNICODE (UTF-8) character strings, use the
NCHAR/NVARCHAR SQL types instead. In program sources you can use
CHAR/VARCHAR; these types can hold single and multibyte character sets, based on
the C POSIX locale.
latin9 database character set for
CHAR/VARCHAR types. Since character set conversion is not supported, your
application can only use Latin-9 or UTF-8.Table columns using a different character encoding than the database is not supported with Genero: All table columns must use the same character encoding defined at the database level.
Based on the FGLPROFILE entry dbi.database.dbname.ifxemul.nationalchars, the
CHAR and VARCHAR type names in SQL statements are converted to
CHAR/VARCHAR or to NCHAR/NVARCHAR Netezza types, to store UTF-8 character data.
When using a multibyte character set (such as UTF-8), define database columns as
NCHAR and NVARCHAR, with the size in character units, and use
character length semantics in BDL programs with FGL_LENGTH_SEMANTICS=CHAR.
When extracting a database schema from a Netezza
database, the fgldbsch schema
extractor uses the size of the column in characters, not the octet length. If you have
created a CHAR(10 (characters) ) column a in Netezza database using the UTF-8 character set, the .sch file will get a
size of 10, that will be interpreted by FGL_LENGTH_SEMANTICS as a number of bytes or characters.
With Netezza, it is not possible to defined the database client client character set: The locale used by programs must match the database locale.
See also the section about Localization.
CHAR/VARCHAR type translation can be controlled with the following FGLPROFILE
entries:dbi.database.dsname.ifxemul.datatype.char = { true | false }
dbi.database.dsname.ifxemul.datatype.varchar = { true | false }