"Helen Borrie" <helebor@...> wrote in message
news:5.1.0.14.0.20020406232728.04300ce0@......

> >
> >arithmetic exception, numeric overflow, or string truncation
> >Cannot transliterate character between character sets
> >
> >The bad side about this bug is that IBObjects internally uses such
> >kind of select statements. So I cannot avoid is so easily.

Where is IBO generating that? FetchWholeRows turned False?

> Until/unless Claudio or Dave S. has a better suggestion, I suspect that
> your problem comes in the casting of the numeric(18,0) to a string and you
> might be getting a not entirely appropriate error message back.

I think the message is basically right: transliteration error.
Let's see, string literals don't have an associated charset unless you use
the old trick
_charset_name 'literal string'
for example
_iso8859_1 'hello'
In absence of that trick, the default charset for a literal string is
dynamic. Dynamic is 127 internally, it doesn't exist, it's only a
placeholder. It has to be resolved later. Same way, when you force the
conversion of a number to a string, the temporary uses charset dynamic.
Finally, the engine resolves "dynamic" to the current charset as specified
in the connection parameters.

> If you are
> using a dialect 3 database (you didn't say) then you are expected to cast
> the numeric(18,0) explicitly as a character type in order to use it in a
> concatenation.

I remember there's something about that in some doco, annotates as an
improvement for dialect 3 conformance with SQL, but can't find the place.

> Some of the statements appear to be allowing the implicit
> cast but it's not recommended in dialect 3... select distinct might be
> especially sensitive because you are asking it to perform its internal
> sorting on this "hairy" concatenation.

Believe it or not, IB/FB is in sync with the standard for most cases when it
comes to concatenation and charsets. Where it misses the boat is in the type
of the result (char or varchar) but I didn't risk to fix that for FB1.

I'm quoting below an excerpt from a response from His Daveness <g> received
more than a month ago. I'm not sure if he got right the full matrix for the
result of a concatenation, but you get the idea (Dave implemented charsets
and collations in IB around 1991-1993, since the original engine by Jim was
ASCII-based). We were discussing some failures that remain in the
implementation of substring, specially when dealing with charsets whose pad
char is not the blank (like the binary charset, also known as octets). See
below.

C.

From David Schnepper:

The rules on this were really fuzzy, in flux, and never that well
defined (to my satisfaction anyway, and I was doing all the work!).
Octets can be forced to another character set, but never implicitly,
only via an explicit CAST(). The idea with OCTETS is we know the
data is non-character, but it can become character if the programmer
tells us so We assume he/she knows what they are doing.
Let's see if I can still fill out the operation matrix.

Val1 || Val2 Result
Octet Octet Octet
Octet <any> Octet (Val2 is cast to <octet>)
None Octet Transliteration error
None <any> None
Ascii Octet Transliteration error
Ascii None Ascii (Val2 is cast to ASCII)
<any> Octet Transliteration error
<any> Ascii <any>
<any> None <any>

Another way to think of this is via precidence levels

Octet
<any defined character set>
ASCII
None

You can always implicitly move data upwards in the precidence
chain, but never downwards. A downward move always takes
an explicit cast.

(Hey, not so bad for 10 year old memory chips!)

Dave