| SQL adaptation guide For SQL SERVER 2005, 2008, 2012 / Data dictionary | |
Informix® supports following character data types:
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)
SQL Server provides the following data types to store character data:
To store large text data (LOBs), Microsoft™ SQL Server version 2005 introduced the VARCHAR(MAX) type as a replacement for the old TEXT type.
The use of NCHAR, NVARCHAR character types is the same as CHAR, VARCHAR, TEXT respectively, except:
Note that SQL Server uses Byte Length Semantics to define the size of CHAR/VARCHAR columns, while NCHAR and NVARCHAR sizes are expressed in character units.
SQL Server defines the character encoding for CHAR and VARCHAR columns with the database collation. The database collation can be specified when creating a new database. Character strings are always stored in the UCS-2 encoding for NCHAR/NVARCHAR columns.
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.
According to the character set used by your application, you must either use CHAR/VARCHAR or NCHAR/NVARCHAR columns with SQL Server. If the charset is single-byte , you can use CHAR/VARCHAR columns. If the charset set is multibyte or Unicode (i.e. UTF-8), you must use NCHAR/NVARCHAR columns in SQL Server.
See also the section about Localization.
Make sure that the regional language settings for non-Unicode applications corresponds to the locale used by Genero programs.
Check that your database tables does not use CHAR or VARCHAR types with a length exceeding the SQL SERVER limit.
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 4gl programs with FGL_LENGTH_SEMANTICS=CHAR.When extracting a database schema from a SQL Server database, the schema extractor uses the size of the column in characters, not the octet length. If you have created a NCHAR(10 (characters) ) column a in SQL Server database, 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.
Do not forget to properly define the database client character set, which must correspond to the runtime system character set.
The SNC driver can work in char or in wide-char mode. The character size mode can be controlled by the following FGLPROFILE entry:
dbi.database.dbname.snc.widechar= { true | false }
By default the SNC database driver works in Wide Char mode (true).
The char mode can be used with applications defining character string columns with CHAR/VARCHAR/TEXT types. It is not mandatory (i.e. the wide-char mode could be used), but it appears that SQL Server behaves in different ways when wide-char bindings are used for CHAR/VARCHAR/TEXT columns.
When defining CHAR(n)/VARCHAR(n) columns in SQL Server, you specify n as a number of bytes, therefore you should use byte length semantics (the default) in Genero programs, with FGL_LENGTH_SEMANTICS=BYTE.
NCHAR / NVARCHAR and NTEXT SQL Server column data types must be used to store Unicode data. The wide-char mode should be used for applications using these types. And in such case, the runtime system must use a UTF-8 locale, with character length semantics (FGL_LENGTH_SEMANTICS=CHAR).
In wide-char mode, all string literals of an SQL statement are automatically changed to get the N prefix. Thus, you don't need to add the N prefix by hand in all of your programs. This solution makes your Genero code portable to other databases.
When using the ESM (EasySoft) database driver, string literals get the N prefix only if the current locale (LANG / LC_ALL) defines a multibyte code set such as .big5 or .utf8. String literals are not touched if the locale uses a single-byte character set.
When using the ESM (EasySoft) database driver, SQL Statements are prepared with SQLPrepare(), by using the current character set. EasySoft takes in charge the conversion from the client charset to UCS-2 before sending the SQL text to the server. ODBC SQL parameters with character string data are bound (SQLBindParameter) with the C type SQL_C_CHAR and with the SQL type SQL_W[VAR]CHAR (=UNICODE) type. As a result, the necessary character set conversion is taken in charge by EasySoft. However, it is critical to declare the correct client character set in EasySoft configuration files. The EasySoft client character set is defined by the "Client_CSet" parameter in odbc.ini.
When using the FTM (FreeTDS) database driver, string literals get the N prefix only if the current locale (LANG / LC_ALL) defines a multibyte code set such as .big5 or .utf8. String literals are not touched if the locale uses a single-byte character set.