CHAR and VARCHAR data types

Informix®

Informix supports the following character data types:

Table 1. Informix character data types
Informix data type Description
CHAR(n) SBCS and MBCS character data (max is 32767 bytes)
VARCHAR(n[,m]) SBCS and MBCS character data (max is 255 bytes)
NCHAR(n) Same as CHAR, with specific collation order
NVARCHAR(n[,m]) 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.

The character set used by applications is defined by the CLIENT_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)

SAP HANA®

SAP HANA provides the following character data types:

  • VARCHAR(N) with N <= 5000 bytes, to store ASCII-7 character strings.
  • NVARCHAR(N) with N <= 5000 characters, to store UNICODE character strings.
  • ALPHANUM(N) with N <= 127 bytes, to store ASCII-7 alpha-numeric character strings.
  • SHORTTEXT(N), supporting text and string search features.
  • CHAR(N)/NCHAR(N): Not officially supported!

The SAP HANA VARCHAR data type must only be used to store ASCII-7 character strings. To store non-ASCII / locale dependent character strings (such as the UTF-8 codeset), you must use the NVARCHAR data type.

Note: The SAP HANA database does not officially support the CHAR and NCHAR datatypes. See SAP HANA documentation for more details.

Solution

Because SAP HANA VARCHAR(N) can only store ASCII-7 characters and does not recommend to use CHAR/NCHAR types, Informix CHAR(N), VARCHAR(N) or LVARCHAR(N) types must be mapped to SAP HANA NVARCHAR(N) when using UTF-8, or a single-byte encoding like ISO-8859-15. When the application is pure English and only requires ASCII-7 storage, you can use the SAP HANA VARCHAR(N) SQL type.

Important: Since SAP HANA does not officially support the CHAR/NCHAR types, all character columns must be SAP HANA VARCHAR (if ASCII-7) or NVARCHAR (if non-ASCII). As the semantics of VARCHAR types are different regarding blank padding, you will get different behavior, when comparing with string values containing trailing blanks.
Depending on the FGLPROFILE entry dbi.database.dbname.ifxemul.nationalchars, the CHAR and VARCHAR type names in SQL statements are converted to VARCHAR (for ASCII-7 data storage), or NVARCHAR (for ISO-8859-? or UTF8 data storage).
Important: The dbi.database.dbname.ifxemul.nationalchars FGLPROFILE entry defaults to false, and CHAR/VARCHAR/TEXT SQL type names will be converted to NVARCHAR/CLOB SAP HANA types for simple ASCII-7 storage. Force this entry to true, if your application is using an ISO-8859-? or UTF-8 locale.

When extracting a database schema from a SAP HANA database, the fgldbsch schema extractor uses the size of the column in characters, not the octet length. If you have created a NVARCHAR(10 (characters) ) column a in SAP HANA database using the UTF-8 character set, the .sch file will get a size of 10, that will be interpreted depending on FGL_LENGTH_SEMANTICS as a number of bytes or characters.

Check that your database schema does not use CHAR or VARCHAR types with a length exceeding the SAP HANA limits especially as the Informix CHAR type has a very long size limit compared to SAP HANA NVARCHAR.

The SAP HANA database interface of Genero automatically converts from/to the application locale to the database locale. Therefore, no database client locale configuration is required with SAP HANA.

See also the section about Localization.