On 15/08/2013 1:05 AM, Tim Young [Elevate Software] wrote:
> Peter,

>
> If you're using the Unicode version of ElevateDB, then your databases
> are in Unicode format.  I think you're confusing the collations with
> character sets.  A collation is simply a sort order, and the UNI
> collation is a *binary* comparison of the Unicode characters.  Other
> collations, like the ENU collation, are a different type of sort, but
> they're still using the Unicode character set.
>

I need to understand this more fully.

In my situation there will be :-
1) many users who have English language data in a database
2) many users who have a western European language, such as German, in a
database
3) users who have Japanese language data in a database
4) users who have Korean language data in a database
5) users who have an eastern European language, such as Russian, in a
database
6) users who have English and Japanese language data in a database
7) users who have English and an eastern European language in a database.

To handles these situations I need to use the Unicode version of ElevateDB.

Generally speaking then, do I need to specify a Collation on my VARCHAR
fields?

What else do I need to be aware of?

Do I need to have a parameter in all my SQL sorts that take a Collation?
And somehow get from the user the preferred sort Collation? So for an
"English" user I would use ENU. For a "Japanese" user I would use the
Collation JPN?

Regards,
  Peter Evans

On 8/19/2013 7:17 PM, Peter Evans wrote:
> To handles these situations I need to use the Unicode version of ElevateDB.

Yes that is to store the actual data.

> Generally speaking then, do I need to specify a Collation on my VARCHAR
> fields?

You can or just use it for display (i.e. in SELECT ... COLLATE) - you
know your app best so whatever works for you.

I'm using an example from MySQL docs but consider the following 4 words
that you need to store in your field :
MySQL, Muffler, Müller, MX Systems

Using UNI_CI for a varchar column and doing order by i see :
Muffler, MX Systems, MySQL, Müller

Doing same query but using ENG_CI:
Muffler, Müller, MX Systems, MySQL

Same in SVE_CI :
Muffler, MX Systems, Müller, MySQL

Hence picking the "right" collation would result in better user
experience as things would sort together properly.

This is fairly easy to do if they store a single language.

However if you're mixing more then one then having an option to control
this by user would be useful since there would likely not be a collation
that would match both (especially for something not even related - like
russian and japanese or such).


> Do I need to have a parameter in all my SQL sorts that take a Collation?
> And somehow get from the user the preferred sort Collation? So for an
> "English" user I would use ENU. For a "Japanese" user I would use the
> Collation JPN?

That would be most flexible solution but then make sure it's propagated
to all internal queries as well that perform comparisons (LIKE and such
that are not directly sorting related would be affected i believe as well).

Raul

Peter,

<< Generally speaking then, do I need to specify a Collation on my VARCHAR
fields? >>

It all depends upon whether you want your users to be able to sort strings
in a manner that is proper for their location/language.  And, of course, you
have to balance that against any needs of your own regarding a common
collation specification for shared scripts, etc.

<< Do I need to have a parameter in all my SQL sorts that take a Collation?
>>

No, in most cases it's enough to just define the collations at the column
level, and then everything else will use those collations. You can see how
that works here:

http://www.elevatesoft.com/articles?action=view&category=edb&article=collations_comparisons

<< And somehow get from the user the preferred sort Collation? So for an
"English" user I would use ENU. For a "Japanese" user I would use the
Collation JPN? >>

This is up to you, but yes, it would probably make your application quite a
bit more user-friendly to allow the user to specify the default collation
for their database, and then use that collation when you create/alter the
database structure.  At some point I'll be adding an option to specify the
default collation for each database, and that will make things much easier
for you since you'll only need to create the database with that collation
specification, and then any new tables, etc. that are created will use that
collation, by default, for all VARCHAR/CHAR/CLOB columns.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Raul and Tim,
Just back after 3 days away.
Thanks for excellent feedback.
This has given me a lot to think about.

Regards,
  Peter Evans