Can the substitution character be changed?

[Former Member]

When using an ODBC client connection to a Sybase 9.0.2 database, the question mark '?' was used as a substitution character if a string contained characters that were incompatible with the database character set. From my experience, the substitution seems to be done by the ODBC driver.

On Sybase 10 and later, it seems like the SUB character (0x1a) is used instead. Although the SUB character makes more sense, it is causing problems for some of our legacy applications.

Do you know of a way to (preferably client-side) change the substitution character used in case of character conversion errors?

Any feedback is highly appreciated!

[johnsmirnios · Accepted solution]

In short there is no way to change the substitution character.

Versions 10 and up use ICU for character set translation (and/or information derived from ICU) and ICU uses a specific default substitution character for each character set. See http://demo.icu-project.org/icu-bin/convexp. ICU does allow you to change the substitution character but SQLA has no facility to allow clients to change it. Such a feature would likely be complex and error prone: you need to be able to specify a substitution character for each character set (since clients could be using a charset different from the database charset) and the substitution character would need to be known by both the client and the server (charset conversion can happen in either place). If you wanted to go as far as having each client able to request a different substitution character, things get even more complex.

If you are needing to deal with substitution characters, you should probably be looking into whether the database should be using UTF8 or maybe some of your columns should be NCHAR.

Regards,

-john.

[Former Member]

In case anyone experience a similar challenge:

We worked around the problem by converting all application strings (unicode) to the database character set (8 bit) using the Windows API WideCharToMultiByte before generating SQL statements.

In our tests, this method gave the same character conversions as the Sybase 9 ODBC driver, including the use of substitution characters.

This is probably not an optimal solution for most developers, but it worked for us.