Den 2011-05-30 21:05 skrev heineferreira såhär:

> I read from a google search that in order to make Firebird case
> insensitive one must use the unicode coallation.
> I am however confused. I come from a SQL Server background and
> know that the default installation of SQL Server is case insensitive.
> A friend of mine says he always uses character set "none" with Firebird.
> Can you combine character set none with coallation unicode?
> What's the difference between a coallation and a character set in
> database terms? I see one can also specify the charset in the odbc dsn.

Well, I'm no expert but I'll share what I think I know.

First, you're not quite clear about WHAT you want to be case
insensitive. String data? Identifiers?

For Identifiers, they are always stored in Unicode, in UNICODE_FSS
encoding, with (up to) three byte per character. If you create an
identifier qithout quotes, FB will uppercase it before storing it in the
schema. So, if you create atAbLE without quotes, FB will store it as
ATABLE. In order to reference this identifier, you will have to write it
without quotes, in which case FB will uppercase it before trying to find
it in the schema, or write it in uppercase with quotes. All these will work:
"ATABLE"
atable
ATABLE
aTaBlE
AtAbLe
This will NOT work:
"atable"
If you create an identifier WITH quotes, FB will store it exactly as is.
In order to reference it, you have to write it with the exact same case,
and if at least one character is lowercase you have to use quotes.

For string/character data, the character set specifies which characters
can be stored. A collation operates on a character set, specifying sort
order and case and accent sensitivity.

Using character set NONE will work as long as all clients expect the
exact same character set and encoding in all cases everywhere. As soon
as one client expects something else, you've got trouble. Character set
NONE simply means that FB should store and retrieve the data without any
transliteration whatsoever. It handles the data as a stream of
meaningless bytes. I'm not sure how FB handles sorting and comparisons
on character set none (var)char columns. Binary byte order?

For your purposes, I'd recommend charset UTF8 and a suitable collation
for case insensitivity. But identifiers will still be Unicode_FSS inside FB.

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64