To send and receive data to the database server according to the locale of your application, you should configure the database locale properly. Both database client locale and application locale settings must match.
The database server locale can be different from the database client locale. If database server uses UTF-8 encoding, any encoding can be used on a database client. If database server uses an encoding other than UFT-8, the database client locale should match the database server locale to store data in the database correctly.
If the db client locale does not match the db server locale then the following behavior can occur:
Runtime errors (in some cases can be fixed by specifying the database character set in driver settings).
Data stored in the database will use the server locale. This can result in data written incorrectly into the database if the character set is not configured on the database client.
Automatic character set conversion can be performed by database driver which can lead to data written to the database using the wrong encoding.
Automatic character set conversion can occur on server side, which can also lead to data written to the database using the wrong encoding, or potential data loss.
Each database software has its own client character set configuration. The table below lists the supported database clients and the settings required to configure locale:
|
Database |
Settings |
|
IBM® Informix® |
The client locale is defined by the CLIENT_LOCALE environment variable. For backward compatibility, if CLIENT_LOCALE is not defined, other settings are used if defined (DBDATE, DBTIME as well as standard LC_* variables).
|
|
IBM DB2® |
The client locale is defined by the DB2CODEPAGE environment variable. You cat set this variable with the db2set command. If DB2CODEPAGE is not set, DB2 uses the operating system code page on Windows and the LANG/LC_ALL locale setting on UNIX®. If you are using UTF-8 locale on Windows, set DB2CODEPAGE to 1208.
|
|
Oracle®
|
The client locale settings can be set using environment variables like NLS_LANG, or after connection, using the ALTER SESSION instruction.
|
|
ODBC® |
The character set used by the database client is defined using the charset ODBC DSN configuration parameter. If this parameter is not set, it ASCII will be used by default.
|
|
Microsoft SQL Server® |
For MSV and SNC drivers on Windows, the database client locale is defined in the language settings for non-Unicode applications. The current ANSI code page (ACP) is used by the SQL Server client.
When using the FreeTDS driver, the client character set is defined using the client charset parameter in freetds.conf file, or the ClientCharset parameter in the DSN of the odbc.ini file.
When using the EasySoft driver, the client character set is defined using the Client_CSet parameter in the DSN of the odbc.ini file. 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® |
You can set the client locale using the PGCLIENTENCODING environment variable, with the client_encoding configuration parameter in postgresql.conf file, or after connection, with the SET CLIENT_ENCODING instruction. See the pg_conversion system table in PostgreSQL documentation for available character set conversions.
|
|
MySQL® |
The client locale is defined using the default-character-set option in the MySQL configuration file. The character set could also be changed by program after the connection, using the SET NAMES or SET CHARACTER SET statements, but it is not supported: The driver needs to know the character set at connection initialization, and you would have to add this statement in all your programs.
|
For details on setting encoding on database server, see database vendor documentation.