Error -21005 when using ODBC to select data from a database.

Cause

The most common reason for this error is having character outside the codeset specified in the database.

The environment variable DB_LOCALE is used to specify the database locale. When a database is created, the codeset used is defined by this variable, which means that only characters included in that particular codeset would be allowed.

The default value for DB_LOCALE is en_US.8859-1. This locale value corresponds to "English" language, "US" territory and "8859-1" codeset.

The 8859-1 codeset has a limited number of characters, some symbols like the Euro or single quotation mark are not allowed (other codeset like CP1252 or UNICODE do allow these symbols)

If the client machine uses a different codeset than the one used in the database, a codeset conversion must be performed by the client communication libraries (GLS library) to correctly transform between the two codesets.

An English Windows machine by default uses the 1252 codeset, which means a conversion would be needed. If at some point during this conversion the GLS libraries find a character that is not allowed in any of the two codesets it would fail with a GLS error.

Since CSDK version 2.80, the ODBC driver is Unicode enabled, this means all the data the driver handles has to be in Unicode format. This means that a extra conversion has to be done:

When retrieving data from the database the conversion is as follows:

  Codeset in DB_LOCALE -> Unicode -> Codeset in CLIENT_LOCALE

When inserting data:

  Codeset in CLIENT_LOCALE -> Unicode -> Codeset in DB_LOCALE

Error -21005 would appear when doing a select if there was an error in conversion.
If for some reason the database contains characters outside the allowed range (e.g.: a 'Euro' symbol in a 8859-1 database) the GLS functions can't convert the character because it is not supposed to be there in the first place.

Codeset conversion.

The mapping between different codesets is defined in the CV files in the $INFORMIXDIR directory.

Assume a database is created with the default 8859-1 codeset, and a connection is to be made from an English Windows (CP1252)

04e4 correspond to CP1252
0333 correspond to 8859-1

(The 'Registry' file inside the $INFORMIXDIR\gls\cv3 directory contains the different representations of the codeset names, from decimal,text and hexadecimal)

The conversion file between 1252 and 8859-1 looks like :

     ---- 04e40333.cv ------
     <source_version>  3
     <modified_date>  "05-04-2004"
     <source_codeset>    "1252"
     <target_codeset>    "8859-1"

     \x00...\xff \x00... # Default everything onto itself
     \x80    \x1a error      # No Euro-sign in 8859-1
     ...
     \x90 \x1a error # undefined-char-00006
     \x91 \x1a error # left single quotation mark
     \x92 \x1a error # right single quotation mark
     ...
     ---- 04e40333.cv ------

The lines inside specify which and how the different codes are mapped between the two codesets

The first line indicates that all the codes from 0x00 to 0xFF should be converted to the same value for a 8859-1 codeset

    \x00...\xff \x00... # Default everything onto itself

The rest of the lines indicate what values are not allowed, and that it should raise an error if one is found in the conversion.

the Euro symbol is allowed in the 1252 codeset and has the code 0x80, but is not allowed in 8859-1, so it has to raise an error.

    \x80    \x1a error      # No Euro-sign in 8859-1



The conversion file between CP1252 and 8859-15 (which does support the Euro) :

     ---- 04e4e02f.cv ------
     <source_version> 2
     <modified_date> "05-04-2004"
     <source_codeset>    "1252"
     <target_codeset>    "8859-15"

     \x00...\xff \x00... # Default everything onto itself
     \x82 \x1a error # single low-9 quotation mark
     ...
     \x91 \x1a error # left single quotation mark
     \x92 \x1a error # right single quotation mark
     ...
     \x80    \xa4            # euro symbol
     \x8a    \xa6            # latin capital letter s with caron
     ...
     ---- 04e4e02f.cv ------


In this case there is a conversion between the Euro in CP1252 to the 8859-15

    \x80    \xa4            # euro symbol

In 1252 the code for the Euro is 0x80 when in 8859-15 it is 0xA4

This GLS conversion is what makes it possible for clients with different codesets to share the same data.
A CP1252 client would insert a Euro (0x80), the GLS functions would convert the (0x80) into a (0xA4) which is the value for a Euro symbol in the 8859-15 codeset.


If a second client, this time with a codeset of 858 inserts a euro (0xD5) it would also be converted to the correct (0xA4) for the 8859-15 codeset. The conversion works in both ways, when a client with a 858 codeset select the Euro symbol from the database, the (0xA4) gets converted to (0xD5)

The conversion file between 858 and 8859-15 (which does support the Euro) :

     ---- 035ae02f.cv -----
     <source_version> 1
     <modified_date> "05-07-2004"
     <source_codeset>    "858"
     <target_codeset>    "8859-15"

     \x00...\xff \x00... # Default everything onto itself
     ...
     \xd4 \xc8 # latin capital letter e with grave
     \xd5 \xa4       # euro
     \xd6 \xcd # latin capital letter i with acute
     ---- 035ae02f.cv -----


Environment settings

The two variables that control the GLS conversion are CLIENT_LOCALE and DB_LOCALE.
CLIENT_LOCALE must contain the codeset of the Client Operating system and DB_LOCALE the codeset of the database.

The gls functions use these two values to know how the conversion has to be done.
If both codesets are the same, the functions assume there is no need to do any conversion, so the data would be directly passed to the server. This could cause corruption if the data is not in the correct format.

Let's say there is an en_US.88591 database, so the DB_LOCALE is set to en_US.8859-1.
In a Windows machine with CP1252 as the codeset, assume the CLIENT_LOCALE is set to to en_US.8859-1 by mistake.

The characters to be inserted are from a 1252 codeset, but the GLS library does not know this ( The CLIENT_LOCALE would need setting correctly ). If a Euro (0x80) is inserted , because there is no conversion that takes place, the 0x80 would be directly inserted into the database - causing corruption. (A code outside the allowed range)

If then, a select of this data is attempted with a client like ODBC (which would always do a GLS conversion to Unicode) the -21005 error would appear.