Database client settings

This section describes the settings defining the locale for the database client.

Each database software has its own client character set configuration.

Important:

In order to properly send/receive character string data to/from the database server, the database client locale and application locale settings must match.

Table 1. Database client settings defining the character set locale
Database Client Locale settings Notes
Oracle® database server

The database client locale must be set with NLS_LANG environment variable.

By default, the client locale is set from the database server locale.

The locale can also be defined after connection, with the ALTER SESSION instruction. However, this is not supported by Genero BDL.

IBM® Informix®

The database client locale is defined by the CLIENT_LOCALE environment variable, and DB_LOCALE must be set to the database locale.

If CLIENT_LOCALE is not defined, other settings are used when defined (DBDATE / DBTIME / GL_DATE / GL_DATETIME, as well as standard LC_* variables).

IBM DB2® LUW

The database client locale is defined by the DB2CODEPAGE profile variable.

The DB2CODEPAGE variable can be set with the db2set command. However, you usually do not need to set this variable: If DB2CODEPAGE is not defined, DB2 uses the operating system code page on Windows® and the LANG/LC_ALL locate setting on UNIX™.

When using a UTF-8 locale on Windows, DB2CODEPAGE must be set to 1208.

IBM Netezza Performance Server®

With IBM Netezza, there is no configuration setting to define database client locale.

When using CHAR/VARCHAR columns, the application character set (LC_ALL, LANG) must match the Latin-9 / ISO-8859-15 character set. When using NCHAR/NVARCHAR columns, the application character set must be UTF-8.

Microsoft® SQL Server with SNC driver (Microsoft ODBC).

On Windows platforms, the database client locale is defined by the language settings for non-Unicode applications. The current ANSI code page (ACP) is used by the SQL Server client and the Genero runtime system.

On Linux® platforms, the database client locale is always UTF-16. The ODI driver uses the MS ODBC Wide Char API, and makes the required character set conversions between the application locale and UTF-16.

See Microsoft ODBC documentation for more details regarding character set configuration.

Microsoft SQL Server with FTM driver (FreeTDS).

The database client character set is defined by the client charset parameter in freetds.conf, or with the ClientCharset parameter in the DSN of the odbc.ini file.

See FreeTDS documentation for more details regarding character set configuration.

Microsoft SQL Server with ESM driver (Easysoft).

The database client character set is defined by the Client_CSet parameter in the DSN of the odbc.ini file.

Depending on the application locale and SQL Server CHAR/VARCHAR or NCHAR/NVARCHAR usage, you might also need to define the Server_CSet and/or the Server_UCSet parameters.

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.

When using CHAR/VARCHAR types in the database and when the database collation is different from the client locale, you must also set the Server_CSet parameter to an iconv name corresponding to the database collation. Some examples:
  • If Client_CSet=ISO-8859-15 and the db collation is Latin1_* (=CP1252), you must set Server_CSet=WINDOWS-1252 (otherwise, the characters €, Š, š, Ž, ž, Œ, œ, Ÿ which are encoded differently
  • If Client_CSet=BIG5 and the db collation is Chinese_Taiwan_Stroke_BIN, you must set Server_CSet=BIG5HKSCS.
PostgreSQL

The database client locale must be set with the PGCLIENTENCODING environment variable, or with the client_encoding configuration parameter in postgresql.conf.

The default is to use the database encoding.

After the database connection, the locale can be set with the SET CLIENT_ENCODING instruction. This is not recommended with Genero BDL.

Check the pg_conversion system table for available character set conversions.

Oracle® MySQL and MariaDB

The database client locale is defined by the default-character-set option in the MySQL configuration file.

MySQL/MariaDB support the SET NAMES SQL instruction, to change the character set after connecting to the database. However, this is not supported with Genero: The driver needs to know the character set at connection initialization. Use the default-character-set configuration option.

SAP HANA

No database client locale configuration is required with SAP HANA: The database driver makes the appropriate charset conversions when needed.

CHAR/NCHAR are not supported by SAP HANA.

VARCHAR columns store only ASCII-7 characters and therefore can only be used with an ASCII/POSIX application locale.

NVARCHAR columns store UNICODE characters, and can be used with a single-byte or UTF-8 application locale.

SQLite No database client locale configuration is required with SQLite: The database driver makes the appropriate charset conversions when needed. SQLite databases use UTF-8 encoding. If the locale used by the runtime system (LANG/LC_ALL) is not UTF-8, Genero will do the appropriate character set conversions.