Cyrillic Alphanumeric Ordering Problems

[RADical_Systems]

Hi,

I have a client in Russia using our software in Cyrillic and they are getting what I would describe as "odd" ordering of alphanumeric lists.

When displaying a list of names the order appears alomost random in that there will be a number of A's then a number of B's then again some A's, Some B's, Some C's, Some Z's then some A's again. For example if it was in English they get:

Aberthforth, Brian
Aimes, John,
Charles, A
Charleston, Peter
Avery, J
Bryan, Zoe

It might start off looking OK but then fails. I have a nice screenshot to show the example but I can't post this until I get to 100 points. If allowed I can post a link to the screenshot on our website if that helps.

I have tried running simple queries in ISQL and get the same results:

select customer.keyname from customer order by customer.keyname

They are using SQL Anywhere 10 on Windows 7 and XP.

It has been suggested that changing the collation of the database may help but this is totally new territory for me so thoughgt I would tap up the forum knowledge before I go down completely the wrong route.

Thanks in advance.

Alasdair

Added for Alasdair:

Character set 1252LATIN1, SQLA 10.0.1.3831

[johnsmirnios · Accepted solution]

I'd say that it looks like someone has disabled (or otherwise bypassed) character set translation. This doesn't appear to be an NCHAR column since the first byte as reported in the "ascii()" column would not be reasonable lead bytes for Cyrillic characters. If it is a CHAR column, well, Cyrillic characters are not representable in CP1252. The characters 192 through 195 in CP1252 are accented forms of capital-A and are therefore considered equal: they will appear in an arbitrary order.

-john.

[RADical_Systems · Accepted solution]

John,

Thanks for the response. I believe you have hit the nail on the head.

The simple solution to entering Cyrillic characters which were then translated into something else when saved to the database was to turn of Character Translation. I'm afraid to say we first did this back with a customer on ASA 8 and the suggestion came from Sybase. At the time I was stumped as to what was going on and phoned for support. I guess I didn't explain myself properly and they assumed I was on a database with the correct collation hence the suggestion.

Anyway I'm off to lock myself in a room with no windows and research what the hell I might do to change the database collation back to the appropriate settings. Presumably a database unload and reload will not work! Anyone with any thoughts they would be gratefully appreciated.

Thanks for all your help.

Alasdair

[johnsmirnios · Accepted solution]

I'm sorry if incorrect advice from Sybase led to the current situation.

It should be pretty easy to change the collation. If you are only worried about this one database (rather than a large deployment) and the database truly does only contain CP1251 data then just do the following: 1) unload 2) Look at the reload.sql file and find the LOAD TABLE ... ENCODING 'windows-1252' statements. Change the 'windows-1252' to 'windows-1251' 3) create a new database with a 1251CYR collation 4) execute the reload.sql script

If your schema (table names, procedure definitions, etc) contains Cyrillic characters, you may have other problems with those but you might be okay if your current locale is 1251 when you execute your script.

I'm heading out of the office in an hour and won't be back until Monday. I'm sure others can step in if you encounter other problems in the meantime. Good luck!

[RADical_Systems · Accepted solution]

OK, I'm getting a copy of the database and will try the unload and reload as above and let you know how it goes.

Thanks again.

Alasdair

[RADical_Systems · Accepted solution]

I eventually got the database and ahve tried the above. It does look fine to my eyes which is great news. I have sent it off to the customer to check and if they are happy I will do the same to their live database.

Thanks very mcuh for your help. I'll report back.