When working in a multi-character set environment, character set conversion issues can occur and it can be difficult to determine where the conversion issue occurred.
When encountering character set conversion issues for client APIs, examine the database and connection options and properties that control character set conversion.
There are two categories into which conversion issues can be placed. The first involves sending data in the wrong format to the client API. Although this cannot happen with Unicode APIs, it is possible with all other client APIs, and results in garbled data.
The second category of issue involves a character that does not have an equivalent in the final character set, or in one of the intermediate character sets. In this case, a substitution character is used. This is called lossy conversion and can happen with any client API. You can avoid lossy conversions by configuring the database to use UTF-8 for the database character set.
The database options and database and connection properties that are in effect for a connection are typically available in the full connection string. However, you can also query for the settings using system functions such as PROPERTY, DB_PROPERTY, and CONNECTION_PROPERTY. For example:
|SELECT PROPERTY( 'CharSet' );||Returns the database server's operating system character set.|
|SELECT DB_PROPERTY( 'CharSet' );||Returns the database CHAR character set.|
|SELECT DB_PROPERTY( 'NcharCharSet' );||Returns the database NCHAR character set.|
|SELECT DB_PROPERTY( 'MultibyteCharSet' );||Returns whether CHAR data uses a multibyte character set (On=yes, Off=no).|
|SELECT CONNECTION_PROPERTY( 'CharSet' );||Returns the client CHAR character set.|
|SELECT CONNECTION_PROPERTY( 'NcharCharSet' );||Returns the client NCHAR character set.|
|SELECT CONNECTION_PROPERTY( 'on_charset_conversion_failure' );||Returns the value of the on_charset_conversion_warning option.|
The following diagram shows the possible locations of character set conversion when client APIs interact with the database server.
The yellow stars in the diagram indicate a location where character set conversion can take place. The numbers in the diagram correspond to additional notes located after the diagram.
DBLIB CHAR and NCHAR character sets default to the client operating system character set. The CHAR character set is used for all string data except BINARY and NCHAR. The NCHAR character set is used for NCHAR typed host variables and host parameters (these are not available from the DBTools or C API interfaces). The database server performs all character set conversion.
You can set the CHAR character set and the NCHAR character set using the CharSet connection parameter and the db_change_nchar_charset function, respectively.
With Unicode APIs, a driver uses the Unicode UTF-16 encoding for character data, and converts Unicode data to/from the database character set. The driver converts Unicode host parameters to the NCHAR character set (UTF-8) before sending them to the database server. Result set columns that are described as NCHAR are fetched in the NCHAR character set (UTF-8) and converted to Unicode after they are received. Setting the CharSet connection parameter is not recommended because it can result in a lossy conversion.
HTTP services (SQL Anywhere as the server): For HTTP services, there are two types of requests: URL-encoded or multipart/form-data requests.
Requests take the form of application/x-www-form-urlencoded where variables are passed as key/value pairs. The database server automatically decodes %-encoded data (UTF-8 or database character set) and translates the key/value pairs into the database character set. Processed values can be extracted using the HTTP_VARIABLE function where the @BINARY or @TRANSPORT attributes can be used to return a value that is %-decoded but not character set translated, or the raw HTTP (transport) value, respectively.
Requests are considered to be binary (using the attributes @BINARY or @TRANSPORT would return identical values).
Character set conversion of an HTTP service response is controlled by the CharsetConversion and AcceptCharset HTTP options, which you can set using the sa_set_http_option system procedure.
Other settings that can impact character set conversion for requests and responses are the Accept-Charset header, which is used to specify the preferred character sets, and the Content-Type header, which is used to identify the character set of the content.
HTTP stored procedures (SQL Anywhere as the client): For HTTP stored procedures, CHAR data is sent in the database character set. If any parameter is an NCHAR type, then all data is sent as UTF-8 (all CHAR parameters are translated to UTF-8). The request sends an Accept-Charset HTTP header identifying the database character set as the preferred character set. UTF-8 is always included in the list of preferred character sets. If the response specifies a character set in its Content-Type header that is not the database character set, then the client translates the response into the database character set.
If an ODBC application uses the Unicode entry points, it is considered a Unicode client API. WCHAR data is handled in the same way it is handled for Unicode APIs. If an ODBC application uses the Unicode entry points and CHAR (ANSI) data, the data is assumed to be in the database character set. Mixing CHAR and WCHAR data in the same application is not recommended.
If an ODBC application uses the ANSI entry points, it is considered an ANSI client API. The CHAR character set defaults to the client operating system character set. The CHAR character set can be changed using the CharSet connection parameter.
Any conversion of ANSI data is done by the database server. Fetching into WCHAR host variables can result in a lossy conversion for CHAR data on the database server. Mixing CHAR and WCHAR data in an application is not recommended.
Some ODBC driver managers convert all CHAR data to WCHAR data and then call the Unicode entry points.
TDS clients such as SAP Open Client and jConnect negotiate with the database server at connect time and prefer that character set conversion take place on the client. During the negotiation, the database server is instructed not to perform character set conversion.