CHARACTER data types

Informix^® supports the following character data types:

CHAR(N) with N<= 32767 bytes
VARCHAR(N[,M]) with N<=255 bytes
NCHAR(N) with N<= 32767 bytes
NVARCHAR(N[,M]) with N<=255 bytes
LVARCHAR(N), without the 255 bytes limit (max size varies according to IDS version)

In 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 for 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)

SQLite 3 provides the TEXT native data type with no strict size limitation. SQLite allows the CHAR(n), VARCHAR(n), NCHAR(n) and NVARCHAR(n) type names to be used, but actually stores the data in a TEXT native type.

SQLite treats empty strings as NOT NULL values like Informix.

Note: With the default BINARY collation, SQLite compares VARCHAR and CHAR values by taking trailing blanks into account. Informix always ignores trailing blanks when comparing CHAR/VARCHAR values.

SQLite supports only the UTF-8 character encoding. Thus, client applications must provide UTF-8 encoded strings.

Solution

The database interface supports character string variables in SQL statements for input (BDLUSING) and output (BDL INTO).

Important: With the default BINARY collation, CHAR and VARCHAR comparison in SQLite takes trailing blanks into account. As result, some queries returning rows with Informix may not return the same result set with SQLite. When creating a table in SQLite, you can change the default collation rule to force the database engine to trim trailing blanks before comparing CHAR/VARCHAR values, by specifying COLLATION RTRIM in the column definitions. When creating a table from a Genero program, if Informix emulation is enabled for the CHAR/VARCHAR types, the SQLite database driver adds automatically COLLATE RTRIM after the CHAR(N) or VARCHAR(N) type, to get the same comparison semantics as Informix.

Regarding character sets, the SQLite database driver automatically converts character strings used in the programs to/from UTF-8 for SQLite.

SQLite uses character length semantics: When you define a CHAR(20) and the database character set is multibyte, the column can hold more bytes/characters than the Informix CHAR(20) type, when using byte length semantics.

When using a multibyte character set (such as UTF-8), define database columns 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 SQLite database, the 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 SQLite database using the UTF-8 character set, the .sch file will get a size of 10, that will be interpreted according to FGL_LENGTH_SEMANTICS as a number of bytes or characters.

See also the section about Localization.