At 08:34 AM 11/10/2007, you wrote:

>Hi,
>
>I need to translate an utf8 value in a varchar field to WIN1252
>
>using fb 1.53 or fb 2.0.3
>
>This works oke:
>
>select cast((_unicode_fss 'Möbel') as char(10) character set
>WIN1252) from rdb$database;
>
>you get Möbel
>
>
>But this is not working when you have the value from a field:
>
>select cast((_unicode_fss ORDERREFERENTIE) as char(10) character set
>WIN1252) from TBL_ORDER WHERE ORDERNUMMER='1481351-1'
>
>you get
>
>*** IBPP::SQLException ***
>Context: Statement::Prepare(
>select cast((_unicode_fss ORDERREFERENTIE) as char(10) character set
>WIN1252) from TBL_ORDER WHERE ORDERNUMMER='1481351-1' )
>Message: isc_dsql_prepare failed
>
>SQL Message : -104
>Invalid token
>
>Engine Code : 335544569
>Engine Message :
>Dynamic SQL Error
>SQL error code = -104
>Token unknown - line 2, char 27
>ORDERREFERENTIE

Well, you have discovered that the introducer
syntax works only with literal values, which is true. :-)

Provided that ....

1) Firebird knows that ORDERREFERENTIE data is
stored as UNICODE_FSS (because it is declared in
the default character set, the domain definition or the field definition)
and
2) You cast it to an output type with compatible
character attributes, particularly that all of
the UNICODE_FSS characters in the actual data are
certain to have equivalent character definitions in WIN1252

.... this should give you the transliteration you want:

select
cast((ORDERREFERENTIE) as char(10) character set WIN1252) MyField
from TBL_ORDER
WHERE ORDERNUMMER='1481351-1'

With UNICODE_FSS just don't try to ask for
something that is not available for the source
character set, for example, an expression that
has to test UPPER(ORDERREFENTIE). UNICODE_FSS is a very blunt instrument...

AFAIR, the introducer syntax can be applied to a
local variable in PSQL, i.e., from memory, in
v.1.5.x I think you can do something along these lines:
create procedure...
as
declare ORUNI varchar(10) character set UNICODE_FSS;
declare OR1252 varchar(10) character set WIN1252;
begin
for select ORDERREFERENTIE from TBL_ORDER
WHERE ORDERNUMMER='1481351-1'
into :ORUNI do
begin
OR1252 = cast ( (_UNICODE_FSS ORUNI) as varchar(10));
....
end
end

Sorry, if I were not up to my ears in crocodiles
right now I'd try it out for you.

The Unicode implementation has changed radically
for the better in Fb 2 and onward, though. One
would aim to convert legacy unicode_fss data to
UTF8 and have it properly handled by the new INTL
implementation. (I'm not pretending that would
be totally simple to do, though...at least, for
me...I'm still trying to get my head around it so
I can write some sensible documentation for it)

./heLen