Subject Re: conversion problem
Author achidan
--- In ib-support@y..., "Claudio Valderrama C." <cvalde@u...> wrote:
> "Helen Borrie" <helebor@t...> wrote in message
> news:
> > >
> > >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?

IBObjects always tries to do a "select count..."-statement for
RecordCount (at least I think it does like this). if you have a
statement like

select distinct field1, field2 from table1

then IBObjects (in IB_Parse, function MakeCountSql) creates the
following statement:

select count(distinct field1 || '.zZz.' || field2) from table1

Only in that way you get the correct result. But the bad side is, that
this statement is not always working.

> 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
> in the connection parameters.

I think Firebird is not doing it in the same way for similar situations:

select field1 || field2 from table1 is (as I guess from some more
experiments) interpretated in the following way:

if field1 is numeric field (numeric, integer, ...), field2 numeric:
select cast(field1 as _dynamic (var)char) || field2 from table1
if field2 has characters > 127 (with i.e. charset ISO8859_1) then you
get the error.

select 'zZz' || field1 || field2 from table1 works because 'zZz' seems
to be interpreted as _iso8859_1 'zZz' and all the following fields
take the same charset.

I think best behavior of Firebird would be to take for implicit
conversion always the charset which is defined as standard charset
within the database. This is only true for string literals but not for
numeric fields.

Some of the examples in my first message were only working because
they had "per coincidence" no characters > 127. So the error is always
created in the following situation:

select distinct field1 || field2 from table1

field1 must be numeric, field2 (var)char with characters > 127.
Distinct is important.