SQL Order By in UTF-8 database - OpenEdge General - Forum

Posted by steve pittman on 01-Apr-2015 15:15

Please note that -cpcoll is used by ABL client and server and broker.
OE sql uses only the database collation. OE sql does not use -cpcoll.

As Garry notes, pls check the database collation as shown.

.....sjp

[collapse]On 4/1/2015 3:58 PM, Garry Hall wrote:

RE: SQL Order By in UTF-8 database
Reply by Garry Hall

The -cpcoll identifies the collation used for runtime comparisons. The database collation is stored in the database. The easiest way to find it is using the Data Administration tool:

- _progres -p _admin.p

- Utilities->Information

The database collation will be listed there, along with the database codepage.

Stop receiving emails on this subject.

Flag this post as spam/abuse.

[/collapse]

All Replies

Posted by steve pittman on 01-Apr-2015 09:49

[collapse]On 4/1/2015 7:33 AM, Andrzej_Sz wrote:

SQL Order By in UTF-8 database
Thread created by Andrzej_Sz

"SELECT ... ORDER BY table.name" in my OE 10.2B UTF-8 database puts national names at the end of the list.

How to achive the right order of the records like in "FOR EACH ... BY table.name" 4GL phrase ?

Stop receiving emails on this subject.

Flag this post as spam/abuse.

For OpenEdge sql, the order of character data in ORDER BY is determined by the database collation.
Since you observe "national names" at the end of your result set (from SELECT), it sounds like your db collation is probably the default BASIC collation.
There are a number of UTF-8 collations that are more locale specific and that would probably give you the ordering you want (or close to what you want).
These collations are in the OE installed directories, usually in $DLC/prolang/utf .

These are described in the OE Internationalizing Applications book that is available online .
Basically, to use a different db collation, you would first determine a collation that works for you, then load the .df for that collation into the db, and then do an index rebuild so all the indexes with character data get rebuilt.
This is just a very brief overview - please check out all the very important details.
This is a non-triivial process to be very carefully done of course!

Once the database collation is all setup your SELECT will automatically use it.

hope this helps, ....steve pittman [OE sql software architect]

[/collapse]

Posted by Andrzej_Sz on 01-Apr-2015 14:34

My settings in "pf" file and server are the same:

Collation table (-cpcoll): ICU-pl (from utf8 group)

Server code page (-cpinternal): UTF-8

Posted by Garry Hall on 01-Apr-2015 14:57

The -cpcoll identifies the collation used for runtime comparisons. The database collation is stored in the database. The easiest way to find it is using the Data Administration tool:

- _progres -p _admin.p

- Utilities->Information

The database collation will be listed there, along with the database codepage.

Posted by steve pittman on 01-Apr-2015 15:15

[collapse]On 4/1/2015 3:58 PM, Garry Hall wrote:

RE: SQL Order By in UTF-8 database
Reply by Garry Hall

The -cpcoll identifies the collation used for runtime comparisons. The database collation is stored in the database. The easiest way to find it is using the Data Administration tool:

- _progres -p _admin.p

- Utilities->Information

The database collation will be listed there, along with the database codepage.

Stop receiving emails on this subject.

Flag this post as spam/abuse.

[/collapse]

Posted by Andrzej_Sz on 01-Apr-2015 15:45

You're right. Database collation is BASIC and should by "ICU-pl" I suppose.

Could you help me with the "db collation" change ?

Posted by Andrzej_Sz on 01-Apr-2015 16:15

I did it with a little help of Knowledgebase.

knowledgebase.progress.com/.../19071

Thank you very much for your help.

This thread is closed