Client Access ODBC: National Language Support (NLS) Considerations

Resolving The Problem

Note: This information applies only to V5R1 and later of Client Access Express, iSeries Access, and IBM i Access ODBC.

Result Set Data and Parameter Data

Character data stored in IBM OS/400 or IBM i5/OS must be converted between the CCSID associated with the data and the Microsoft Windows code page of the client running the ODBC application. Starting with V5R1, Client Access Express applications using ODBC can also request the data in Unicode rather than the Windows code page. Client Access Express ODBC now uses a direct conversion for both SBCS and DBCS character data. All conversions are done on the client.

The code page associated with OS/400 or i5/OS character data is defined when the object is created. The data-type specification for the CREATE TABLE and CREATE PROCEDURE statements allows a CCSID to be specified for character and graphic field types. If this value is not specified, the CCSID defaults to the CCSID of the job. For graphic field types, the CCSID defaults to the DBCS CCSID most closely related to the job CCSID. Unicode data can be stored in a graphic field, but CCSID 13488 must be specified. The CCSID of a column can be determined by issuing a DSPFFD command or by querying the system catalog tables (QADBIFLD). The CCSID of a stored procedure parameter can be determined by querying the QSYS2/SYSPARMS catalog table.

V5R1 ODBC complies to the ODBC 3.x specification and therefore supports Unicode. If the conversion is to or from the ODBC wide character type, the Unicode code page is used. If the requested conversion is to or from the standard ODBC character type, the conversion is done using a Windows ANSI code page.

Non-Unicode DBCS data fields (for example, fields that are GRAPHIC with a CCSID other than 13488) are described as the ODBC type SQL_CHAR with a byte length rather than a character length (V5R1 Service Pack SI02795 or later is required). Clients must be running a DBCS version of Windows to correctly bind this data type to an SQL_C_CHAR. Applications running on SBCS versions of Windows must bind to a wide character (Unicode). The native field type can be used to distinguish between SBCS and DBCS fields because both report as ODBC type SQL_CHAR.

The ANSI Code page is determined as follows:

o	The code page specified in the ODBC data source if provided.
o	The ANSI code page specified in the Client Access Properties if provided.
o	The value returned from a call to the Windows GetACP API. The value that Windows returns is set depending on the version of Windows installed and the default system locale setting.

Literals

The Microsoft driver manager calls the wide (Unicode) version of the API exported from the driver. This implies that ANSI code page override in the Client Access settings will always be ignored. The driver manager will convert the literal to Unicode, then pass a unicode string to Client Access ODBC. The default setting for V5R1 ODBC will convert the entire Unicode SQL statement, including literals, to the CCSID of the current ODBC connection's job. If the character in the literal is not supported in the code page associated with the job CCSID, Client Access will report an error.

OS/400 V5R1 and Client Access Express ODBC V5R1 can support Unicode SQL statements. When Unicode SQL statement support is enabled, the Unicode SQL statement received from the application is passed to IBM DB2 without any conversion. DB2 will process tokens representing string constants as Unicode (UCS-2) graphic strings without conversion to single-byte. This support must be enabled by setting the UNICODESQL connection property to "1".

MetaData

MetaData is information about the database objects. This includes information such as field names, table descriptions, table names, and so on. OS/400 extends the SQL specification by allowing identifiers (names) to have the following additional code points: x5B, x7C, x7B. This is a code point rather than a character. In US English (CCSID 37), the characters appear as '$', '@', '#'; however, the actual character represented by this code point varies according to the CCSID of the job accessing the data. This implies that an application using these code points will not run correctly on any job using a different CCSID or any system initialized to a different primary language.

Use of these code points is strongly discouraged. The ODBC specification allows no special characters in an identifier except the underscore ('_'). Some ODBC applications cannot handle these additional characters. The DB2 SQL Reference also discourages the use of object names that contain invariant characters:

Caution: $, @, #, and all other variant characters should not be used in identifiers because the code points used to represent them vary depending on the CCSID of the string in which they are contained. If they are used, unpredictable results may occur. For more information on variant characters, see the book National Language Support, SC41-5101-01.

To avoid problems with metadata conversion, follow the naming guidelines recommended by ODBC and the DB2 SQL Reference.

The OS/400 or i5/OS database server reports that all metadata is in the CCSID of the current job. If the table was created by a job running a different CCSID, the metadata can be interpreted incorrectly.

For example, assume that a table was created from a 037 CCSID user profile with one character field such as:

CREATE TABLE T1 (FIELD$1 CHAR(10) )

A user with an OS/400 user profile set up for CCSID 285 runs an ODBC program that issues SELECT * FROM T1. A description of the result set (or a catalog request) will return a field name of "FIELD£1". Because metadata is stored by codepoint rather than character, the x5B value appears as a different character in each CCSID.

If the user set up for CCSID 285 attempts to run a query using the field name "FIELD$1", it fails. A "SELECT "FIELD$1" FROM T1" fails with [IBM][Client Access ODBC Driver (32-bit)][DB2/400 SQL]SQL0206 - Column "FIELD$1" not in specified tables.

ODBC Translation DLL

ODBC defines an optional translation DLL, which allows translation of all data that passes between the driver and data source. Client Access allows you to register your translation DLL in the datasource or the corresponding connect string property. The Client Access ODBC driver will pass all result set data and parameter data to the translation DLL (the SQLBindxxx and SQLGetData APIs). See the ODBC specification for more detail.

Default CCSID Considerations

Many SQL functions (for example, CAST, scalar functions, literals and so on) rely on the default CCSID. The default CCSID is determined by the job CCSID attribute. The OS/400 database server program allows the job CCSID to be set when the connection is made. Client Access ODBC does not use this setting; therefore, the job CCSID is set to the value specified in the user profile used to make the ODBC connection (the current user). Applications and administrators can override the job CCSID by running CHGJOB commands (through exit programs or stored procedure calls).

Other

CCSID 65535 implies the data is in an unknown code page. The default settings for Client Access ODBC treat this type of data as binary. If the data source option, Convert binary data (CCSID 65535) to text, is checked, the data will be assumed to be in the Default CCSID (the job CCSID). True binary data (BLOB data type) is not affected.

Client Access ODBC uses conversion tables to convert between the source and target code pages. Common conversion tables are installed with Client Access. The default location of the tables is C:\Program Files\IBM\Client Access and they have a name similar to xxxxyyyy.tbl where xxxx and yyyy are the code page numbers in hexadecimal format. If Client Access Express ODBC requires a conversion table that is not installed locally it will attempt to contact the managing system to retrieve the conversion table. The managing system is defined in Operations Navigator. If Client Access Express ODBC is unable to retrieve the conversion table, an error is returned to the application. If history logging is enabled, the error is posted in the history log. The error message will include the CCSIDs of the requested conversion.

The correct version of Windows must be installed to correctly display the data. For example, DBCS data can be retrieved on any Windows client by using the appropriate Client Access ANSI code page overrides or by binding to a Unicode ODBC character string. This data can be manipulated and stored. The data cannot be displayed correctly unless the correct code page and character set are used.