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.

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®

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®

N/A

With IBM Netezza, there is no configuration setting to define database client: When using CHAR/VARCHAR columns, the application character set (LC_ALL, LANG) and the database character set defined by CREATE DATABASE must match. 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 or the Server_UCSet parameters.

If you want to support all possible UNICODE characters (not only UCS-2), 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. For example, if Client_CSet=BIG5 and the db collation is Chinese_Taiwan_Stroke_BIN, you must set Server_CSet=BIG5HKSCS, otherwise invalid data will be returned from the server.

PostgreSQL

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

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.

The character set can also be changed at runtime with the SET NAMES or SET CHARACTER SET statements, but this is not supported: The driver needs to know the character set at connection initialization.

SAP ASE

By default, the database client character set is defined by the operating system locale where the database client runs.

On Windows, it is the ANSI code page of the login session (can be overwritten by setting the LANG environment variable).

On UNIX it is defined by the LC_CTYPE, LC_ALL or LANG environment variables.

You may need to edit the $SYBASE/locales/locales.dat file, to map the OS locale name to a known ASE character set.

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

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.