I have been inputing data into my sybase database for years without any problems. All of sudden, the text import file that I use is now coming with high ascii characters (like the name O'Brien) and my program is failing.
What is the problem here? I there anything I can do other than stripping the characters out befor the get passed to the input sp.
Language is important here. The English alphabet is totally
covered by the first 128 chars of any ASCII-based charset.
Those chars, first defined by the ASCII standard, have
the same internal representation in all those charsets.
For instance: iso_1 (ISO 8859-1), cp850 (IBM's code page
850 for DOS), roman8 (Hewlett-Packard preferred charset),
MS Windows code page 1252, etc. The most common punctuation
chars are also covered by ASCII.
An English text, as this post, won't have charset problems.
First, conversion from any of the above charsets into other
will never find a untraslatable char. Second, there's no
chance to miss the proper charset; for instance, let's suppose
I pretend to handle this text as iso_1, but I neglect to
specify it when I upload it to a table, using bcp in. If bcp
uses the roman8 charset (by default, v10 bcp in HP-UX uses
roman8), I'll scramble all chars whose internal representation
differ in iso_1 and roman8. But this text has none; I've
been forgiven because it is English text.
Characters aren't illegal "per se"; they are illegal
according to a character set. When a Sybase client
connects to the dataserver, they agree on a charset
conversion. This means that the charset of the client
(for instance, roman8) and the default charset of
the dataserver (for instance, iso_1) are identifyed,
and ASE checks whether it has the appropiate translation
tables to convert from one into the other.
Continuing with the example. Since client and server
have different charsets, "illegal" characters may
be those that
- aren't valid characters according to the client
- while being valid roman8 chars, there's no
corresponding iso_1 chars to traslate into
When client and server agree on the same charset
(for instance, both iso_1), charset traslation
becomes a "passthrough". CPU is saved, as no
traslation is really done.
It's not unfrequent to configure the wrong client
charset, thus causing traslation errors. For instance,
a file containing text strings in iso_1 charset
can be erroneously imported by bcp, configured as
roman8. Sybase will try to handle those chars as
thought they have the roman8 charset, and will do a
wrong traslation. For instance, funny signs may
appear where some special signs should have been
Some traslation messages, as 2403, are simply
warnings: the SQL instruction is executed, but
some chars weren't properly traslated.