Accent characters processed as plain characters!

[Former Member]

Hi Everyone,

I am running in to an issue where my ASA database is processing accent chars (like ù, ú, û, ü,) as plain chars (like u).

I have a row in a table that looks like:

--------------------------------

     Col1       Col2

--------------------------------

     Test_ù    TestInfo

The table has Col1 as Primary Key.

The database does not honor a request to insert the following row:

     Test_û   TestInfo

It generates a primary key not unique error!!! even though this second row is unique on Col1.

Is there something I am missing that should be considered?

(FYI, these characters are from the default Windows 1252 Code Page.)

Thanks in advance for any input.

Tejo

[JasonHinsperger · Accepted solution]

Hi Tejo,

Your database must be created with a collation in which accented and unaccented versions of the same character to be considered different. 

There are options to do this in the create database statement and the dbinit utility.  You will most likewant to use a UCA collation for your database.

http://dcx.sap.com/index.html#sa160/en/dbreference/create-database-statement.html

hth,

--Jason

[Former Member · Accepted solution]

Hi Jason,

Thank you for your reply.

We currently use the supplied default Collation "1252LATIN1" and the Character Set "cp1252" for Language "us_english". This Character Set has all the characters we need.

Doesn't the supplied ASA Collation '1252LATIN1' meet the requirement you have mentioned.

Also isn't this Collation recommended for Western European Languages (like English, French, German, & Spanish) as it provides the most likely match to the Character Set 'cp1252'.

Is there a specific Collation you'd want me to try during database creation.

Thanks for all your input.

Tejo

[JasonHinsperger · Accepted solution]

Something like this should work for you:

dbinit -a -z UCA -ze windows-1252 mydb.db

-a : treat accented characters as different

-z : use the UCA collation for sorting

-ze: the encoding for all CHAR datatypes is 1252

You can read more about collations here to better understand how they work:

SQL Anywhere Collations

--Jason

[Former Member · Accepted solution]

Hi Jason,

Thanks for the recommendation.

We are having to use an older version of the ASA database - 8.0.2.4272 unfortunately!

Looks like the above switches are for the newer version and not available in ours. Are there any alternate switches for the older version we are having to use?

Thank you

Tejo

[JasonHinsperger · Accepted solution]

With version 8.0.2 your only option to get accented characters to be treated as unique would be to use the dbcollat utility and create your own custom collation based on 1252.

You can find info. in the 8.0.2 help file under "custom collations" and the "dbcollat" utility.  Basically, you can unload the collation from a 1252 database and then customize it to your liking and use the customized location when you create a new database.

However, note that custom collations have been removed in more recent versions of SQL Anywhere in favor of the more standard ICU collations, so if you do upgrade in the future, you will have to rebuild your database with a new standard collation.

--Jason

[Former Member · Accepted solution]

Jason,

Thanks a lot for your helpful pointers as well as insights.

Looks like the "-a" switch was thoughtfully added in to the newer version to cover such situations. Clearly emphasizing an evolution in multi-language database support.

We are not sure if we could take the route of customizing the collation at this point. It could make our application both backward & forward (upgrade) incompatible then.

You have a wonderful holiday season.

Thanks again.

Tejo