On 22 Dec 2001, at 22:42, didiergm wrote:

> Helen,
>
> Sorry I did not answered earlier, I was away for a couple of days
> (pre-xmas holidays :-)). Anyway, the Unicode FSS seems to be a quite
> laborious way. I also forgot to mention that my app must be able to
> run on some other DB engines (namely Sybase & Oracle). If I have to
> prefix all fields with '_UNICODE_FSS:' then the new issue is that I
> will have to maintain a completely separate set of Sql code for
> firebird (aaarrrgh).
>
> I did some more experiment, and notices that if I set the default
> charset to be WIN1252, it seems that at least the basic accented
> western european characters are indeed recognised, with no display
> issues (at least none obvious I could find) in Delphi.
>
> My front end is Windows only, so , am I missing something?
>
> The root of my previous problems came from the fact that I've been
> using several different DBA tools for interbase (in view of testing &
> chosing the right one for me), but it seems that all those products
> have a completely different defualt understanding/behaviour as far as
> default charset are concerned. so I ended up with a dreadful mixture
> of fields and charsets, without realizing.
>
> Another question, I ghave tried to find a description (comprehensive)
> of collation orders, but all I could come up with was collation order
> names (or codes) with are supposedly known to a lot of people (but me
> ?) I'd love to see a table showing how a particulmar collation
> sequence works and/or how easy it could be to build my own.
>

One thing I find useful, for supporting different engines are domains,
for example some engines support a boolean type, others do not,
so you create a domain called D_BOOL (D_ meaning it's a domain,
so if the engine later adds a bool type, you don't have to rewrite
code). For engines that have a bool type, it becomes:

create domain D_BOOL as boolean

for IB/FB you do this:

create domain D_BOOL as integer DEFAULT 0 CHECK (VALUE IN
(0,1)) not null;

This effectively creates a boolean type, although it uses more
storage then it needs to.

So fields that may be entered as English or French (I'm in Canada),
I define using a set of domains I call D_BICHAR_length here are a
few examples:

create domain D_BICHAR_11 as varchar(11) character set
WIN1252 collate PXW_INTL850;

create domain D_BICHAR_31 as varchar(31) character set
WIN1252 collate PXW_INTL850;

create domain D_BICHAR_51 as varchar(51) character set
WIN1252 collate PXW_INTL850;

This should in theory mean that aàáâãäAÀÁÂÃÄ should all be
considered the same character for sortation purposes. You need
to do your own testing however, to make sure this works for you.
The simplest way, is to create a small table, then stuff it with the
characters, then do an order by, to make sure it works for you.
Make sure you load the table out of order so that it needs to
reorder it properly to get it to work,

This also gets you past the other engines because II don't know
Sybase (well) and I don't know Oracle, but if the same mechanism
is available it's going to work differently, so ultimately you will
probably enf up with different SQL anyway. However if they support
domains, then this goes a long way towards getting them
compatable.

Paul



Paul Schmidt
Tricat Technologies
paul@...
www.tricattechnologies.com