CHAR and VARCHAR data types

Informix®

Informix supports the following character data types:

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)

Microsoft™ SQL Server

Microsoft SQL Server supports following data types to store character data:

To store large text data (LOBs), Microsoft SQL Server (2005) provides the VARCHAR(MAX)/NVARCHAR(MAX) type as a replacement for the old TEXT/NTEXT types. See TEXT and BYTE (LOB) types for more details.

The use of NCHAR, NVARCHAR character types is the same as CHAR, VARCHAR respectively, except:

• For NCHAR / NVARCHAR, the character encoding is UCS-2 or UTF-16, depending if the database collation is using the SC option.
• The length N in N[VAR]CHAR(N) defines a number of byte-pairs, not bytes. For UCS-2 this corresponds to a number of characters. But with UTF-16 there can be surrogate pairs using 4 bytes (2 byte-pairs).
• The maximum size of NCHAR(N) and NVARCHAR(N) column is 4000 byte-pairs, compared to 8000 bytes for CHAR/VARCHAR using a single-byte character set.
• Unicode string literals are specified with a leading N. For example: N'日本語'
• The LIKE statement behaves differently with CHAR and NCHAR columns when using the N prefix before the search pattern.

Note that SQL Server uses Byte Length Semantics to define the size of CHAR/VARCHAR columns, while NCHAR and NVARCHAR sizes are expressed in byte-pair units, which for most cases (UCS-2) corresponds to a number of characters (but this is not true for UTF-16 characters encoded in surrogate pairs).

SQL Server defines the character encoding for CHAR and VARCHAR columns with the database collation, specified when creating a new database. When using the _UTF8 modifier, character strings are UTF-8 encoded in CHAR/VARCHAR columns. In NCHAR/NVARCHAR columns, character strings are always encoded in UCS-2 or UTF-16. UTF-16 is used when the DB collation has the SC option.

Automatic charset conversion is supported by SQL Server between the client application and the server. The client charset is defined by the Windows® operating system, in the language settings for non-Unicode applications.

Solution

Check that your database tables does not use CHAR or VARCHAR types with a length exceeding the limits of SQL Server character types.

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.

Make sure that the regional language settings for non-Unicode applications corresponds to the locale used by Genero programs. For more details about program locale settings, see Localization.

The widechar mode of SQL Server drivers defines the ODBC SQL parameters and fetch buffers bindings for character strings (SQL_CHAR or SQL_WCHAR), as well as automatic adding of N prefix in SQL string literals. To get best behavior and performance of SQL Server, the widechar mode should be enabled when using NCHAR/NVARCHAR columns, and it should be disabled when using CHAR/VARCHAR columns (ref: FGL-315).

By Default, with all SQL Server ODI drivers, when not setting the FGLPROFILE *.widechar option to false, the widechar mode is automatically enabled, if the application locale is multibyte (UTF-8) and FGL_LENGTH_SEMANTICS=CHAR. Otherwise, with SBCS locale (ISO-8859-15) or MBCS locale (UTF-8/BIG5) and BYTE length semantics and SQL Server 2019 + DB collation is *_UTF8, the widechar mode defaults to false. If the SQL Server version is older than 2019 or the DB collation is NOT *_UTF8, for backward compatibility, the widechar mode is enabled by default when the application locale is MBCS.

When extracting a database schema from an SQL Server database, the fgldbsch schema extractor uses the size of the column in byte-pair units, not the byte length. If you have created an NCHAR(10) column in an SQL Server database, the .sch file will get a size of 10, that will be interpreted as a number of bytes or characters, depending on FGL_LENGTH_SEMANTICS.

dbi.database.dsname.ifxemul.datatype.char = { true | false }
dbi.database.dsname.ifxemul.datatype.varchar = { true | false }

Automatic conversion of CHAR/VARCHAR type names

By default, in DDL statements executed by programs (such as CREATE TABLE), the CHAR, VARCHAR and LVARCHAR type names are left untouched.

dbi.database.dsname.ifxemul.nationalchars = true 

Using the SNC driver

According to the char or widechar mode, the SNC driver will do the appropriate ODBC bindings, using SQL_CHAR/SQL_VARCHAR or SQL_WCHAR/SQL_WVARCHAR SQL types, and let the MS ODBC driver do the charset conversions when binding with SQL_C_CHAR for the C type, or do the appropriate charset conversions when using SQL_C_WCHAR for the C type.

dbi.database.dsname.snc.widechar= { true | false }

Using the ESM driver

The database client application codeset needs to be defined when using the ESM driver.

Client_CSet   = UTF-8
Server_UCSet  = UTF-16LE

To support all UNICODE characters when using UTF-8 with NCHAR/NVARCHAR columns, you need to define Client_CSet=UTF-8 and Server_UCSet=UTF-16LE.

According to the char or widechar mode, the ESM driver will do the appropriate ODBC bindings, using SQL_CHAR/SQL_VARCHAR or SQL_WCHAR/SQL_WVARCHAR as SQL type and SQL_C_CHAR as C type. The Easysoft ODBC driver will then do the appropriate character set conversions for SQL_C_CHAR data.

dbi.database.dsname.esm.widechar= { true | false }

Using the FTM driver

The database client application codeset needs to be defined when using the FTM driver.

ClientCharset = UTF-8

dbi.database.dsname.ftm.widechar= { true | false }